Re: Length limitation of cell formulas

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <Charles@xxxxxxxxxxxxxxxxxx> wrote in message
news:#$Gh6ecRFHA.648@xxxxxxxxxxxxxxxxxxxxxxx
> 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 *** name rather than the
> *** 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
> 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>


.

Quantcast