VBA collection not updating?-CodePudding

I have a global dictionary actionDict that has a structure that looks like this:

id: [collection1 collection2 collection3 collection4 collection5]

each collection has 5 strings (either user informations or an empty string)

When the user clicks a button, the script will update the dictionary

' inside the onclick function
    ElseIf actionDict(constituentID.Value)(2)(2) = "" Then
        actionDict(constituentID.Value)(2)(2) = action_date.Value 'value = test1
        actionDict(constituentID.Value)(2)(5) = agent_tm.Value 'value = test1
        actionDict(constituentID.Value)(2)(4) = action_response.Value 'value = test1
        actionDict(constituentID.Value)(2)(3) = "Call Center"
        ' this is displayed correctly
        MsgBox "second action empty"
        ' this displays the correct information
        MsgBox action_date.Value & " " & agent_tm.Value & " " & action_response.Value
        ' this displays an empty string
        MsgBox actionDict(constituentID.Value)(2)(2) & " " & actionDict(constituentID.Value)(2)(5) & " " & actionDict(constituentID.Value)(2)(4)

The first 2 message boxes are displaying the right information when I click the button is the right information, however the third one is displaying a blank string.

Also when I access the dictionary again, the newly added values are still displayed as empty

' inside calling function
Sub onclick()
    action = actionDict(id)(2)
    'tmagent, actiondate, actionresponse, pledge_amnt are text fields
    populatefieldsAction(tmagent, actiondate, actionresponse, pledge_amnt, action)
End Sub

Function populatefieldsAction(tmagent, actiondate, actionresponse, pledge_amnt, action)
    MsgBox action(5) & "  " & action(2) & action(4)
    'showing empty string
    tmagent.Value = action(5)
    actiondate.Value = action(2)
    actionresponse.Value = action(4)
End Function

What exactly is going wrong? Thanks

CodePudding user response：

Building on what freeflow said:

You cannot change the contents of an array held in a Dictionary because the value returned by the key is a copy of the array. To correctly update the dictionary you must first retrieve the array,then make your changes, then replace the old array with the updated array. – freeflow

You code will look something like this:

' inside the onclick function
ElseIf actionDict(constituentID.Value)(2)(2) = "" Then
    temp_arr = actionDict(constituentID.Value)
    
    temp_arr(2)(2) = action_date.Value 'value = test1
    temp_arr(2)(5) = agent_tm.Value 'value = test1
    temp_arr(2)(4) = action_response.Value 'value = test1
    temp_arr(2)(3) = "Call Center"
    
    actionDict(constituentID.Value) = temp_arr

' inside calling function
Sub onclick()
    'tmagent, actiondate, actionresponse, pledge_amnt are text fields
    Call populatefieldsAction(tmagent, actiondate, actionresponse, pledge_amnt, actionDict, id) 'Pass the entire dictionary
End Sub

Function populatefieldsAction(tmagent, actiondate, actionresponse, pledge_amnt, ByRef actionDict, id)
    tmagent.Value = actionDict(id)(2)(5)
    actiondate.Value = actionDict(id)(2)(2)
    actionresponse.Value = actionDict(id)(2)(4)
End Function

CodePudding user response：

I ended up creating a new array altoghether, based on what @freeflow and @Hrishikesh Nadkarni said:

    Dim new_arr
    Set new_arr = New Collection
    
    Dim new_act()
    ReDim new_act(1 To 5)
    
    new_act(1) = agent_tm.Value
    new_act(2) = action_date.Value
    new_act(3) = action_response.Value
    new_act(4) = pledge_amnt.Value

    'Set test = actionDict(constituentID.Value)
    If Not actionDict.Exists(constituentID.Value) Then
        Dim arr()
        ReDim arr(1 To 5)
        arr(1) = agent_tm.Value
        arr(4) = action_date.Value
        arr(3) = action_response.Value
        arr(2) = pledge_amnt.Value
        new_arr.Add (arr)
    Else
        For i = 1 To 5
            If actionDict(constituentID.Value)(i)(2) <> "" Then
                new_arr.Add (actionDict(constituentID.Value)(i))
            End If
        Next i
    End If
    
    new_arr.Add (new_act)
    

    Do While new_arr.Count < 5
        Dim emptyArr()
        ReDim emptyArr(1 To 5)
        emptyArr(1) = ""
        emptyArr(2) = ""
        emptyArr(3) = ""
        emptyArr(4) = ""
        emptyArr(5) = ""
        new_arr.Add emptyArr 'add empty array
    Loop
    
    Set actionDict(constituentID.Value) = new_arr