Problem retrieving item from Excel VBA Dictionary

Thermal

New Member
Joined
Jan 17, 2011
Messages
2
Hi,

Forgive me if this is a simple question. I am a novice and have been battling with a dictionary item retrieval issue for quite a while, spent days searching the internet for an answer and have given up in favour of posting my own question. Any help will be very much appreciated.

My issue is I cannot retrieve the data indexed by a particular key. Example code:

Dim Dict01 As New Dictionary
Sub Macro1()
'
' Macro1 Macro
'
'
Dim Count As Integer
Dim CurCell As Range
Dim Keys As Variant
Dim Iterate As Variant
Dim Test As Variant
Set Dict01 = New Dictionary
'
' Row 1 is where the dictionary keys live.
' Row 2 and onward is where the data referenced by the keys is located.
'
Range("A1").Select
Set CurCell = Range(Selection, Selection.End(xlToRight))
CurCell.Select
'
' Creates dictionary keys
'
For Each Iterate In CurCell
Dict01(Iterate) = ""
Next

Keys = Dict01.Keys
Count = 0
'
' Selects data for loading into dictionary
'
Range("A2").Select

Set CurCell = Range(Selection, Selection.End(xlToRight))
CurCell.Select
For Each Iterate In CurCell
Dict01(Keys(Count)) = Iterate
Count = Count + 1
Next
Test = Keys(0)
MsgBox (Dict01.Item(Keys(0))) ' Works! returns data identified but 1st key
MsgBox Test 'Proves Test contains correct key
MsgBox (Dict01.Item(Test)) ' Does not work! displays empty content

End Sub

I do not understand why the last Msgbox statement returns 'nothing' rather than the data held by Keys(0).

For completeness, the data in the sheet would look like this:

A B C
1 Ford Audi BMW
2 Ka A5 X6

I expect the 3 Msgbox's to return

Ka - Output as expected
Ford - Output as expected
Ka - No output.

Many thanks in advance.
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
You might want to read about the properties and methods of a dictionary object in Help (Microsoft Visual Basic Documentation > Visual Basic Language Reference > Objects > Dictionary).

You add items to a dictionary using the Add method. You can't access an item in a dictionary by position except by reading all of the Keys into a variant array, which kind of defeats the purpose of a dictionary.

I think this is part of what you're trying to do:

Code:
Dim dic          As Dictionary
 
Sub Macro1()
    Dim r           As Range
    Dim cell        As Range
    Dim avKeys      As Variant
 
    Set dic = New Dictionary
    Set r = Range("A1", Range("A1").End(xlToRight))
 
    For Each cell In r
        If Not dic.exists(cell.Value) Then dic.Add Key:=cell.Value, Item:=cell.Offset(1).Value
    Next cell
    avKeys = dic.Keys
End Sub
 
Last edited:
Upvote 0
Dict01(Iterate) = "" adds the Iterate cell object not its value
For solving replace it by: Dict01(Iterate.Value) = ""
 
Upvote 0
Hi Thermal
Welcome to the board

Since you are having problems retrieving the item, here are some examples at the end of the Sub.

shg, hope you don't mind that I borrowed your code.

Code:
Sub Macro1()
    Dim r           As Range
    Dim cell        As Range
    Dim avKeys      As Variant
    Dim dic         As Dictionary
 
    Set dic = New Dictionary
    Set r = Range("A1", Range("A1").End(xlToRight))
 
    For Each cell In r
        If Not dic.Exists(cell.Value) Then dic.Add Key:=cell.Value, Item:=cell.Offset(1).Value
    Next cell
 
    ' 3 ways to display the second item
    avKeys = dic.Keys
 
    MsgBox dic.Item(avKeys(1))
    MsgBox dic(avKeys(1))
 
    MsgBox dic.Items(1)
 
    ' other ways to display the second item, using the range
    MsgBox dic.Item(r.Offset(0, 1).Resize(1, 1).Value)
    MsgBox dic(r.Offset(0, 1).Resize(1, 1).Value)
 
    MsgBox dic(r(2).Value)
 
End Sub
 
Last edited:
Upvote 0
Thank you all for your responses, really useful. Clearly I have some work to do on my understanding of how dictionaries function, I will jump to it.

Kind regards.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top