Re: Length limitation of cell formulas

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 ***(s) you can rename ***(s) to something very
> short, build your formula string with short names, apply the formula, then
> restore original long *** 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" <paul.schenker@xxxxxxxxxx> wrote in message
> news:42664539$1_3@xxxxxxxxxxxxxxxxxx
> > Hi all,
> >
> > is there a length limitation for formulas?
> >
> > Thanks for any reply.
> >
> > Paul
> >
> >
>
>


.

Loading