Re: Length limitation of cell formulas

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

> 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" <twogilvy@xxxxxxx> wrote in message
news:u8Knj$dRFHA.2136@xxxxxxxxxxxxxxxxxxxxxxx
> 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
> news:OcdmNLdRFHA.3296@xxxxxxxxxxxxxxxxxxxxxxx
> > > 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" <twogilvy@xxxxxxx> wrote in message
> > news:#j5awpcRFHA.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
> > >
> >
> >
>
>


.

Quantcast