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 type | Size limit | Possible values |
|---|---|---|
| Boolean | 2 bytes | TRUE or FALSE |
| Byte | 1 byte | Integer values ranging from 0 to +255 |
| Integer | 2 bytes | Integer values ranging from -32,768 to +32,767 |
| Long | 4 bytes | Integer values ranging from -2,147,483,648 to +2.147.483.647 |
| Currency | 8 bytes | Monetary values (i.e. with 4 decimal digits) ranging from -922,337,203,477.5808 to +922,337,203,685,477.5807 |
| Single | 4 bytes | Negative decimal values from -3.402823E38 to -1.401298E-45, 0 (zero), or positive decimal values from +1.401298E-45 to +3.402823E38 |
| Double | 8 bytes | Negative decimal values from -1.79769313486231E308 to -4.94065645841247E-324, 0 (zero), and positive values ranging from +4.94065645841247E-324 to +1.79769313486232E308 |
| Date | 8 bytes | Date and time values ranging from January 1, 100 to December 31, 9999 |
| String | All text strings | |
| Object | 4 bytes | Objects |
| Variant | 16 bytes | All 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.