Home > Blockchain >  VBA global dictionary not populated appropriately when accessed from onclick function?
VBA global dictionary not populated appropriately when accessed from onclick function?

Time:03-15

(update from this answer)

I have a global dictionary in a module

Public donationDict As Scripting.Dictionary
Option Explicit

Sub ouvrir()
    'populate the dictionary
    userform1.show
End Sub

The structure of the dictionary looks like this (I replaced the data with str)

key1:
[
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"]
]
...
lastKey:
[
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"],
    ["str", "str", "str", "str"]
]

inside the function ouvrir, all the keys are there (I verified with dict.Exists)

However, when I use the donationDict inside the onclick fonction of a button, it seems like the only key that there is the last one

    'get the text from the search box
    Dim id As String
    id = constituentID.Value
    
    Dim constData As Worksheet
    Set constData = Sheets(2)
    
    Set FoundCell = constData.Range("A:A").Find(What:=id)
    If Not FoundCell Is Nothing Then
        Dim test As Range
        Set test = constData.Range("A" & FoundCell.row, "Y" & FoundCell.row)
        firstLastName.Value = test(2) & ", " & test(3)
        'assign a bunch of other values
    Else
        MsgBox (id & " not found")
    End If
    
    Dim donationData As Worksheet
    Set donationData = Sheets(3)
    MsgBox "" & donationDict.Exists("90338") 'this shows false when it shows true in ouvrir()
    'some if else statements

if it helps I have office version 18.2110.13110.0

CodePudding user response:

A dictionary can take a key of [almost?] any datatype, and two keys which seem the same may not be:

Sub Tester()

    Dim dict As Object, k
    Set dict = CreateObject("scripting.dictionary")

    dict.Add 999, 1
    dict.Add "999", 2  'no "duplicate key" error
    
    [A1] = 999
    'note this adds the cell as a key, not its value
    dict.Add [A1], 3   'no "duplicate key" error
    
    For Each k In dict
        Debug.Print k, TypeName(k), dict(k)
    Next k

End Sub

Output:

 999          Integer        1 
 999          String         2 
 999          Range          3 

You might consider casting all of the keys to (eg) string when you create the dictionary, and then query using strings to be sure you don't have data type problems when looking up entries in the dictionary.

  • Related