Results 1 to 5 of 5

Problem retrieving item from Excel VBA Dictionary

This is a discussion on Problem retrieving item from Excel VBA Dictionary within the Excel Questions forums, part of the Question Forums category; Hi, Forgive me if this is a simple question. I am a novice and have been battling with a dictionary ...

  1. #1
    New Member
    Join Date
    Jan 2011
    Posts
    2

    Question Problem retrieving item from Excel VBA Dictionary

    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 by Thermal; Jan 17th, 2011 at 09:02 PM. Reason: Additional information.

  2. #2
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    14,950

    Default Re: Problem retrieving item from Excel VBA Dictionary

    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 by shg; Jan 17th, 2011 at 09:25 PM.

  3. #3
    ZVI
    ZVI is offline
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    2,766

    Default Re: Problem retrieving item from Excel VBA Dictionary

    Dict01(Iterate) = "" adds the Iterate cell object not its value
    For solving replace it by: Dict01(Iterate.Value) = ""
    Vladimir Zakharov
    Microsoft MVP Excel

  4. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,872

    Default Re: Problem retrieving item from Excel VBA Dictionary

    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 by pgc01; Jan 17th, 2011 at 10:35 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    New Member
    Join Date
    Jan 2011
    Posts
    2

    Default Re: Problem retrieving item from Excel VBA Dictionary

    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.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com