Excel VBA Speed and Efficiency

https://www.soa.org/News-and-Publications/Newsletters/Compact/2012/january/com-2012-iss42-roper.aspx

 

Excel VBA Speed and Efficiency

by Kevin Roper

As the proud owner of several large VBA macros, I have spent a considerable amount of time looking for ways to make macros run faster. This article lists my top rules for speeding up VBA. It is easy to lapse into bad programming habits when working with small macros, but with large macros and macros that run a long time it is critical to use efficient coding. This article is primarily focused on Excel VBA macros, however many of these rules apply to Microsoft Access VBA macros as well.

The first five rules generally have the largest impact on macro performance. Rules six through 11 have a marginal impact. Please note that my estimates of time savings below may vary significantly for your specific application. The analysis used Excel 2007.

Rule #1. Turn off automatic spreadsheet calculation

This rule is well known, but it is the most important rule. When a new value is entered into a worksheet cell, Excel will recalculate all the cells that refer to it. If the macro is writing values into the worksheet, VBA will need to wait until the worksheet is done recalculating each entry before it can resume. The impact of leaving automatic calculation turned on can be dramatic. I highly recommend turning off automatic calculation using the following command at the beginning of the macro.

Application.Calculation = xlCalculationManual

If you need to recalculate spreadsheet values while the macro is running you can use any of the following commands. The first command recalculates the entire workbook. The second command only recalculates a specific sheet. The third command only recalculates a specific Range.

Calculate
Worksheets("sheet1").Calculate
Range("A1:C5").Calculate

When the macro is done, automatic calculation needs to be turned back on using the following command. If the macro ends prematurely before this command is processed, you will need to manually reset calculation to automatic in EXCEL.

Application.Calculation = xlCalculationAutomatic.

Rule #2. Turn off screen updates

Every time VBA writes data to the worksheet it refreshes the screen image that you see. Refreshing the image is a considerable drag on performance. The following command turns off screen updates.

Application.ScreenUpdating = FALSE

At the end of the macro use the following command to turn screen updates back on.

Application.ScreenUpdating = TRUE

Rule #3. Minimize traffic between VBA and the worksheet

Once the macro begins it is important to avoid unnecessary references to the worksheet. Grabbing data from the spreadsheet is a drag on performance. Avoid reading or writing worksheet data within loops whenever possible. It is much faster to read the data once and save it into memory than to reread it each time.

In this example, the macro will need to grab the named Range "issue_age" from the worksheet repeatedly. This is a common mistake. VBA is much faster when it doesn't need to stop and interact with the worksheet.

For Duration = 1 To 100
Attained_Age = Range("Issue_Age") + Duration
Next Duration

In the following code the variable Issue_Age is read in only once from the worksheet and traffic between VBA and Excel is minimized. The code below is more than 100 times faster than the code above!

Issue_Age = Range("Issue_Age")
For Duration = 1 to 100
Attained_Age = Issue_Age + Duration
Next Duration

It is also more efficient to perform all numerical calculations in VBA. It is frequently tempting to leave formulas in the spreadsheet and call them from the macro. But, if speed is important, put all the formulas in the macro. This minimizes traffic and doesn't require spreadsheet recalculation.

As a general rule use commands WorkSheets, Range, Cells and Application as efficiently as possible outside of loops.

Rule #4. Read and write blocks of data in a single operation

This rule is a continuation of Rule #3. This is another way to minimize traffic between VBA and Excel. Whenever possible read and write data in chunks. There are several methods to accomplish this. Here is an example of reading in a large block of data (2,600 cells) into an array. This example is roughly 50 times faster than reading in each cell individually in a loop.

Dim myArray() As Variant ´ note that this must be a variant
myArray= Worksheets("Sheet1").Range("A1:Z100").value

Likewise, here are examples of writing the array back into the worksheet. All are roughly 40 times faster than writing each of the 2,600 cells individually within a loop.

Method #1

Worksheets("Sheet1").Range("A1:Z100").value = myArray

Method #2

With Worksheets("Sheet1")
.Range("A1:Z100").Value = myArray
End With

Method #3

Dim theRange As Range
Set theRange = Range("A1:Z100")
theRange.value = myArray

Rule #5. Avoid using certain Excel worksheet functions

This rule was surprising to me. I had naively assumed that common worksheet functions would be efficiently processed by VBA. This is clearly not the case. For example, most VBA users are probably aware that VBA does not have a Max() or Min() function. Excel does have these functions. It is common to use the following code that uses the Excel version of Max():

variable1 = Application.Max(Value1, Value2)

I found an open source version of a VBA Max() function on the Internet. It was 10 times faster than the Excel based counterpart above. However, the code below is over 80 times faster! I concede that the function below only works with two arguments and does not support arrays, but the improvement in speed is substantial.

Function Max2 (Value1, Value2)
If Value1 > Value2 Then
Max2 = Value1
Else
Max2 = Value2
End If
End Function

I suggest caution when using worksheet functions in large, time consuming macros. You should evaluate the impact of rewriting the function. Note that any command that starts with "Application." or "WorksheetFunction." is referring to an Excel function. I can't say that all "Application." functions are slow. But, I have written or downloaded versions of Min(), Max(), Average(), Match(), NormSInv() and StDev() that are much faster than the Excel versions.

Rule #6. Avoid using Variants in formulas

Do not declare a numerical variable as Variant unless necessary. Note that if you choose not to use "Option Explicit" at the beginning of the macro any undefined variable will be a Variant. Variants are very flexible because they can be numerical or text, but they are slow to process in a formula. The impact on efficiency is not large, but every little bit helps. Note that this rule also applies to any functions you write. Based on my tests the variable types from fastest to slowest in mathematical equations are: Constant, Single, Double, Long, Integer, Variant.

Rule #7. Avoid evaluating Strings

Strings (text) are slow to evaluate. Avoid evaluating Strings in code like this:

Select Case Gender
Case "Male"
(insert code here)...
Case "Female"
(insert code here)...
Case "Unisex"
(insert code here)...
End Select

Enumeration assigns a constant numerical value to a variable. VBA can process enumerated values quickly while maintaining readable code. Enumeration can assign default numerical values or specific values can be assigned.

Public Enum enumGender
Male = 0
Female = 1
Unisex = 2
End Enum
Dim Gender as enumGender

Select Case Gender
Case Male
(insert code here)...
Case Female
(insert code here)...
Case Unisex
(insert code here)...
End Select

Boolean operators are simply TRUE or FALSE switches that process really quickly. In the example below bMale, bFemale and bUnisex are Boolean variables. The Boolean code is roughly 10 times faster than using Strings.

If bMale Then
(insert code here)...
ElseIf bFemale Then
(insert code here)...
ElseIf bUnisex Then
(insert code here)...
End If

Rule #8. Don't select specific worksheets unless necessary

You generally do not need to use the Select command to read or write to a worksheet. It is about 30 times faster not to Select a worksheet.

Avoid this:
Worksheets("sheet1").Select
Amount1 = Cells(1, 1)

Do this instead:
Amount1 = Worksheets("sheet1").Cells(1,1)

Rule #9. Avoid overuse of StatusBar updates

VBA can process math faster than it can display the StatusBar. Writing to the StatusBar is another example of traffic between VBA and Excel. The following example writes one out of every 100 scenarios to the StatusBar. It is about 90 times faster than writing every scenario to the StatusBar.

For scenario = 1 To 10000
(insert code here)...
If Scenario mod 100 = 0 Then Application.StatusBar = Scenario
Next Scenario

Rule #10. Avoid unnecessary math

As actuaries we love macros full of formulas. Frequently the formulas are not as efficient as they should be. In the example below, the macro calculates the monthly value of a fund growing at 5 percent annual effective interest for 50 years. I frequently see code like this:

Interest_rate = .05
For i = 1 To 600
fund(i) = fund(i-1) * (1+interest_rate)^(1/12)
Next i

It is more efficient to convert the annual interest rate to monthly rate once as shown below. Within the loop, VBA only uses a single numerical operation (multiplication). The example above uses four numerical operations (one multiplication, one addition, one division and one exponentiation) within the loop and is therefore about four times slower.

Interest rate = .05
Interest_factor = (1+interest_rate)^(1/12)
For i = 1 to 600
fund(i) = fund(i-1) * interest_factor
Next i

Also note that exponentiation is slower than addition, subtraction, multiplication or division.

Rule #11. Don't Copy and Paste

The Copy and Paste (or PasteSpecial) functions are slow. It is about 25 times faster to use the following to copy and paste values.

Range("A1:Z100").value = Range("A101:Z200").value

Final Thoughts

I have found it useful to write a small macro to evaluate the time savings associated with various methods. The macro simply performs a method a million times or so and records the time spent performing that method. The simple macro below compares the Excel Max() function to the Max2 function shown in Rule #5.

'**Evaluate the first function
Start_time = Now
For i = 1 To 1000000
value1 = Application.Max(amt1, amt2)
Next i
End_time = Now
Worksheets("sheet1").Cells(1, 2) = End_Time — Start_Time

'**Evaluate the second function
Start_time = Now
For i = 1 To 1000000
value1 = Max2(amt1, amt2)
Next i
End_time = Now
Worksheets("sheet1").Cells(2, 2) = End_Time — Start_Time

Please contact me if you have other time-saving tips.

I also want to point out and thank several excellent articles on the internet that address this topic: