VBA formula strings longer than 255 characters
 

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.

VBA formula strings longer than 255 characters
 

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" wrote in message
...
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.

VBA formula strings longer than 255 characters
 

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" wrote:

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" wrote in message
...
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.

VBA formula strings longer than 255 characters
 

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



"MiD-AwE" wrote in message
...
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" wrote:

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" wrote in message
...
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.