How do I reference array items in a dictionary?

Question

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)

You are going to need to show how you loaded the array and the dictionary — Scott Craner
– Scott Craner, Commented Dec 12, 2019 at 22:34
If you could cut down your code to only essential parts that might help your question. — Tim Williams
– Tim Williams, Commented Dec 12, 2019 at 22:53
I'm trying to show how the Dictionaries are initialized and passed to the function where the error is. — Aaron Parisi
– Aaron Parisi, Commented Dec 12, 2019 at 22:54
I see that but you only need to show a single example dictionary. Large code sections can put people off, as it's more difficult to see what's going on. — Tim Williams
– Tim Williams, Commented Dec 12, 2019 at 23:14

Tim Williams · Accepted Answer · 2019-12-13 01:09:45Z

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

Thanks! I was looking for something like this to help overcome a planning problem with a peice I was designing. Clean, simple, easy. I like it.
Is there a way to access the array items without looping? Just pick one that I want using the index?

Collectives™ on Stack Overflow

How do I reference array items in a dictionary?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related