CODEWITHSUNDEEP
vbadictionarycollections
Varun Yadav
Varun Yadav

Reputation: 15

VBA adding collection to a dictionary or multiple values for one key

I have a main big table. Of which I want column

Flag 1 if Value 1 > Value 2, Flag 2 if Value 1 = 0.45. Obviously there are above 5K+ unique records of each. I was wondering if I could store above as collection and the add it to a dictionary with name as the key and collection as item?

Thanks

Upvotes: 0

Views: 7501

Answers (1)

Vityata
Vityata

Reputation: 43585

In the collection, you may use any object as an item. Thus, you may put Array, Object or Collection there. In these, you may put multiple values. Here you go with Array as Item. Run it and check the results in the immediate window.

Option Explicit

Public Sub TestMe()

    Dim myCol   As New Collection
    Dim myVar   As Variant        

    myCol.Add Array("A", "B"), "2"
    myCol.Add Array("C", "D"), "3"
    myCol.Add Array("F", "G", "H"), "6"

    For Each myVar In myCol
        Debug.Print myVar(LBound(myVar))
        Debug.Print myVar(UBound(myVar))
        Debug.Print "-------------------"
    Next myVar

End Sub

Here is the solution with a Scripting .Dictionary:

Option Explicit

Public Sub TestMe()

    Dim myCol   As Object
    Dim myVar   As Variant

    Set myCol = CreateObject("Scripting.Dictionary")    

    myCol.Add 2, Array("A", "B")
    myCol.Add 3, Array("C", "D")
    myCol.Add 6, Array("F", "G", "H")

    For Each myVar In myCol.Keys
        Debug.Print myCol(myVar)(LBound(myCol(myVar)))
        Debug.Print myCol(myVar)(UBound(myCol(myVar)))
        Debug.Print "-------------------"
    Next myVar

End Sub

This is the result in the immediate window:

A
B
-------------------
C
D
-------------------
F
H
-------------------

Upvotes: 1

Related Questions