VBA Excel Key-Value Store Cache mit Dictionary

Um die Performance von langlaufenden Berechnungen mit MS Excel zu erhöhen, habe ich für eine immer wiederkehrende Berechnung einen rudimentären Key-Value Cache eingebaut mit Hilfe eines Dictionary Objektes.

Eine Dictionary Objekt in VBA ist ein Object aus der Microsoft Scripting Library, die dafür in das Projekt eingebunden muss über:

1. Visual Basic Editor in Excel öffnen
2. Extras -> Verweise
3. Microsoft Scripting Library Kreutzchen setzen

Ein Dictionary Object ist wie ein assoziatives Array, was für VBA schon das höchste der Gefühle ist 🙂

Folgender Code cached das Ergebnis eines Funktionsaufrufes in einer öffentliche (public, globaler Scope) Variablen namens „cache“:

'this code does not run, it is pseudo code understand the cache behaviour

'public variable, global scope
Dim cache  As Scripting.Dictionary

sub mySub()
    'initialize cache
    Set cache = = New Scripting.Dictionary 
    For Each myRow In rows
        resultFromCache = myFunction(1 , 2, 3)
    Next myRow
end Sub

Function myFunction(input1, input2, input3)
    'chache this result
    cacheKey = input1 & " " & input2 & " " & input3
    If Not cache.Exists(cacheKey) Then
        result = cacheAbleFunction(input1, input2, input3)
        cache.Add cacheKey, result
    Else
        result = cache.Item(cacheKey)
    End If
    myFunction = result
end Function

Function cacheAbleFunction(input1, input2, input3)
    'do something here very slow or very often
    cacheAbleFunction = input1 * input2 * input3 * input1 * input2 * input3 * input1 * input2 * input3
end Function

Weitere Tipps zur Performance-Optimierung von Excel Makros habe ich in dem Artikel Excel Markos Speed Up Guide zusammengefasst.