formulaArray limit of 255 chars - what workarounds exist?

[AC]

Hi

We have run into what appears to be a well known issue: we have array
formulas much longer than 255 chars.

These formulas are not designed/supplied by us, all we know is that
they work. What is happening is we have code that munges the array
formulas temporarily into a regular text string, some other code (not
controlled by us) then deletes worksheets and imports new data and
does a whole lot of other stuff, and we then 'unmunge' the formulas
back again once the worksheets are reinstated.
Without doing this the formulas turn into #REF when the other code
deletes the worksheets (we know the worksheets are coming back again
but once the #REF appears its too late).

Generally the array formulas are less than 255 chars so they work OK,
however we have just started running into issues where they are longer
than 255 chars.

Is there a known work around for this problem? Im sure we will run
into this issue again for future projects so would like any known
generalised workarounds rather than suggestions on how to change what
we are currently doing (not that we dont appreciate suggestions - its
just that I want to learn a generalised fix )

I have seen one suggestion which says to add it as a normal formula
and then use send keys to 'ctl-shift-enter'. Are there better fixes
out there?

Thanks in advance
AndyC

 

[Dave Peterson]

If I were using VBA...

Take a look at Dick Kusleika's site:
http://www.dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

It's a technique to workaround that .formulaarray length limit.