CODEWITHSUNDEEP
vbaexceldictionary
Vincent Tang
Vincent Tang

Reputation: 4170

Excel VBA Dictionary Storing and Retrieving

How do you go about creating excel VBA dictionaries?

Say I have the following values:

enter image description here

How can I set column A as the key, column B as the value?

Do I loop through every value to store?

How do I go about using the dictionary afterward to get the value of 5 for instance ("Key A")

Upvotes: 1

Views: 2904

Answers (2)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11191

In Excel:

=VLOOKUP("D", A:B, 2,FALSE)

returns 20. In VBA:

MsgBox WorksheetFunction.VLookup("D", Sheet1.Range("A:B"), 2, False)

pops 20.

Upvotes: 1

Vincent Tang
Vincent Tang

Reputation: 4170

putting answer here for documentation purposes, from reddit user MRMCMLXXXV

source https://www.reddit.com/r/excel/comments/6u4swi/how_do_you_create_a_dictionary_in_excel_vba_and/

Public Sub DictionaryExamples()

    Dim exampleValues As Variant
    Dim i As Long
    Dim aKey As String
    Dim aValue As Integer
    Dim exampleDict As Object

    'Load values into a variant array
    exampleValues = Range("A1:B10").Value

    'Instantiate a dictionary
    Set exampleDict = CreateObject("scripting.dictionary")

    'Read all keys and values, and add them to the dictionary

    For i = 1 To UBound(exampleValues)
        aKey = CStr(exampleValues(i, 1))
        aValue = CInt(exampleValues(i, 2))
        exampleDict.Add aKey, aValue
    Next i

    'Output the value associated with key A

    MsgBox exampleDict.Item("A")

End Sub

The result looks like this in excel

enter image description here

Upvotes: 0

Related Questions