CODEWITHSUNDEEP
vbaexcel
simpleMan
simpleMan

Reputation: 65

Can't call VBA Dictionary item with string key

I'm trying to create a mapping for data cleaning using vba dictionary. I store a range of values of country codes like FR, BE, NL as keys and their offset values as items: France, Belgium, Netherlands... When I run a test and try to retrieve the values using the strings as key, it throws run time error 451 'did not return object' Can anyone tell me what could be the problem?

Sub getthisdone()

'Dim dict As scripting.dictionary
Dim ws As Worksheet
Dim lastRow As Long
Dim key As String, dictItem As String
Dim i As Long

Set ws = ThisWorkbook.Worksheets("Country mapping")
Set dict = CreateObject("Scripting.Dictionary")

lastRow = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = 2 To 8 'lastRow

key = ws.Cells(i, 1).Text
dictItem = ws.Cells(i, 2).Text

    With dict

        .Add key, dictItem

    End With

Next i

MsgBox dict.items("FR")   '<---- Error happens here, why?



End Sub

Upvotes: 0

Views: 2199

Answers (1)

Alex K.
Alex K.

Reputation: 175796

.Item not .Items

MsgBox dict.Item("FR")

If you click Project → References, tick "Microsoft Scripting Runtime" and change the CreateObject line to:

Dim dict As Scripting.Dictionary: Set dict = New Scripting.Dictionary

You will get early binding auto-completion and avoid typos like this.

Upvotes: 3

Related Questions