Static variables in VBA functions, like their counterparts in C, retain their values between function calls. A common use for static variables is to maintain a running total or count. This article describes how static variables can be used for cacheing in Excel VBA, compares the performance of a simple approach versus a cache in Excel, and some discussion for using this approach.
Excel applications often have read or write data in specific cells, columns or rows, which are identified by cells containing specific strings. A simple approach is to write a function that returns a cell reference given a string, as below.
Function GetHeaderFind(ByVal str As String) As Range
Set GetHeaderFind = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
End Function
If we know that the cells referenced do not change during the execution of the program, then we could find them when they are first required and cache their values in a Collection. The next time the same string is presented, we just return a reference from the Collection:
Function GetHeaderStatic(ByVal str As String) As Range
Static colRange As New Collection
Dim rng As Range
On Error Resume Next
Set rng = colRange(str)
If Err.Number <> 0 Then
Set rng = Sheet1.Range("A:A").Find(What:=str, SearchOrder:=xlByRows, SearchDirection:=xlNext)
colRange.Add key:=str, Item:=rng
End If
On Error GoTo 0
Set GetHeaderStatic = rng
End Function
Does caching the results make a difference? We test it by searching for three values 100000 times and writing the time elapsed between the two approaches:
Public Sub TestCache()
Dim sStart As Single, sTimeFind As Single, sTimeCache As Single
Dim rng As Range, wks As Worksheet
Dim rngHeader As Range
Const iCOUNT As Long = 100000
Dim i As Long, j As Integer
Dim aKey(3) As String
aKey(0) = "x"
aKey(1) = "y"
aKey(2) = "z"
'Find implementation
sStart = Timer
For i = 1 To iCOUNT Step 1
For j = 0 To 2 Step 1
Set rng = GetHeaderFind(aKey(j))
Next j
Next i
sTimeFind = Timer - sStart
'Cache implementation
sStart = Timer
For i = 1 To iCOUNT Step 1
For j = 0 To 2 Step 1
Set rng = GetHeaderStatic(aKey(j))
Next j
Next i
sTimeCache = Timer - sStart
Debug.Print "Find, Cache times = " & sTimeFind & "," & sTimeCache
Debug.Print "Ratio = " & sTimeFind / sTimeCache
End 'Dispose of static variables
End Sub
The results below show that the cache is about 29 times faster than the simple implementation.
Find, Cache times = 16.92969,0.5859375 Ratio = 28.89333 Find, Cache times = 16.85156,0.578125 Ratio = 29.14865 Find, Cache times = 16.84375,0.578125 Ratio = 29.13514
Of course, we could have simply declared and initialised a module-level Collection, and saved ourselves the effort of writing a cache. Below are some reasons why creating a cache in a function is useful:
- We are refactoring an existing function and do not want to (or cannot) change the function's interface.
- Unlike a VBA module-level variable which can be inadvertently changed by any function, we ensure that the Collection can only be changed in a one location because only the function can modify its static variable.
