Speed Up Your VBA Macro

For more complex financial models, macro can become an integral part of the calculations. Letting them operate can slow down the work - in extreme cases up to hours. This article explains ways to speed up your macros.

Logical optimisations

First of all, try to challenge your own way of solving problems. Sometimes you might find quicker, more efficient algorithms. There could not possible be a way of really writing a comprehensive tutorial on this - just try to always make sure if your solution is really the most efficient one.

Switch off automatic calculations

Whenever the content of one single cell changes, Excel makes sure all formulas that might or might not depend on that one are re-calculated. A macro that works directly in cells would trigger that event very often, therefore incurring a lot of possibly unneeded recalculations. With highly complex models, you will find yourself switching the recalculations from automatic to manual, anyway. This can be done in VBA, too:

Application.Calculation = xlCalculationManual

' Include your macro code here

Application.Calculation = xlCalculationAutomatic

However, when your macro needs input from cells that would necessarily be recalculated while the macro is working, insert a recalculation command:

Application.Calculate '... if you need the all open workbooks to be recalculated

ActiveSheet.Calculate '... if you want to recalculate the active sheet

Worksheets(yourWorksheet).Calculate '...if you want to recalculate a specific sheet, but not necessarily the active one

Range(youRange).Calculate '...if you want to calculate only a specific range

Switch off automatic screen updates

Similarly to recalculations, Excel will update the values it shows when something changes. For the duration of your macro, you can switch this off to save some processor time:

Application.ScreenUpdating = False

' Include your macro code here

Application.ScreenUpdating = True

Variable declarations

A lot of users are unaware of the fact that imprecise or missing data type declarations cost both memory and processing time. You should always declare your variables properly, including the data type:

Data typeSize limitPossible values
Boolean2 bytesTRUE or FALSE
Byte1 byteInteger values ranging from 0 to +255
Integer2 bytesInteger values ranging from -32,768 to +32,767
Long4 bytesInteger values ranging from -2,147,483,648 to +2.147.483.647
Currency8 bytesMonetary values (i.e. with 4 decimal digits) ranging from -922,337,203,477.5808 to +922,337,203,685,477.5807
Single4 bytesNegative decimal values from -3.402823E38 to -1.401298E-45, 0 (zero), or positive decimal values from +1.401298E-45 to +3.402823E38
Double8 bytesNegative decimal values from -1.79769313486231E308 to -4.94065645841247E-324, 0 (zero), and positive values ranging from +4.94065645841247E-324 to +1.79769313486232E308
Date8 bytesDate and time values ranging from January 1, 100 to December 31, 9999
StringAll text strings
Object4 bytesObjects
Variant16 bytesAll possible types - variables without a type declaration will automatically be considered "Variant"

But even developers that know about the importance of correct declarations will sometimes apply the concept incorrectly. Including just one type definition in one row of declarations is the typical mistake:

Dim a, b, c As Integer

While you would think that all three variables a, b and c are declared as Integer, in fact, only c is! Variables a and b would be declared as Variant, taking up more memory and CPU time. Instead, the fully correct declaration should look like this:

Dim a As Integer, b As Integer, c As Integer

Release memory from unused objects

If your macro tends to take up a lot of memory, make sure to release memory blocked by objects not needed further in the code, as soon as possible. It's done like this:

Set oYourObject = Nothing

Working with cells without selecting them

A lot of VBA developers select single cells like this:

Range("A1").Select
ActiveCell.Value = "Example"

Instead, this way is much faster:

Cells(1, 1).Value = "Example"

Visible feedback to the user

"Has your Excel crashed?" - "I don't know, let's wait for another minute." - If this is a typical user reaction to your macro, you need to include some visible feedback to the user to show the current status. For example, if you have a data set of 10,000 items, show something like "Currently processing item 1,000 out of 10,000" in a small dialogue or the status bar and refresh it accordingly. The status bar can be accessed like this:

Application.DisplayStatusBar = True
Application.StatusBar = "Currently processing item " + iCurrent + " out of " + iTotal

In that case, iCurrent would be the counter giving the current item, and iTotal the total number of items to be processed.

Comment your code

One important thing: Keep in mind to always put comments in your macros. This is especially important when your financial model might be adjusted by someone else or when it is used by someone not familiar with VBA or your code. And even if you are the only person to ever use that macro, if you want to change functionality or optimise speed after some time, you will appreciate your comments yourself.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.