Which bad practices/operations greatly slow down VBA macros?

[AmericanSeiko]

Hello

I've been writing long, computationally expensive macros lately, and have noticed that VBA is considerably slower at certain types of computations than others.

Copying/pasting, debug.prints, and Application.ScreenUpdating = True are some expensive operations that stand out to me.

I was wondering if you all knew others that should be avoided.

Looking forward to reading your tips/comments!

 

[hiker95]

AmericanSeiko,

What version of Excel and Windows are you using?

Can we see one of your macros, so that we may be able to suggest some improvements?

If posting VBA code, please use Code Tags - like this:

[code]

Paste your code here.

[/code]

 

[timfritsch]

In larger workbooks, calculation time can cause delays. As long as you don't immediately need the result of the calculation, you can set to manual (xlCalculationManual). Otherwise, you can recalc just a single cell or small range instead of the entire workbook.


Tim

 

[AmericanSeiko]

Thanks for the replies.

Good idea about setting the calculations to manual. That's what I was wondering about. General Excel/VBA practices that seem obvious to veteran spreadsheet jockeys but are not truisms to novices.

I'd post my macros, but I wouldn't want you all to waste your time trying to optimize them as they are constantly changing.

Thanks again!

 

[hiker95]

AmericanSeiko,

You are very welcome. Glad we could help.

And, come back anytime.

I've been writing long, computationally expensive macros lately, and have noticed that VBA is considerably slower at certain types of computations

See if something in the below link will assist you.

Training / Books / Sites as of 10/15/2013
http://www.mrexcel.com/forum/genera...-visual-basic-applications-2.html#post3604871