I need to create a bunch of dictionaries in VBA; the best solution is to concatenate prefix "dict" with a string variable (enumeration would cause more complexities). Is there a way to convert a string into a VBA statement?
For example, I have a dictionary dictABC created. How to refer to it by using two segment of strings, "dict" and "ABC" concatenated?
(The only way I could think of is to create a "meta-dictionary", with pairs of string "dictABC" and dictionary dictABC.)
If you build an array of dictionary objects and use another single dictionary to act as the index of all of the dictionaries you've created, you should get something akin to what you have described. Consider the following data that has three unique values in Col A.
Col A Col B Col C Col D
Y 196 RNT 4-Jan-2015
Y 127 IYI 12-Feb-2015
X 173 ZVM 24-Jan-2015
Z 124 LRP 16-Jan-2015
Z 176 XTN 27-Jan-2015
Y 137 SUG 30-Jan-2015
X 139 IBG 7-Feb-2015
X 165 DON 11-Feb-2015
Z 153 EUU 16-Feb-2015
After adding Microsoft Scripting Runtime to the VBE's Tools ► References we can run down column A, adding a key entry and index number to the index of dictionaries then redimming the array of dictionaries for room and populating that new dictionary object. If a value in column A already exists, the dNDX is used to figure out which dictionary object in the array should be referenced and adds a new key/item.
Sub mcr_Dict_Over_Dicts()
Dim rw As Long, d As Long, sCOLa As String, ky As Variant
Dim dNDX As New Scripting.Dictionary, dDICTs() As New Scripting.Dictionary
dNDX.CompareMode = TextCompare 'or BinaryCompare
With ActiveSheet
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
sCOLa = .Cells(rw, 1).Value
If CBool(Len(sCOLa)) Then
If Not dNDX.Exists(sCOLa) Then
'create a new entry in dNDX and a new dictionary in the array
d = dNDX.Count + 1
dNDX.Add Key:=sCOLa, Item:=d
ReDim Preserve dDICTs(1 To d)
dDICTs(d).Add Key:=.Cells(rw, 2).Text, _
Item:=Join(Array(.Cells(rw, 1).Text, .Cells(rw, 2).Text, .Cells(rw, 3).Text, .Cells(rw, 4).Text), ChrW(8203))
Else
'add an entry to an existing dictionary
dDICTs(dNDX.Item(sCOLa)).Add Key:=.Cells(rw, 2).Text, _
Item:=Join(Array(.Cells(rw, 1).Text, .Cells(rw, 2).Text, .Cells(rw, 3).Text, .Cells(rw, 4).Text), ChrW(8203))
End If
End If
Next rw
'return the values to the worksheet reordered in an alternate location
.Cells(1, 6).Resize(1, 4) = .Cells(1, 1).Resize(1, 4).Value
For d = LBound(dDICTs) To UBound(dDICTs)
For Each ky In dDICTs(d)
.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Resize(1, 4) _
= Split(dDICTs(d).Item(ky), ChrW(8203))
Next ky
Next d
End With
For d = LBound(dDICTs) To UBound(dDICTs)
dDICTs(d).RemoveAll
Next d
'alternately redim dDICTs
ReDim dDICTs(1)
dNDX.RemoveAll
Set dNDX = Nothing
End Sub
This does demand that there will be some value or combination of values (hash) that can be used as a unique key within each of the dictionaries in the array.
Note that the results in columns G and I are text based. This is a result of them being split out of a concatenated string.
I'm confused why you've thought of and for some reason rejected the simple, obvious solution.
But if you really want to go the code generation route, this is how you would do it.
Function dictAbcd() As Object
Static result As Object
If result Is Nothing Then
Set result = CreateObject("Scripting.Dictionary")
End If
Set dictAbcd = result
End Function
Public Sub Test()
Dim x As Object
Dim dict_index As String
dictAbcd("key") = "value"
dict_index = "Abcd"
Set x = Application.Run("dict" & dict_index)
Debug.Print x("key") ' Prints "value"
End Sub
N.b. doing it this way is completely ridiculous.
You could put the dictionaries into a collection, which allows you to have a string index.
Application.Run()dictABC,dictDEF, ... ,dictXYZas keys, and created sub dictionaries as values.