Excel VBA - crazy re-re-re-recalculating during save

[dnky]

I've written a function which takes a range of usually a few thousand cells, puts them in an array, sorts them and finds a particular located cell and returns that value. The sorting takes a moment to complete so the calculation usually takes approx a second.

Then I noticed that automatic calculations, such as when you save, take minutes rather than seconds to recalculate so I decided to put in a MsgBox to tell me when the function is run and for some reason each calculation is being repeated an incredible amount of times! I can't understand why because even doing a Shift+F9 doesn't seem to cause the same amount of recalculating.

Can anybody give me any tips on how to find the cause and possible solutions. It's getting ridiculous waiting so long for the constantly repeating recalculations.

Please help!

For your info I'm using Excel XP and I've switched to Manual Recalculating within the Function just to be sure it's not that.

 

[dnky]

This morning it seems a bit more obvious. The cells in the range are not just values but formulas and it would make sense that as each cell in the range has its formula recalculated it is then triggering the function to recalculate also - i.e. if the function is focused on 1000 cells containing formulas, the function is recalculated 1000 times over.

I think i've discovered the cause. Any tips on how to prevent it would be awesome - other than the obvious: avoid using formulas in the cells.

thanks

 

[SkipVought]

Hi,

Post your function code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}