Setting a global Scripting.Dictionary in Visual Basic for Applications (VBA)

Question

I have a function that is appending a dictionary. I want to keep the contents of this dictionary as long as the updateList isn't true. I currently have it setup like this:

Public Function runPolluxFull(voerUit As Boolean, updateList As Boolean)

Dim dicTitle As Variable
Dim dicFound As Variable

If updateList = True Then
Set dicTitle = CreateObject("Scripting.Dictionary")
Set dicFound = CreateObject("Scripting.Dictionary")

While status
        Set ObjectsJSON = jsonlibPollux.parse(responseString)

        With dicTitle
        .Add inc, ObjectsJSON.Item("title")
        End With

        With dicFound
        .Add inc, ObjectsJSON.Item("description")
        End With

Wend
End If

And when voerUit is true the following happens:

For i = 1 To dicTitle.Count
klaar = findReplace(dicTitle.Item(i), "x" + dicTitle.Item(i), dicTitle.Item(i) + vbCrLf + vbCrLf + dicFound.Item(i))
Next i

The problem here is that when this function ends, dicTitle and dicFound are cleared, and the findReplace function gets fed empty arguments.

Is there anyway to makes to code work, or a good workaround?

Answer 1

You will need to add a reference to the Microsoft Scripting Runtime to use binding like I have used. It is preferable to the manner you are declaring them as you have better access to the object and can see member variables easier, etc.

Method 1 - module level or public variables for dictionaries

One way you can do this is to create module level variables for the scripting dictionaries you are using:

Public gDicTitle As Scripting.Dictionary
Public gDicFound As Scripting.Dictionary

Public Function runPolluxFull(voerUit As Boolean, updateList As Boolean)


    If updateList = True Then
        Set gDicTitle = New Scripting.Dictionary
        Set gDicFound = New Scripting.Dictionary
        
        While Status
                Set ObjectsJSON = jsonlibPollux.Parse(responseString)
        
                With gDicTitle
                .Add inc, ObjectsJSON.Item("title")
                End With
        
                With gDicFound
                .Add inc, ObjectsJSON.Item("description")
                End With
    
        Wend
    End If
End Function

Method 2 - static references to dictionaries

You can also do this by making the dictionaries static. This will preserve them between function calls like the documentation says.

Public Function runPolluxFull(voerUit As Boolean, updateList As Boolean)
    Static gDicTitle As Scripting.Dictionary
    Static gDicFound As Scripting.Dictionary
    
    If updateList = True Then
        Set gDicTitle = New Scripting.Dictionary
        Set gDicFound = New Scripting.Dictionary
        
        While Status
                Set ObjectsJSON = jsonlibPollux.Parse(responseString)
        
                With gDicTitle
                .Add inc, ObjectsJSON.Item("title")
                End With
        
                With gDicFound
                .Add inc, ObjectsJSON.Item("description")
                End With
    
        Wend
    End If
End Function