Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul

maybe 255

U¿ytkownik "Paul O. Schenker" napisa³ w
wiadomoœci ...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul
yes, there is - I guess 255 characters

Hi Paul,
is there a length limitation for formulas?

I found approx 1024 characters with the following formula, add
something and it will not work anymore.

arno


=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A1 5+A16+A17+A18+A19+A20
+A21+A22+A23+A24+A25+A26+A27+A28+A29+A30+A31+A32+A 33+A34+A35+A36+A37+A3
8+A39+A40+A41+A42+A43+A44+A45+A46+A47+A48+A49+A50+ A51+A52+A53+A54+A55+A
56+A57+A58+A59+A60+A61+A62+A63+A64+A65+A66+A67+A68 +A69+A70+A71+A72+A73+
A74+A75+A76+A77+A78+A79+A80+A81+A82+A83+A84+A85+A8 6+A87+A88+A89+A90+A91
+A92+A93+A94+A95+A96+A97+A98+A99+A100+A101+A102+A1 03+A104+A105+A106+A10
7+A108+A109+A110+A111+A112+A113+A114+A115+A116+A11 7+A118+A119+A120+A121
+A122+A123+A124+A125+A126+A127+A128+A129+A130+A131 +A132+A133+A134+A135+
A136+A137+A138+A139+A140+A141+A142+A143+A144+A145+ A146+A147+A148+A149+A
150+A151+A152+A153+A154+A155+A156+A157+A158+A159+A 160+A161+A162+A163+A1
64+A165+A166+A167+A168+A169+A170+A171+A172+A173+A1 74+A175+A176+A177+A17
8+A179+A180+A181+A182+A183+A184+A185+A186+A187+A18 8+A189+A190+A191+A192
+A193+A194+A195+A196+A197+A198+A199+A200+A201+A202 +A203+A204+A205+A206+
A207+A208+A209+A210+A211+A212+A213+A214+A215+A216+ A217+A218+A219+A220+A
221+A222+A223+A224+A225+A226

done a quick test:
in excel 97 and excel 2003 it appears to be 1024.

Sub LongFormula()
Dim i, m, s$
m = 255
ActiveSheet.Cells.Clear
Application.ScreenUpdating = False

On Error GoTo oops:
For m = 1 To 255
s = ""
For i = 1 To m
s = s & "+" & Cells(i, 3).Address(0, 0)
Next

Cells(m, 1).Formula = "=" & s
Cells(m, 2).Formula = Len(s)
Next
Stop
oops:
Application.ScreenUpdating = True
MsgBox m & vbLf & Len(s)

End Sub

if you change address(0,0) to plain address
it still errors out when len(s) goes over 1024.




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Snake Plissken wrote :


U¿ytkownik "Paul O. Schenker" napisa³ w
wiadomoœci ...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul
yes, there is - I guess 255 characters

U¿ytkownik "Snake Plissken" napisa³ w wiadomoœci
...

U¿ytkownik "Paul O. Schenker" napisa³ w
wiadomoœci ...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul
yes, there is - I guess 255 characters


or even more but for sure there is a limit... in exc2000 I was able to put
only 1000 characters in formula string

In Excel Help under limitations and specifications it says:

--------------------------------------------------------------
Length of formula contents 1,024 characters

--------------------------------------------------------------

Here is some added information:

The length of a formula is determined using R1C1 notation, so if you are
using A1, it may vary in terms of the actual length.

--
Regards,
Tom Ogilvy

"Paul O. Schenker" wrote in message
...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul

Hi Paul,

As others have said the length limit for a formula applied with vba is max
1024, or can be a bit less.

However you may be able to work around this. Eg, if the formula contains
references to other sheet(s) you can rename sheet(s) to something very
short, build your formula string with short names, apply the formula, then
restore original long sheet name(s).

I don't know the "real" max length of a formula, as a guess 32k. Even though
the formula could be considerably more than 1024, you cannot "read" more
than the first 1000+ characters.

Regards,
Peter T


"Paul O. Schenker" wrote in message
...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul

This contrives to build a formula length of 5489:

Sub test()
Dim i As Long, nShts As Long
Dim sF As String, sPartF As String
Dim sName As String, sMsg As String
Dim cel As Range

sName = "z"
Worksheets(2).Name = sName
Worksheets(2).[a1:a200].Value = 1

sPartF = sName & "!A"
sF = "=" & sPartF & 1

nShts = 161
For i = 2 To nShts
sF = sF & "+" & sPartF & i
Next

Set cel = Worksheets(1).[a1]

cel.Formula = sF

sName = "My_Unnecessarily_Long_SheetName"
Worksheets(2).Name = sName

sMsg = nShts & " additions" & vbCr & _
"Original formula len " & Len(sF) & vbCr & _
"Actual formula len " & (Len(sName) - 1) * nShts + Len(sF) & vbCr & _
"Readable formula len " & Len(cel.Formula)

MsgBox sMsg, , "Value A1 = " & cel.Value

End Sub

Regards,
Peter T


"Peter T" wrote:
Hi Paul,

As others have said the length limit for a formula applied with vba is max
1024, or can be a bit less.

However you may be able to work around this. Eg, if the formula contains
references to other sheet(s) you can rename sheet(s) to something very
short, build your formula string with short names, apply the formula, then
restore original long sheet name(s).

I don't know the "real" max length of a formula, as a guess 32k. Even
though
the formula could be considerably more than 1024, you cannot "read" more
than the first 1000+ characters.

Regards,
Peter T


"Paul O. Schenker" wrote in message
...
Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul

typo:

...length of 5489:
should read
length of 5849

Peter T

that just caused an error on the first message box. (xl2003). The formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you will
see the truncated formula isn't functional (it isn't secretly working as it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

Hi Tom,

We appear to get different results. I've doubled checked and the "long"
formula with long sheet name still works, on manual recalc or if I change
any of the values on Sheet2 A1:A161. I've even pasted back the code from my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly 1000
after changing the sheet name, not 1023. The original formula length is 1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you will
see the truncated formula isn't functional (it isn't secretly working as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

my post about length limitation

I had to help people trying to make some kind of report in Excel, taking a
lot of text and sticking it in one cell.
As a result they didn't see everything.
There is 1024 limit, however if you look in Formula Bar you will see all
your information way beyond 1024 characters.
As a matter of fact, I just tried to type as long it will type - it wouldn't
type beyond 32767 characters.

In my coworker situation I had to trunk text block to the width of the cell
with vbCrLf (if my memory serves me well)

AvP




"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you will
see the truncated formula isn't functional (it isn't secretly working as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

Hi Peter,

Your very long formula works OK for me using excel97, excel2000 and
Excel2002.
Check the truncation when in R1C1 mode: I get 1024

I suspect that the internal limit for functioning formulae is probably based
on the parsed version of the formula rather than the text version, and the
parsed form contains numeric references to the sheet name rather than the
sheet name itself: cant check in the SDK because I have lent my copy to
someone.

But there are limits on the size of formula string (and FormulaArray string)
that can be entered without using the namechange trick.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

We appear to get different results. I've doubled checked and the "long"
formula with long sheet name still works, on manual recalc or if I change
any of the values on Sheet2 A1:A161. I've even pasted back the code from
my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly 1000
after changing the sheet name, not 1023. The original formula length is
1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

That isn't a formula.

--
Regards,
Tom Ogilvy

"Barmaley" wrote in message
...
my post about length limitation

I had to help people trying to make some kind of report in Excel, taking a
lot of text and sticking it in one cell.
As a result they didn't see everything.
There is 1024 limit, however if you look in Formula Bar you will see all
your information way beyond 1024 characters.
As a matter of fact, I just tried to type as long it will type - it
wouldn't
type beyond 32767 characters.

In my coworker situation I had to trunk text block to the width of the
cell
with vbCrLf (if my memory serves me well)

AvP




"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

OK, the code still errors for me. However, the formula does continue to
work as long as you don't edit it and hit enter rather than escape. I did
edit it to see what it looked like - thus my initial response. So there may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least to
me).

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

We appear to get different results. I've doubled checked and the "long"
formula with long sheet name still works, on manual recalc or if I change
any of the values on Sheet2 A1:A161. I've even pasted back the code from
my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly 1000
after changing the sheet name, not 1023. The original formula length is
1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489. It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

I did noted "my post about length limitation" at the beginning, though :)

"Tom Ogilvy" wrote in message
...
That isn't a formula.

--
Regards,
Tom Ogilvy

"Barmaley" wrote in message
...
my post about length limitation

I had to help people trying to make some kind of report in Excel, taking
a
lot of text and sticking it in one cell.
As a result they didn't see everything.
There is 1024 limit, however if you look in Formula Bar you will see all
your information way beyond 1024 characters.
As a matter of fact, I just tried to type as long it will type - it
wouldn't
type beyond 32767 characters.

In my coworker situation I had to trunk text block to the width of the
cell
with vbCrLf (if my memory serves me well)

AvP




"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489.
It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working
as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

Hi Charles,

Thanks for confirming that.

I wonder if the formula length might be limited by Excel's internal count
and numbering method of the "items" in a formula, rather than the actual
length of the formula. If so, this would be akin to defined names whose
actual length limit is not related the 2xx string limit. I guess academic as
probably not possible in practical terms ever to reach such a limit.

Regards,
Peter T


"Charles Williams" wrote in message
...
Hi Peter,

Your very long formula works OK for me using excel97, excel2000 and
Excel2002.
Check the truncation when in R1C1 mode: I get 1024

I suspect that the internal limit for functioning formulae is probably
based
on the parsed version of the formula rather than the text version, and the
parsed form contains numeric references to the sheet name rather than the
sheet name itself: cant check in the SDK because I have lent my copy to
someone.

But there are limits on the size of formula string (and FormulaArray
string)
that can be entered without using the namechange trick.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

We appear to get different results. I've doubled checked and the "long"
formula with long sheet name still works, on manual recalc or if I
change
any of the values on Sheet2 A1:A161. I've even pasted back the code from
my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly 1000
after changing the sheet name, not 1023. The original formula length is
1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489.
It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working
as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

OK, the code still errors for me. However, the formula does continue to
work . . .

I'm confused! How can the code error yet the formula work ?

So there may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least to
me).

I doubt anyone would or should want to create such a formula of 5000+. But
knowing about this might help a few. A long time ago I had manually created
a formula with lots of functions and references. Big but not exceptionally
so. A while later came to edit it but couldn't. In the meantime I had
renamed sheets with much longer names causing formula to exceed the 1024,
probably only by a little. Temporarily renaming the sheets did the trick,
but it took a while to figure it.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
OK, the code still errors for me. However, the formula does continue to
work as long as you don't edit it and hit enter rather than escape. I did
edit it to see what it looked like - thus my initial response. So there
may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least to
me).

--
Regards,
Tom Ogilvy

Looks like the Excel formula parser has a limit of 1024 characters (in R1C1
mode), anything that invokes the parser seems to hit this. Copy and paste of
a formula or filldown drag of a formula does not invoke the parser, but any
edit or enter of a formula does.

Sometimes the truncation that results from this limit yields a valid but
incorrect result, sometimes it results in a #Name error, depending on where
the formula gets truncated.

And there is still the VBA limit of 255 characters with .FormulaArray, for
which the renameing sheets trick can often be a useful workaround.

But as Tom points out the msgbox in your code errors because you can't use
VBA to get the length of the very-long formula.


regards
Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

"Tom Ogilvy" wrote in message
...
OK, the code still errors for me. However, the formula does continue to
work as long as you don't edit it and hit enter rather than escape. I did
edit it to see what it looked like - thus my initial response. So there
may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least to
me).

--
Regards,
Tom Ogilvy


"Peter T" <peter_t@discussions wrote in message
...
Hi Tom,

We appear to get different results. I've doubled checked and the "long"
formula with long sheet name still works, on manual recalc or if I change
any of the values on Sheet2 A1:A161. I've even pasted back the code from
my
post. Also saved reopend a test file. No errors.

Another difference - for me the "long" formula truncates to exactly 1000
after changing the sheet name, not 1023. The original formula length is
1019
(before renaming the sheet).

The 5849 (typo 5489) characters is the calculated length of the formula
after changing the sheet name from "z" to the long name.

Does it work / not work for anyone else ?

Regards,
Peter T


"Tom Ogilvy" wrote in message
...
that just caused an error on the first message box. (xl2003). The
formula
was truncated to 1023 characters. what is the significance of 5489.
It
appears the limit is 1024 as stated in help. If you do a recalc, you
will
see the truncated formula isn't functional (it isn't secretly working
as
it
appears you are trying to imply).

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions wrote in message
...
typo:

...length of 5489:
should read
length of 5849

Peter T

That work is done when it errors on smsg.

See, no practical use. (generally renaming sheets to edit formulas would not
seem practical). <g

No argument it might be useful knowledge.
--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions wrote in message
...
OK, the code still errors for me. However, the formula does continue to
work . . .

I'm confused! How can the code error yet the formula work ?

So there may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least
to
me).

I doubt anyone would or should want to create such a formula of 5000+. But
knowing about this might help a few. A long time ago I had manually
created
a formula with lots of functions and references. Big but not exceptionally
so. A while later came to edit it but couldn't. In the meantime I had
renamed sheets with much longer names causing formula to exceed the 1024,
probably only by a little. Temporarily renaming the sheets did the trick,
but it took a while to figure it.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
OK, the code still errors for me. However, the formula does continue to
work as long as you don't edit it and hit enter rather than escape. I
did
edit it to see what it looked like - thus my initial response. So there
may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least
to
me).

--
Regards,
Tom Ogilvy

That work is done when it errors on smsg.

Ah. If you mean the code breaks on smsg then there would appear to be an XL
version difference reading the long formula.

In my XL2000 the code runs to the end without error, after showing the smsg.

Debug.Print sMsg '
161 additions
Original formula len 1019
Actual formula len 5849
Readable formula len 1000

Obviously the last line is the truncated formula string length, but also
part of the purpose of the demo. Namely to illustrate what I said in my
original post:

"Even though the formula could be considerably more than 1024, you cannot
"read" more
than the first 1000+ characters."

to which I should have added - "and neither can it be edited", as you have
pointed out.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
That work is done when it errors on smsg.

See, no practical use. (generally renaming sheets to edit formulas would
not
seem practical). <g

No argument it might be useful knowledge.
--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions wrote in message
...
OK, the code still errors for me. However, the formula does continue
to
work . . .

I'm confused! How can the code error yet the formula work ?

So there may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least
to
me).

I doubt anyone would or should want to create such a formula of 5000+.
But
knowing about this might help a few. A long time ago I had manually
created
a formula with lots of functions and references. Big but not
exceptionally
so. A while later came to edit it but couldn't. In the meantime I had
renamed sheets with much longer names causing formula to exceed the
1024,
probably only by a little. Temporarily renaming the sheets did the
trick,
but it took a while to figure it.

Regards,
Peter T

"Tom Ogilvy" wrote in message
...
OK, the code still errors for me. However, the formula does continue
to
work as long as you don't edit it and hit enter rather than escape. I
did
edit it to see what it looked like - thus my initial response. So
there
may
be no internal limitation on the formula length, but that
knowledge/capability doesn't seem to have much practical use (at least
to
me).

--
Regards,
Tom Ogilvy