VBA: Dictionary items to string array?

Question

What I am trying to do is fairly straightforward. I want to get a list of all items (values) in a Dictionary, and save them in an array of strings.

I'd guess this code would work:

Sub PrintFilters(ByVal crit As Dictionary)
    Dim i() As String
    i = crit.Items()
    ' Do stuff with i
End Sub

However, I am getting a type mismatch on the third line. I am guessing that crit.Items()'s return value is some kind of list, an not an array. The MSDN pages do not mention what this method's return value's type is, though.

Is there a proper way to do this?

Solution 1

I think is Variant type so try this:

Sub PrintFilters(ByVal crit As Dictionary)
    Dim i As Variant
    i = crit.Items()
    ' Do stuff with i
End Sub

Solution 2

If you need a string array you need to build one manually as .Items is a variant()

Sub PrintFilters(crit As Dictionary)
    Dim key As Variant, i As Long

    ReDim items(crit.Count - 1) As String

    For Each key In crit.Keys()
        items(i) = crit(key)
        i = i + 1
    Next

    Debug.Print Join(items, ", ")
End Sub

Solution 3

Please find below 2 options achieving the same result either by handling the conversion directly as part of your main routine or by passing the dictionary to a function returning a String Array; a check in the VBA Locals Window indicates that arrString is a variable of type "String(0 to 2)". Note that the array can be built with both the dictionary .Keys or .Items.

In the first example, the Join function creates a string including all aDict.Items separated by the "|" character. Then the Split function breaks down that string into an array of strings where "|" acts as the delimiter used to create each array element.

In both examples the actual conversion from Dictionary to String Array is achieved with 1 line of code.

OPTION #1 - As part of the main routine

Sub Dictionary_to_StringArray()

   Dim aDict As Scripting.Dictionary
   Dim arrString() As String

   Set aDict = New Scripting.Dictionary
   aDict.Add "United Kingdom", "London"
   aDict.Add "France", "Paris"
   aDict.Add "United States of America", "Washington, D.C."

   arrString = Split(Join(aDict.Items, "|"), "|") 'this works equally with .Items & .Keys

   Set aDict = Nothing
   Erase arrString

End Sub

OPTION #2 - Conversion handled by dedicated Function

Sub CallingProc()

   Dim aDict As Scripting.Dictionary
   Dim arrString() As String

   Set aDict = New Scripting.Dictionary
   aDict.Add "United Kingdom", "London"
   aDict.Add "France", "Paris"
   aDict.Add "United States of America", "Washington, D.C."

   arrString = Make_StringArray_From_Dictionary(aDict)

   Set aDict = Nothing
   Erase arrString

End Sub

Function Make_StringArray_From_Dictionary(ByVal SubmitDict As Scripting.Dictionary) As String()

   Make_StringArray_From_Dictionary = Split(Join(SubmitDict.Items, "|"), "|")

End Function

Comments

[Admin]

I guess the OP wasn't really looking for a String but a Variant array :P

[IT goldman]

this breaks if your string "|" is contained in either of the keys or items.