Access Dictionary in Different Subs in VBA

Question

I want to store some status in VBA (Excel) using a dictionary but got problems with accesing it in a different sub.

in the end, I want a nested Dictionary containing ID's as primary keys and for each ID I want multiple status-messages with the matching timestamp:

Example: {ID_1: {status_1: 09.10.19 06:47, status_2: 09.10.19 07:00,...}, ID_2:{status_6: 09.10.19 06:30, status_1: 09.10.19 06:20}}

So far I was just trying to get a simple dictionary only containing the ID and the status:

Option Explicit  Public dict As Scripting.Dictionary  Public id As Integer


Public Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Fin

    Dim current_status As String

    current_status = Tabelle4.Range("B11").Value
    id = Tabelle3.Cells(7, 6).Value

    Application.EnableEvents = False
    If Not Intersect(Target, Range("C12:G12")) Is Nothing Then


        If dict Is Nothing Then
            Set dict = New Scripting.Dictionary

            dict.Add (id), current_status

        'Checks if inner dict exists
        ElseIf Not dict(id).Exists Then

            dict.Add (id), current_status

        End If

    End If

    Fin:

         Application.EnableEvents = True
    End Sub

Please note that I change the ID manually and current_status will be generated in B11 by changing one of the values in C12 to G12 (that is why I used Change event). In the End I want to keep a record of all the status updates that occur and track when they occur.

My Problem is that this does work for 1 Entry. I am able to create one entry in the "If dict is Nothing Then"-part and can access it even after the event. (e.g. inserting MsgBox dict(ID) at the start before triggering the event again.) I also want to print the result using a Button on my Excel. However, when I call:

Public Sub print_dict()    

MsgBox dict(id)

End Sub

I get an error message that dict is not defined. "MsgBox id" yields an empty MsgBox.

Maybe someone can help me out. I am pretty new to VBA and am still struggling with it :D

Cheers!

1) Are you sure `print_dict()` gets called after the `Worksheet_Change` event has been triggered at least once? 2) See [What is the lifetime of a global variable in excel vba?](https://stackoverflow.com/q/7041138/4934172). — 41686d6564 stands w. Palestine, Oct 09 '19 at 05:17
yes pretty sure. I did the event in debug mode 2 times and added "MsgBox dict(id)" at the first line before the second run and it yield the correct "current_status" and id when running the event again. However, it breaks when changing the id or when trying to acces the dict in the different sub. — mayool, Oct 09 '19 at 05:23

Tim Williams · Accepted Answer · 2019-10-09T05:38:32.913

dict(id).Exists

should be

dict.Exists(id)

When using an error handler it's worth making sure you're not just skipping over problems with your code syntax.

I'm guessing MsgBox dict(id) fails because that sub is in a regular module and not in your sheet module.

MsgBox sheetCodename.id

should work though.

MsgBox id gives you an empty messagebox because you're not using Option Explicit in your regular code module: the variable id doesn't exist in a scope which can be accessed by your Sub , but without Option Explicit your code auto-creates a (blank) id variable for you.

Access Dictionary in Different Subs in VBA

1 Answers1