Excel is recalculating after editing code

[stduc]

Suddenly, or so it seems to me, Excel is doing a full recalculate if I change some code. Even simply adding a comment to some VBA code will do it.

I can't for the life of me think what I may have (inadvertently) done to trigger this behaviour. Or is this normal - because at the same time the workbook has grown to take a fair few minutes for a full re-calculate.

 

[SkipVought]

hi,

Is this a function that is used on a sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[stduc]

This is rather complicated workbook now. But to answer your question I was just commenting a function called on workbook close. Suddenly I get the 'hour glass' and had to wait a while! If I remember I set calculation to manual before working on the macros. This time I forgot!! Thought I would post this question whilst I waited.

 

[stduc]

As a further explanation. I just had to modify the code in a macro that is called from a button in one sheet only. I set the workbook to manual recalc before I started. Once finished and tested I reset the workbook back to auto calculate and the whole workbook recalculated. Why? As no worksheet was changed in any way.

It is a 9 worksheet workbook.

 

[SkipVought]

Look in Excel HELP regarding Formula and Name Basics, to see when Excel recalculates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[stduc]

I believe I have solved my problem with judicious use of

Sheets("Sheetname").EnableCalculation = False

and

Sheets("Sheetname").EnableCalculation = True

Both in the Open macro and sprinkled elsewhere.

 

[Welshbird]

I set automatic calcuation to be false, then force it within each chunk of code whereever its appropriate, and the set it to be true again just before the worksbook closes.

Seems to work really well that way.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen