Hi
Background:
I have a rather large excel 2007 workbook (4.8mb) with plenty custom functions, array formulas, volatile functions (INDIRECT method to be specific) and macros. It is permanently in Manual calc mode. The sheet (with its add-ins) was created in excel 2003 but the sheet has since been converted to excel 2007's .xlsm format. Add-ins are still .xla format. Im using Windows 7 and 32-bit MS Office.
Question:
The workbook is very erratic when trying to run macros which basically run the .Calculate method on Ranges with a number of the custom functions, array formulas and volatile functions. By erratic i mean the sheet will either work for a while and then either say "Not Responding", will go grey and ask me to "restart the program" or the command buttons sometimes do not even execute the code. When this happens and i open the VB Editor i can step through the code but nothing executes. The total EXCEL.EXE memory usage per Windows Task Manager never exceeds 1.3GB, meaning there is still 700MB free.
Has anyone experienced something similar post converting a large sheet from excel 2003 to excel 2007? Could it be a memory issue, and that recalculation is pushing memory usage over the 2GB limit?
FYI - i have been researching the issue for a good few days now and probably the most helpful article was this...Improving Performance in Excel 2007. From the article the only thing i can think might solve my sheet problems is to reduce the number of array formulas and/or volatile functions. But this is really a last resort. Hopefully someone has overcome a similar problem and can assist?
Thanks
Jasen
Background:
I have a rather large excel 2007 workbook (4.8mb) with plenty custom functions, array formulas, volatile functions (INDIRECT method to be specific) and macros. It is permanently in Manual calc mode. The sheet (with its add-ins) was created in excel 2003 but the sheet has since been converted to excel 2007's .xlsm format. Add-ins are still .xla format. Im using Windows 7 and 32-bit MS Office.
Question:
The workbook is very erratic when trying to run macros which basically run the .Calculate method on Ranges with a number of the custom functions, array formulas and volatile functions. By erratic i mean the sheet will either work for a while and then either say "Not Responding", will go grey and ask me to "restart the program" or the command buttons sometimes do not even execute the code. When this happens and i open the VB Editor i can step through the code but nothing executes. The total EXCEL.EXE memory usage per Windows Task Manager never exceeds 1.3GB, meaning there is still 700MB free.
Has anyone experienced something similar post converting a large sheet from excel 2003 to excel 2007? Could it be a memory issue, and that recalculation is pushing memory usage over the 2GB limit?
FYI - i have been researching the issue for a good few days now and probably the most helpful article was this...Improving Performance in Excel 2007. From the article the only thing i can think might solve my sheet problems is to reduce the number of array formulas and/or volatile functions. But this is really a last resort. Hopefully someone has overcome a similar problem and can assist?
Thanks
Jasen
