VBA formula strings longer than 255 characters

[MiD-AwE]

Hi all,

My question appears to be related to "Article ID : 213841". I'm running
Excel 2003 and using VBA to parse some formulas from a seperate Workbook. All
is fine until I run into a situation when a formula is longer than 255
characters. So, I have tried MS's workarounds as follows:

If Len(nfrmla) > 255 Then
With Selection
..Formula = ""
For indx = 0 To Application.RoundUp(Len(nfrmla) / 255, 0)
..Characters(.Characters.Count + 1).Insert = Mid(nfrmla, (indx * 255) + 1, 255)
Next indx
End With
Else: ActiveSheet.Range(frmlaCel).Formula = nfrmla
End If

The result is the continued "Run-time error '1004'". Pease help me solve this?

Thank you in advance.

 

[Peter T]

Absolute max length of a formula is 1024 but it can be somewhat less, though
normally at least 900. However you cannot have individual strings within the
formula that exceed 255.

Mess around with this

Sub test()
Dim s1 As String, s2 As String, sFmla As String

s1 = " Hello"
Do
s2 = s2 & s1
Loop Until Len(s2) > 210

sFmla = "=A1 & " & Chr(34) & s2 & Chr(34) & _
" & " & _
Chr(34) & s2 & Chr(34) & _
" & " & _
Chr(34) & s2 & Chr(34) & _
" & " & _
Chr(34) & s2 & Chr(34)

Range("A2").Formula = sFmla

MsgBox Len(Range("A2").Formula)

End Sub


Regards,
Peter T

 

[MiD-AwE]

I found your answer very helpful, but I still have a 772 character sting that
triggers the "Run-time error '1004'". I'm wondering why you used a 210
character string for your example. Your example resulted in a longer string
than mine but your works and mine fails. I'll try the 210 length just in case
that works.

 

[Peter T]

I'm wondering why you used a 210

character string for your example.

Just a quick way to make an adjustable string +/- 255, actually as written
Len(s2) = 216

If Len(s2) is 255+, writing the formula would fail, even with only one
instance of the string s2 (instead of the 3 as written).

Like I said, mess around with it. You'll soon see what works and what
doesn't (though it's a bit inconsistent near the margins).

Regards,
Peter T