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.