Speeding up VBA

Many people think VBA is slow, but it's actually pretty fast. Let me demonstrate.

Guess how long that took? A grand total of 0.08 of a second (1.8mhz PC with 512meg of RAM). Yes, I could have done it 12 times over, in a single second. Now how fast do you need Excel to be? Sure, C++ can do it even faster, but most of the time, you can build models much faster in Excel, and the speed is good enough.

Here's the code I used:

Sub test()
    Dim V As Variant
    Dim i As Long, j As Long
    V = Cells(1, 1).Resize(10000, 10) 'this takes 0.035 secs
    Dim arrOut(10000, 1) As Single
   For i = 1 To 10000 'this loop takes 0.025 secs
        For j = 1 To 10
            arrOut(i, 1) = arrOut(i, 1) + V(i, j)
        Next j
     Next i
    Cells(1, 11).Resize(10000, 1) = arrOut 'this takes 0.02 secs
End Sub

Don't mix Excel and VBA

The key (for me, anyway) is to minimise the number of times that VBA and the worksheets talk to each other, because there is a definite timelag when you do this. This means that if you have to read in data, try to do it in one go, and when you write results back to a worksheet, try to do it in one go. If you read/write cells one at a time, you might as well go for a long lunch.

Reading in data

Here is how I read in a table in cells A1:C1000, which I've given the range name Data

Dim V as variant
V = Range("Data")

Note that

If you are going to do a lot of work with the data, such as using it many times over and over again, you might consider moving the data from V into an array of Long, Single or whatever, because they are faster than Variants. See below for more on data types.

Writing data

Suppose I've done my work, and I want to write the array A(10,2) back to the sheet starting at cell C5, which I've named "Results".

Range("Results").Resize(10,2) = A

The Resize command lets me pick the starting cell (the range called Results) and then tell VBA how many rows and columns to use. I like Resize because it is easy to read the code and see how big my table is.

Note that

I've seen cases where writing the results out in one go, instead of one row at a time, speeds up execution by as much as 100x.

Use range names

I'll cover this fully somewhere else, but note how i've used range names above. I never hard code a cells address (eg "C5") in VBA, because then if I add a row/column in the spreadsheet, my code will break, whereas a range name will update its address and will still work.

Minimise the use of worksheet functions

So you find VBA doesn't have a MIN function, but Excel does, and you can run Excel functions using Application.WorksheetFunction.

Suppose instead you wrote your own MIN function

Function Min(a as Long, b As Long) As Long
    If a<b Then Min = a Else Min = b
End Function

You wouldn't expect it to run faster than a built-in Excel function, but remember, you are talking to Excel from VBA, and that slows things down. My testing suggests the code above will run 50 (fifty) times faster than the worksheet function, simply because you are mixing VBA and spreadsheet functions.

Similarly, if you write a VBA function to use in a worksheet cell, expect it to run much slower than if you use an ordinary Excel function, or if you did the whole thing in VBA. It won't matter if you only call it a few times, but if it's a few hundred times, you'll see the difference. It's mixing VBA and Excel together that slows you down.

Memory is persistent

Suppose you have a module with this code

Option Explicit 'if you don't know what this is, shame on you. find out and turn it on!
Dim V As Variant

Sub MainCalculation()
    V = Range("Some_Data")
    DoCalculate 'some code to use the data
End Sub

Suppose you run this sub. When it's finished, V will still hold all the data you read in from the worksheet, because it's defined at module level. If you defined V inside the Sub, it would be deleted when the sub finished, and, if you aren't going to need the data again, this would be the best way to go.

However, suppose you are going to run this routine a few times, and that while reading data into memory is fast, we have to clean it up or process it before we can do any calculations, or that it is coming from a database, which takes a few seconds, say. Wouldn't it be good if we didn't have to read the data in each time? Well, because VBA keeps V in memory, all we have do is check whether it contains anything or not, like this.

Sub MainCalculation()
   If IsEmpty(V) = TRUE Then  V = Range("Some_Data") 'only read in data if we don't have it
    DoCalculate 'some code to use the data
End Sub

Now the data will only be read in if necessary. I use this trick all the time to avoid reinitialising where I don't have to.

How much is too much?

If you are going to read everything into VBA, is there a limit to how much you can hold in memory? Of course, but it is pretty big. I've held a couple of million numbers in memory before, without problems. Test and see what works for you.

Type your variables

Excel has several variable types to choose from. Do you think it makes much difference which types you use in your code? For example, which of the Boolean, Integer, Long, Single and Double types is fastest?

I thought intuitively that smaller variables (Boolean, Integer) would be faster, but that's not true. The fastest is Long, because it is 32 bit, like the PC itself. Shorter variables have to be broken up, and longer ones have to be put together, so the fastest is the "native" 32 bit format. It's not a huge difference, on my tests (see below), and there's isn't a lot of difference between all of the other types, except Variant.

Variant is definitely the slowest, because VBA has to stop and figure out what type of variable to use, each time you use avariant in code. So avoid variants where you have big loops.

Trap: What type are these variables?

Dim A, B, C As Long

A & B are Variants, and C is Long. You have to specify the type for each variable, or VBA will assume Variant. So you have to do this. It might be safer to put each on its own line, then you'll never get it wrong.

Dim A As Long, B As Long, C As Long


Control the Excel interface

A lot of speed problems occur when VBA interacts with the worksheet, and triggers recalculation or forces the screen to keep redrawing. You can improve speed significantly by controlling the user interface.

Recalculation

You can turn off calculation, and turn it on again at the end, to prevent Excel recalculating every time you put a cell in the worksheet. Just remember to turn it back on!

Application.Calculation = xlCalculationManual
'some code here to do the work, then.....
Application.Calculation = xlCalculationAutomatic

You can force calculation at any point in your code, using the Calculate function. You can also calculate just a single sheet at a time.

Screen Updating

Iif you code is switching between sheets, it can slow things down, and all the screens leaping around can be a nuisance for the user. Try this

Application.ScreenUpdating = False

Note you don't need to turn it back on - it will happen automatically when the code is finished.

Keeping the user informed

Humans are impatient, and we like to see something happening. If something is going to take a while, it's a good idea to provide some kind of progress indicator.

You can do all sorts of fancy things, and I have some ideas on this site, but for speed, you can't go past using the Excel statusbar, like this

For i = 1 to nRecords
    'code to do some work
    If i mod 1000 = 0 Then Application.StatusBar = "Processed " & i & " records "
Next i

Application.StatusBar = False 'release the status bar so Excel can use it

The status bar is very simple to use - just remember to give it back to Excel when you've finished.

Note also that I'm only providing feedback every 1000 records (using the Mod function), because it's only necessary to update the statusbar every couple of seconds or so. Doing it for every record would just slow everything down, and the statusbar text would change so fast it would be hard to read.

The Macro Recorder writes dreadful code

If you want to do something like programmatically adjust a pivot table, the macro recorder is great for giving you the names of the objects and the commands you need, but it writes dreadful code.

For example, this is the code to copy one cell to another:

Range("C2").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

when you could just write Cells(2, 1) = Cells(2, 3)

So the macro recorder can save a lot of time, but always check the code carefully to see if you can clean it up.

 

Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.