Retrieve nth key in dictionary VBA

Question

Is it possible to extract the nth key from a dictionary and its value in VBA? Something like

For i = 1 To Counter + diccompare.Count - UBound(RecSource)
WS.Cells(Lastrow + i, "A") = diccompare.Keys(UBound(RecSource) - Counter + i)
Next

Where I am trying to assign the Cell(Lastrow +i) the value of the key in dictionary diccompare(UBound(RecSource) - Counter + i)

No. Not in VBA, but it is impossible in any language. Keys in dictionary are not numbered. See the 2. answer here - http://stackoverflow.com/questions/1820506/c-get-first-key-from-dictionarystring-string — Vityata, Mar 24 '17 at 15:15
Ahh okay, thanks for the link. I will find a workaround somehow else then — Vlad M, Mar 24 '17 at 15:20
In general - if you want to compare keys of a dictionary this is not a good idea - the dictionary guarantees, that every key is unique. — Vityata, Mar 24 '17 at 15:24

score 6 · Accepted Answer · answered Mar 24 '17 at 15:26

6

Not sure if I fully got you, something like this

Sub KeyTest()

Dim d As New Scripting.Dictionary

d.Add "Test1", 1
d.Add "Test2", 2
d.Add "Test3", 99

Debug.Print d.Keys()(1)

End Sub

answered Mar 24 '17 at 15:26

Nathan_Sav

8,466
2
13
20

@VladM Looking at your issue, as `.Keys()` returns an array, you can skip the loop and use something along these lines `Range("a1").Resize(UBound(d.Keys()) + 1, 1).Value = (Application.Transpose(d.Keys))` – Nathan_Sav Mar 24 '17 at 15:45

score 3 · Answer 2 · answered Mar 24 '17 at 15:27

you could use this helper Function:

Function GetNthKey(dict As Dictionary, nth As Long)
    Dim arr As Variant
    With dict
        arr = .Keys
        GetNthKey = arr(nth - 1)
    End With
End Function

to be exploited in your "main" code as follows:

Dim diccompare As Dictionary 

Set diccompare = New Dictionary

With diccompare
    .Add 1, "a"
    .Add 2, "b"
    .Add 3, "c"
    .Add 4, "d"
End With

MsgBox GetNthKey(diccompare, 2) '<--| returns "2", i.e. the 2nd key

Retrieve nth key in dictionary VBA

2 Answers2