Hi all,

is there a length limitation for formulas?

Thanks for any reply.

Paul

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

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

maybe 255

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

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