Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formulas cannot return strings longer than 255 | Excel Programming | |||
characters and strings | Excel Discussion (Misc queries) | |||
Binary numbers longer than 10 characters | Excel Programming | |||
Binary Numbers longer than 10 characters | Excel Worksheet Functions | |||
String Not Longer Than 255 Characters in VBA? | Excel Programming |