CODEWITHSUNDEEP
vbaexcel
StayAtHome
StayAtHome

Reputation: 91

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

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.

Upvotes: 7

Views: 4484

Answers (2)

Ralph
Ralph

Reputation: 9444

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

Upvotes: 1

brettdj
brettdj

Reputation: 55682

question changed with this additional comment
https://stackoverflow.com/users/4742533/stayathome
will return and update this

initial answer

You can format the partial string using characters.

Code below appends your sample string to test string (300 characters long), then makes the last three italic, the three before that bold.

Sub LikeThis()
Dim StrIn As String

StrIn = "aaaabbbccc"

[a1] = Application.Rept("xyz", 100)

[a1].Value2 = [a1].Value2 & StrIn

[a1].Characters(Len([a1]) - 5, 3).Font.Bold = True
[a1].Characters(Len([a1]) - 2, 3).Font.Italic = True

End Sub

Upvotes: 0

Related Questions