Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulaArray limit of 255 chars - what workarounds exist?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formulaArray limit of 255 chars - what workarounds exist?
If I were using VBA... Take a look at Dick Kusleika's site: http://www.dailydoseofexcel.com/arch...rmulas-in-vba/ It's a technique to workaround that .formulaarray length limit. AC wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
limit # of chars in a cell | Excel Discussion (Misc queries) | |||
Building a FormulaArray using VBA - Size Limit? | Excel Programming | |||
Autosize bug workarounds in Excel 2007 shape textframes | Excel Programming | |||
.PageSetup: Any recent workarounds for the speed problem? | Excel Programming |