By Kevin Roper
Do you have an Excel VBA model that runs for a long time and you don’t know why? The first step in resolving the problem is identifying the section of code that is slow. This article presents a few simple techniques to find the bottleneck.
In order to record elapsed time, you need an accurate function to track time. The VBA functions Now and Time are only accurate to a second. Many VBA processes run much faster than that. The VBA Timer (not Time) function is typically accurate to 1/256th of a second on Windows based PCs. This degree of precision should be accurate enough for most applications. The Timer function counts the fractional number of seconds elapsed since midnight. So, if you know the Timer value at the start of a procedure and the Timer value at the end of a procedure, the difference in the two values is the number of seconds it took for the procedure to process. Below is an example:
StartTime = Timer
Your Code Here
ElapsedTime = Timer – StartTime
The variable ElapsedTime will contain the number of seconds it took to process your code. Note that since the Timer function provides the number of seconds since midnight, be careful using it on jobs that run overnight. StartTime and ElapsedTime should be data type Single.
If the Timer function is not precise enough, you can use Windows API calls to the system high–resolution timer. The high–resolution timer is built into most, but not all CPUs. The high–resolution timer has two important pieces of information: the number of elapsed counts and the number of counts per second. So, if you know that a given process took 100 counts and the frequency of the high–resolution timer is 250,000 counts per second, the elapsed time is 0.0004 (100 / 250,000) seconds.
The level of precision for the high–resolution timer will vary depending on your CPU and cannot be changed. For example, the high–resolution timer on my PC has a frequency of 2,467,958. I can capture processes as fast as 1/2,467,958th of a second or about .0000004 seconds. Thus, the high–resolution timer can be used to evaluate very small blocks of code. Below is a function called MicroTimer that uses the high–resolution timer. It was written by Charles Williams in his excellent articles on the Microsoft Developer Network titled, “Improving Performance in Excel 2007” and “Excel 2010 Performance: Improving Calculation Performance.” MicroTimer uses the QueryPerformanceCounter and QueryPerformanceFrequency API calls to track elapsed time. It is very accurate and very efficient.
Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
' Initialize MicroTimer
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds = Ticks (or counts) divided by Frequency
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
Note that both cyTicks and cyFrequency are defined as Currency. This is because these values are passed as 8–byte integers. In VBA the data type Integer is 2–byte and Long is 4–byte. There isn’t an 8–byte integer option. Currency is 8–byte. Even though Currency contains decimals, the decimals are effectively removed by the division statement. If you want to know your system’s high–resolution timer frequency, multiply the cyFrequency variable by 10,000 (Currency is stored with 4 decimal places).
You can use MicroTimer just like the Timer function except that StartTime and ElapsedTime should now be data type Double.
StartTime = MicroTimer
Your Code Here
ElapsedTime = MicroTimer – StartTime
Actuarial models frequently contain loops. You may be looping through scenarios, in–force records, or projecting values over time. You may want to know the cumulative amount of time spent processing some block of code. With a minor adjustment you can track the cumulative amount of time spent processing the code.
StartTime = MicroTimer
Your Code Here
ElapsedTime = ElapsedTime + MicroTimer – StartTime
If you are testing several blocks of code at the same time, an easy way to keep them straight is to change ElapsedTime to an array. Then simply place the commands shown above around each block of code to be tested. Here is an illustrative example:
Sub Model_Office()
Dim StartTime As Double, ElapsedTime() As Double
ReDim ElapsedTime(4)
For Input_Record = 1 To 10000
StartTime = MicroTimer
Call ReadInputRecord
ElapsedTime(1) = ElapsedTime(1) + MicroTimer – StartTime
StartTime = MicroTimer
Call GenerateScenarios
ElapsedTime(2) = ElapsedTime(2) + MicroTimer – StartTime
StartTime = MicroTimer
Call CashFlows
ElapsedTime(3) = ElapsedTime(3) + MicroTimer – StartTime
StartTime = MicroTimer
Call Reserves
ElapsedTime(4) = ElapsedTime(4) + MicroTimer – StartTime
Next Input_Record
For i = 1 To 4
Worksheets("Sheet1").Cells(i , 1) = ElapsedTime(i)
Next i
Hopefully these simple tools will help you to dig into those slow models to find the bottlenecks. Once you have identified the offending code, there are numerous resources on the Internet to help make the code more efficient. As a starting point you may want to read my article titled, “Excel VBA Speed and Efficiency” published in the February 2012 edition of CompAct.
Kevin Roper, FSA, MAAA, is an Assistant Vice President at Transamerica working on annuity product development. He can be reached at kevin.roper@transamerica.com.