Formulas longer than 255 characters

[Geoff]

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

 

[Norman Harker]

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.

 

[Geoff]

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

 

[Geoff]

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

 

[Niek Otten]

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

 

[Geoff]

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

 

[Niek Otten]

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.

 

[Geoff]

With the function detailed I get the #Value with the & when exceeding the
255 characters

 

[Dave Peterson]

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))

 

[Geoff]

Thanks

This will work

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))

 

[Niek Otten]

Thanks Dave!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

Thanks

This will work

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))

text

as

=UPPER("aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"&"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa so

 

[Dave Peterson]

You're quite welcome <vbg>.

Thanks Dave!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel