PDA

View Full Version : turning off calculation on all worksheets



kualjo
05-08-2013, 09:00 AM
My macro has the simple task of going down a column of dates, determining if all dates are present, and inserting a row and adding the date anywhere it is needed. It stops at the bottom of the date list. The problem I have is that there are a number of other worksheets that contain numerous INDEX/MATCH formulas, all of which are calculating based on the dates on the first worksheet. Everytime a new row inserts, every INDEX/MATCH formula - several thousand of them - calculate before allowing the macro to continue. This takes an inordinate amount of time to run. I have the code Application.Calculation = xlManual at the beginning of the code, but I'm thinking this only applies to the active worksheet. How can I stop all calculation on all worksheets until the macro is finished? (Application.Calculation = xlAutomatic is the last line of code.)

SamT
05-08-2013, 05:22 PM
I have always thought that Application.Calculation set the entire application... Anyway I went online to do some reading and this bit of code at jpsoftwaretech (http://www.jpsoftwaretech.com/excel-vba/calculation-mode-excel-optimization/) made me think that you might try setting Application.EnableEvents = False.

Teeroy
05-09-2013, 06:11 AM
You could disable calculation on the sheets that have these formulae (or loop through all sheets) e.g.

Sheets("sheet1").EnableCalculation = False

Make sure that you re-enable calculation (in the error handler would a good place) as the property change is persistent.

kualjo
05-09-2013, 08:28 AM
Thanks for the suggestions. Unfortunately, neither one worked. I experimented a little bit to see if maybe it was just taking a long time to insert a row, but when I deleted the sheets with all the INDEX/MATCH formulas, the macro ran in the blink of an eye. The time it takes to run is proportional to the number of calculation worksheets needed. Somehow, even with calculation turned off, it is still trying to do the math with every change to the main worksheet. Guess I'll keep searching. Thanks again!