Simple cache in Excel using VBA static variables

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.

References

• Visual Basic for Applications Reference, Static Statement