Using Dictionary Object in Excel VBA

Question

I am struggling to make my Dictionary object work to return economic details of selected Reference number.

e.g. I have below reference nos and corresponding values, however, not sure if I can achieve this using Dictionary object , and alternative, suggestion would be highly appreciated.

Ref No  Amount Price   Year
IB1232  1000   1.23    2011
IB1231  1000   3.23    2011
IB1233  1000   3.43    2011
IB1234  1000   3.43    2011

I thought would be able to achieve by forming Key and Value for reference and their corresponding details, but not been able to achieve ..

Answer 1

@das_weezul

There is a Dictionary object in VBA in the scripting library (you need to add that reference to use it). The Dictionary has some extra functionality, such as the ability to check if a key exists before trying to access it.

@Sky Cobb

Yes, you could do all of the above tasks with the Dictionary. The syntax will the same, except you should provide a key for every item that you add to it.

Answer 2

As mentioned before, you need to enable a reference to get the Dictionary object, but it absolutely does exist. To add the reference: Tools > References > [x] Microsoft Scripting Runtime

Public Sub test_dict()
    Dim td As Object
    Set td = New Dictionary

    td("IB1232") = "1000   1.23    2011"
    td("IB1233") = "1000   3.43    2011"

    'Another way to do it, may be around for legacy support
    td.Item("IB1234") = "1000   3.43    2011"

    'What you probably want... a key:value dictionary where the value is a collection
    Set td("IB1231") = New Collection
    td("IB1231").add 1000
    td("IB1231").add 3.23
    td("IB1231").add 2011


    'Get value by key
    Debug.Print td("IB1234")

    'Get a collection's value.... it's 1-indexed
    Debug.Print td("IB1231")(1)

    'Test if a key exists
    Debug.Print td.exists("IB12345")

    'See how many items there are
    Debug.Print td.Count()

End Sub

Answer 3

I don't know what you're referring to as Dictionary in VBA, as the data structure with the said functionality is called Collection in VBA (but maybe you coded your own Ditionary, in that case we need the code in order to be able to help you).

If I get your example right, you want to access e.g {1000,1.23,2011} via the key "IB1232". You can do this easily by creating a Collection of Collections like this:

Dim coll as new Collection
Dim data as new Collection

data.Add 1000
data.Add 1.23
data.Add 2011

coll.Add data, "IB1232"

To access your data just get the desired record (Collection) via the key

Debug.Print coll.Item("IB1232")(1) 'Prints 1000
Debug.Print coll.Item("IB1232")(2) 'Prints 1.23
Debug.Print coll.Item("IB1232")(3) 'Prints 2010

You can also use an array of Variants for the data