CODEWITHSUNDEEP
excelvba
Aaron Parisi
Aaron Parisi

Reputation: 706

How do I reference array items in a dictionary?

I am trying to determine if any one of the strings inside any one of the array items in a VBA dictionary equal a string of 4 spaces.

    If _
    Not CStr(info.Items(1, 4)) = "    " Or _
    Not CStr(info.Items(1, 5)) = "    " Or _
    Not CStr(info.Items(1, 6)) = "    " Or _
    Not CStr(info.Items(2, 4)) = "    " Or _
    Not CStr(info.Items(2, 5)) = "    " Or _
    Not CStr(info.Items(2, 6)) = "    " Or _
    Not CStr(info.Items(3, 4)) = "    " Or _
    Not CStr(info.Items(3, 5)) = "    " Or _
    Not CStr(info.Items(3, 6)) = "    " Or _
    Not CStr(info.Items(4, 4)) = "    " Or _
    Not CStr(info.Items(4, 5)) = "    " Or _
    Not CStr(info.Items(4, 6)) = "    " Then

I keep getting a Subscript out of range error. I've tried

...info.Items(1)(4)... as well with the same error.

I know each array item has 6 elements in it, and I know there are 4 keys in the dictionary. How do I access elements of each key's item if the item is an array?

        Dim RQItems As Dictionary
        Dim RPItems As Dictionary
        Dim IMPItems As Dictionary
        Dim EMItems As Dictionary
        Dim BOOTItems As Dictionary

        Dim RQ1(6) As String
        Dim RQ2(6) As String
        Dim RQ3(6) As String
        Dim RQ4(6) As String




        Set RQItems = New Dictionary

        RQ1(1) = "PSA "
        RQ1(2) = "Prlm"
        RQ1(3) = "Info"
        RQ1(4) = "    "
        RQ1(5) = "    "
        RQ1(6) = "    "

        RQ2(1) = "Mary"
        RQ2(2) = "Clnt"
        RQ2(3) = "Escr"
        RQ2(4) = "Bank"
        RQ2(5) = " SS "
        RQ2(6) = "    "

        RQ3(1) = "Inst"
        RQ3(2) = "Wire"
        RQ3(3) = "    "
        RQ3(4) = "    "
        RQ3(5) = "    "
        RQ3(6) = "    "

        RQ4(1) = "Acct"
        RQ4(2) = "Fee "
        RQ4(3) = "    "
        RQ4(4) = "    "
        RQ4(5) = "    "
        RQ4(6) = "    "

        RQItems("OPEN") = RQ1
        RQItems("DOCS") = RQ2
        RQItems("$$$$") = RQ3
        RQItems("FILE") = RQ4

I pass these into a function like myFn(info As Dictionary)

Upvotes: 0

Views: 411

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

You can access arrays stored in a dictionary like this:

Sub Test()

    Dim dict As New Dictionary, arr(1 To 4), k, arr2, v

    arr(1) = "One"
    arr(2) = "Two"
    arr(3) = "Three"
    arr(4) = "four"

    dict.Add "Test", arr 

    'access a single item
    Debug.Print dict("Test")(1) '>> One


    'loop over all contained arrays 
    For Each k In dict.Keys
        arr2 = dict(k)

        Debug.Print arr2(3) 'access a single element

        'or loop through all elements in the array
        For Each v In arr2
            Debug.Print k, v
        Next v
    Next k

End Sub

Upvotes: 1

Related Questions