Formulas longer than 255 characters

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

Is it possible to create a formula longer than 255 characters? At this point
you get an error.
 
Hi Geoff!

Excel specifications say maximum length of formula is 1,024
characters. If you're getting a problem with a shorter one, then post
the formula you are attempting. My guess at this stage is that you're
exceeding a nesting limitation.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
E.g.
=CustomNew("y","aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa")

the whole sytax needs to be lower than 255. By nesting, do you mean the
second part of the formula.

Thanks
 
I note MS limits Excel strings to a length of 255 characters so are there
any recommended ways to work around this!
 
Hi Geoff,

Don't know about your version of Excel ,but this works for me (ExcelXP):

=UPPER("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa")


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
If you tried this in a custom function this does not work when beyond the
255 limit

Public Function x(a As String)
x = a
End Function
 
Hi Geoff,

Right. But you could concatenate within the function. Supply your text as
seperate text arguments and & them together. Tested.
Of course it depends on your specific needs if
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
this is an acceptable solution.
 
I bet that Niek meant for you to write your function so that it does the
concatenating--not passing the long string to it:

Option Explicit
Function CustomNew(ParamArray Args() As Variant) As Long

Dim myLongString
Dim iCtr As Long

myLongString = ""
For iCtr = LBound(Args) To UBound(Args)
myLongString = myLongString & Args(iCtr)
Next iCtr

CustomNew = Len(myLongString)

End Function

I got 502 back when I did this:
=CustomNew("y","n",REPT("aaaaa",50),REPT("aaaaa",50))
 
Thanks

This will work

Dave Peterson said:
I bet that Niek meant for you to write your function so that it does the
concatenating--not passing the long string to it:

Option Explicit
Function CustomNew(ParamArray Args() As Variant) As Long

Dim myLongString
Dim iCtr As Long

myLongString = ""
For iCtr = LBound(Args) To UBound(Args)
myLongString = myLongString & Args(iCtr)
Next iCtr

CustomNew = Len(myLongString)

End Function

I got 502 back when I did this:
=CustomNew("y","n",REPT("aaaaa",50),REPT("aaaaa",50))
Click to expand...
 
Thanks Dave!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

Geoff said:
Thanks

This will work

Dave Peterson said:
I bet that Niek meant for you to write your function so that it does the
concatenating--not passing the long string to it:

Option Explicit
Function CustomNew(ParamArray Args() As Variant) As Long

Dim myLongString
Dim iCtr As Long

myLongString = ""
For iCtr = LBound(Args) To UBound(Args)
myLongString = myLongString & Args(iCtr)
Next iCtr

CustomNew = Len(myLongString)

End Function

I got 502 back when I did this:
=CustomNew("y","n",REPT("aaaaa",50),REPT("aaaaa",50))
Click to expand...
text
as
Click to expand...
=UPPER("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa so
Click to expand...
 
Back
Top