General Macro Speed Question

[dturgel]

I'm stumped on some code I'm running. I have two workbooks that are for all intents and purposes identical. I have a macro I am running in both of them that has identical code. In one workbook it takes six seconds, in the other 1 minute and 20 seconds. Does anyone have any general tips or ideas for me to check out that I may be missing that is causing one to take so much longer? I already compared the formulas and advanced tabs in excel options and those are identical. Anything else you would check out?

 

[dazfoz]

Is the data in both the same?

Is the file format the same?

Running on the same machine/server/network?

Did you copy the code from one into the other and get the differing results?

Look in VBA Manager, are there any hidden or very hidden sheets?

 

[shg]

Perhaps you have volatile formulas in the slow one that recalculate every time the macro writes to a worksheet.

 

[dturgel]

Thanks shg! I don't think that's going on here but just to verify, would I then see the following code in one and not the other:
Application.Volatile True
?

 

[shg]

Volatile formulas, not code (though UDFs could also be volatile, either by their coding or arguments).

Formulas that use TODAY, NOW, CELL, OFFSET, INDIRECT, or {SUM|COUNT|AVERAGE}IF(S) with improper references, are all volatile, which means they recalculate any time there is any change in any workbook, whether they need to or not.

 

[dturgel]

shg,
Thanks - I do have a ton of offset in the formulas and a little bit of sumif.
So how do I determine whether or not the references are improper (I'm sorry I don't know exactly what you mean by this). And then what do you recommend I do to alter this to accelerate the run?