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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

9

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 13, 2014 at 15:24

enderland

14.3k18 gold badges105 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Martijn Nosyncerror Over a year ago

Whne I set dicTitle with Scripting.Dictionary (static, or without), I get a compiling error saying that the type is not defined (sorry have a Dutch install, so I have to translate error message's).

enderland Over a year ago

@MartijnNosyncerror - You have to add a reference to that library in your VBA editor, add what in English is the Microsoft Scripting Runtime library. I added that to the top of my answer.

Martijn Nosyncerror Over a year ago

That did the trick for that error, problem is that my dictionary is still empty when using it in the findReplace function. I checked, and both dictionaries get populated with the first snippit, but still end up being deleted when that While loop ends..

enderland Over a year ago

@MartijnNosyncerror please post the rest of your code for the function exactly as you are using it with your problem, it seems you must have more code in your runPollufFull function than what I used here.

Martijn Nosyncerror Over a year ago

Yes I have but it's a lot of junk that isn't really relevant to the problem. I will post it on pastebin because of the size, but it's mostly in Dutch (and bad programming, which is the worst part :D) pastebin.com/a47vtBv8

|

Collectives™ on Stack Overflow

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

1 Answer 1

Method 1 - module level or public variables for dictionaries

Method 2 - static references to dictionaries

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Method 1 - module level or public variables for dictionaries

Method 2 - static references to dictionaries

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related