Which bad practices/operations greatly slow down VBA macros?

AmericanSeiko

New Member
Joined
Sep 16, 2013
Messages
21
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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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

Active Member
Joined
Jun 10, 2004
Messages
343
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

New Member
Joined
Sep 16, 2013
Messages
21
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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649

Watch MrExcel Video

Forum statistics

Threads
1,096,291
Messages
5,449,494
Members
405,568
Latest member
leonsky119

This Week's Hot Topics

Top