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 ..

Are Ref No, Amount, Price, and Year seperate columns (like A:D)? What data are you trying to get back? This might be a situation where you can use VLookup (for example, if you have a reference number and you want to look it up in your data sheet and return the amount). — Gaijinhunter, Jul 27 '11 at 00:12
For dictionary structure itself in VBA, see [this](http://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure) SO Article. — Scott Conover, Aug 30 '12 at 16:53

score 15 · Answer 1 · answered Jul 27 '11 at 13:52

15

@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.

answered Jul 27 '11 at 13:52

Adam Clason

321
1
9

Thans a lot managed to work with Collection - works perfectly fine and getting expeted results . – Sky Cobb Jul 28 '11 at 09:28

score 13 · Accepted Answer · answered Jul 26 '11 at 17:55

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

Hi There, thanks a lot for quick response - yes, I am refering to Dictionary object in VBA - can't I achive your example by using Dict object using Excel / VBA. — Sky Cobb, Jul 27 '11 at 09:18

score 1 · Answer 3 · answered Mar 07 '19 at 20:22

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

Using Dictionary Object in Excel VBA

3 Answers3

Linked