What causes a macro to run slower at certain times?

[Wegener]

I couldn't find anything that really answered this question specifically. When I run macros usually they're fast, but occasionally will run very slow. An example macro I have is pasted below. It's just a simple macro that pastes months. If I am pasting on top of a cell that contains a month it pastes the rest of the months that follow.

Most of the time this macro runs very fast, but occasionally it takes about 1-2 seconds to run, and it is a very simple macro! It is not macro specific, as all my macros will be slow. I''m using Windows 7 with Excel 2010. I'm only using 23% of my CPU and 4.5GB of my 16GB of RAM, so I don't think it's a hardware thing.

Is there some kind of condition that occurs that will make excel run macros slower? Any help is appreciated.

Please Login or Register  to view this content.

[Bernie Deitrick]

It depends on what else is open - You should get into the habit of starting your code with this to prevent the slowdown that making changes to a cell by code can start:

Please Login or Register  to view this content.

And then finish up with this:

Please Login or Register  to view this content.

[Wegener]

It depends on what else is open - You should get into the habit of starting your code with this to prevent the slowdown that making changes to a cell by code can start:

Please Login or Register  to view this content.

And then finish up with this:

Please Login or Register  to view this content.

Thanks for these tips! Sped up my macro about 5x.

I also closed a workbook with a lot of calcs in it and it ran a lot faster without the above tricks. Does every workbook recalc formulas when a macro runs? That would make sense to me why turning off auto calculation would speed things up.

[Bernie Deitrick]

All workbooks can be re-calced, so having few or no others open is best: not always possible.

The code captures whatever your calculation mode is currently using the line

xlCalc = .Calculation

It then turns it to manual, then final resets to your selection. You never want to assume a user prefers one over the others.

[ukmikeb]

Hi

Further to the comment within your code here is a variation :-

Please Login or Register  to view this content.

hth