Re: Length limitation of cell formulas

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" <twogilvy@xxxxxxx> wrote in message
news:%23j5awpcRFHA.3928@xxxxxxxxxxxxxxxxxxxxxxx
> 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
> news:e5fO9BcRFHA.3972@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi Tom,
>>
>> We appear to get different results. I've doubled checked and the "long"
>> formula with long *** 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 *** name, not 1023. The original formula length is
> 1019
>> (before renaming the ***).
>>
>> The 5849 (typo 5489) characters is the calculated length of the formula
>> after changing the *** name from "z" to the long name.
>>
>> Does it work / not work for anyone else ?
>>
>> Regards,
>> Peter T
>>
>>
>> "Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
>> news:eQw#aybRFHA.2356@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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
>> > news:eTlBEZbRFHA.3144@xxxxxxxxxxxxxxxxxxxxxxx
>> > > typo:
>> > >
>> > > > ...length of 5489:
>> > > should read
>> > > length of 5849
>> > >
>> > > Peter T
>> > >
>> > >
>> >
>> >
>>
>>
>
>


.