I am working in Excel VBA (Excel 2013, x64) and trying to make a dictionary with arrays as keys. I currently have the following code, which I want to output "a,b" when called:
Sub DI()
Dim D As Object
Dim x As Variant
x = Array("a", "b")
'commenting out the line above and uncommenting the line below results in the output "a,b"
'x = "c"
Set D = CreateObject("scripting.dictionary")
D.Add x, "a,b"
Debug.Print D(x)
End Sub
Is there anything I can do to make this work?
Thanks!
An array cannot be used as a key of a dictionary, because the key should be a String type. Anyway, the array could be casted to string with the Join(array, deliminator) and used the casted string can be used as a key:
Sub TestMe()
Dim myDictionary As Object
Dim x As Variant, y As Variant
x = Join(Array("a", "b"), ",")
y = Join(Array("c", "d"), ",")
Set myDictionary = CreateObject("scripting.dictionary")
myDictionary.Add x, "a,b"
myDictionary.Add y, "c"
Debug.Print myDictionary(x)
Debug.Print myDictionary(y)
End Sub
Try the next way, please. The key must be a string, but the item can be an array:
Sub DI()
Dim D As Object
Dim x As Variant
x = Array("a", "b")
Set D = CreateObject("scripting.dictionary")
'The first array element can be a key:
D.Add x(0), x
Debug.Print D(x(0))(0), D(x(0))(1)
String. No options there.