Characters.Insert Method (Excel) limits text to 255 characters

Question

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

Sample code:

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

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

Answer 1

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.

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.

Edit#1:

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