CODEWITHSUNDEEP
vbaexceldictionarykey-value
Santa
Santa

Reputation: 47

VBA : key-pair dictionary

I go with Late binding as i got error using Early binding.
Instead of showing the 3 activesheet name, the output is like this

Key: Value:

Never tried this before. Can anyone point me to the right direction?

Sub LoopKeys()

Dim key As Variant
Dim country As Variant

country = Array("Kentucky", "California", "Ohio")

dic.Add "Kentucky", "Alicia"
dic.Add "California", "Ken"
dic.Add "Ohio", "Benjamin"

For sheet = LBound(country) To UBound(country)
ThisWorkbook.Worksheets(country(sheet)).Activate

    If ActiveSheet.Name = " & key & " Then
        Debug.Print "Key: " & key & " Value: " & dic(key)
    End If

Next

End Sub

Upvotes: 0

Views: 1448

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

In a Scripting.Dictionary object you need to loop through the Keys collection:

Dim key As Variant
For Each key In dic.Keys
    Debug.Print "Key: " & key & " Value: " & dic(key)
Next key

Update 1:

Sub LoopKeys()
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    Dim s As Worksheet
    For Each s In ThisWorkbook.Sheets
        dic.Add key:=CStr(s.Index), Item:=s.Name
    Next s

    Dim key As Variant
    For Each key In dic.Keys
        Debug.Print "Key: " & key & " Value: " & dic(key)
    Next key

    Set dic = Nothing
End Sub

'Key: 1 Value: Sheet1
'Key: 2 Value: Sheet2
'Key: 3 Value: Sheet3

Update 2:

Sub LoopKeys()
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    With dic
        .Add key:="Kentucky", Item:="Alicia"
        .Add key:="California", Item:="Ken"
        .Add key:="Ohio", Item:="Benjamin"
    End With

    Dim key As Variant
    For Each key In dic.Keys
        ThisWorkbook.Worksheets(key).Activate
        Debug.Print "Key: " & key & " Value: " & dic(key)
    Next key

    Set dic = Nothing
End Sub

Upvotes: 1

Related Questions