CODEWITHSUNDEEP
vbadictionary
lakshmen
lakshmen

Reputation: 29064

How to retrieve the value in a Dictionary based on key in VBA

I have a dictionary as such:

Set Desk = CreateObject("Scripting.Dictionary")
For Index = 1 To NoOfDesks
   Desk.Add Cells(15 + Index, 4).Value, Index
Next

I am interested in getting the value based on the index. I tried doing this:

MsgBox Desk.Items()(1)

But I am not able to get the Value. It is returning a integer. It should be a string. Need some guidance on this.

Upvotes: 1

Views: 28750

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

You're getting exactly what you asked for: when you added the items, you specified Cells(15 + Index, 4) for a Key, and Index for an Item. Index being an Integer, you're getting an Integer.

If possible, add a reference to Microsoft Scripting Runtime instead of late-binding with CreateObject: you'll get IntelliSense, which makes it much easier to work with an unfamiliar API:

Add(Key,Item) tooltip

Your code would look like this:

Set Desk = New Dictionary
For Index = 1 To NoOfDesks
   Desk.Add Index, Cells(15 + Index, 4).Value
Next

One thing to note, is that dictionary keys must be unique - you're [un]lucky to not have duplicates in column 4, otherwise it would be quite apparent that you have inverted the dictionary's key and value.

Upvotes: 2

Vasily
Vasily

Reputation: 5782

try this:

Sub test()
    Dim Desk As Object, NoOfDesks&, Index&, Key As Variant
    Set Desk = CreateObject("Scripting.Dictionary")

    NoOfDesks = 100

    For Index = 1 To NoOfDesks
       Desk.Add Cells(15 + Index, 4).Value, Index
    Next
    For Each Key In Desk
        Debug.Print Key, Desk(Key)
    Next
End Sub

Upvotes: 1

Related Questions