Characters.Insert方法(Excel)将文本限制为255个字符 [英] Characters.Insert Method (Excel) limits text to 255 characters

问题描述

实际上不可能通过MS Excel中的VBA宏在单个单元格中添加超过255个字符吗?

It is really impossible to append more than 255 chars into a single cell by VBA macro in MS Excel?

示例代码:

Option Explicit
Sub TestSub()
  Dim L As Long
'  Const str = "1" & vbLf
  Dim i As Integer
  Range("A1").ClearContents
  Range("A1").WrapText = True
  For i = 1 To 260  ' any number greatest than 255
    L = Range("A1").Characters.Count
    Debug.Print L
    Range("A1").Characters(L + 1, 1).Insert ("A")
  Next i
End Sub

已添加: 将以前的char格式保存在单元格中很重要.

Added: It is important to save previous formatting of chars in cell.

推荐答案

以下代码会将500 A写入单元格A1.此后，所有其他A都将设置为粗体格式.

The following code will write 500 A into cell A1. Afterwards, every other A will be formatted bold.

Public Sub tmpSO()

For i = 1 To 500
    Range("A1").Value = Range("A1").Value & "A"
Next i

For i = 1 To 500
    If i Mod 2 = 0 Then Range("A1").Characters(i, 1).Font.Bold = True
Next i

End Sub

我希望能解决您的问题.

I hope that solves your problem.

注意:您的代码将无法正常工作，因为您尝试在之后 L + 1后面插入一个字符.但是，您的字符串当前只有L个长，而不是 L + 1个.插入另一个A后，该单元格中将包含L + 1个字符.但还没有.因此，如果您将代码与Range("A1").Characters(L, 1).Insert ("A")一起使用，那么它将起作用.

Note: your code won't work because you are trying to insert a character after L + 1. Yet, your string is currently only L long and not L + 1. Once you have inserted another A you will have L + 1 characters in that cell. But not yet. So, if you are using your code with Range("A1").Characters(L, 1).Insert ("A") then it will work.

下面的代码已经过测试，并将500 A正确插入单元格A1中.此外，某些A的格式将为粗体.

The following code has been tested and correctly inserts 500 A into cell A1. Furthermore, some of the A will be formatted bold.

Sub TestSub()
    Dim i As Integer

    Range("A1").ClearContents
    Range("A1").WrapText = True
    Range("A1").Font.Bold = False

    For i = 1 To 500
        Range("A1").Characters(i, 1).Insert ("A")
    Next i
    For i = 1 To 500 Step 10
        Range("A1").Characters(i, 3).Font.Bold = True
    Next i
End Sub

这篇关于Characters.Insert方法(Excel)将文本限制为255个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！