CODEWITHSUNDEEP
vbaexceldictionary
sohil
sohil

Reputation: 415

VBA how to use a dictionary

I am getting issues in using a dictionary in VBA. I want to add values from a sheet to a dictionary. If I use simple lists, there is no error in the code. Like this.

Function Account(Place As String) As String

Dim cities(500)
Dim accounts(500)

For i = 2 To 500
    cities(i) = Worksheets("Sheet2").Cells(i, 2).Value
    accounts(i) = Worksheets("Sheet2").Cells(i, 3).Value
Next i

placeName = StrConv(Place, vbProperCase)
Account = placeName

End Function

This code does not give an issue but if I add the code for the dictionary, there is some issue. 

Function Account(Place As String) As String

Dim cities(500)
Dim accounts(500)
Dim dict
Set dict = CreateObject(Scripting.Dictionary)

For i = 2 To 500
    cities(i) = Worksheets("Sheet2").Cells(i, 2).Value
    accounts(i) = Worksheets("Sheet2").Cells(i, 3).Value
    dict(cities(i)) = accounts(i)
Next i

placeName = StrConv(Place, vbProperCase)
Account = placeName
dict = Nothing

End Function

Can someone point out the error. I am new to vba so I dont know much about it.

Upvotes: 1

Views: 1456

Answers (2)

Rich Holton
Rich Holton

Reputation: 682

One possible area of concern, brought to mind by one of your comments, lies in the use of "Sheet1" and "Sheet2". In Excel VBA, there are two different ways to refer to a worksheet. The is the Name of the worksheet, which is what the user sees on the tabs in Excel, and the user can change at will. Thtese default to names like "Sheet1", "Sheet2", etc.

There is also the "Codename" for each worksheet. In the Visual Basic Editor, the project explorer window will list all the worksheets under "Microsoft Excel Objects". There you'll see the Codename for each worksheet, with the Name of the worksheet in parentheses.

When you use Worksheets("Sheet1"), the "Sheet1" refers to the Name, not the Codename. It's possible to end up with a worksheet with the Name "Sheet1" and the codename "Sheet2".

As far as your functions are concerned, I note that in both cases you declare local variables -- the arrays 'cities' and 'accounts' in the first, and those two plus the dictionary 'dict' in the second. You have code to fill those local variables, but then do nothing with them. The return value of the function is not dependent on any of those local variables.

Once the function code completes, those local variables lose their values. VBA returns the memory it used to store those variables to its pool of available memory, to be reused for other purposes.

Try commenting-out the entire for...next loop, and you'll see that the value return from the function is unchanged.

I'm not certain what you intend to accomplish in these functions. It would be helpful for you to explain that.

Upvotes: 0

user4039065
user4039065

Reputation:

The folowing UDF loads a dictionary object with places as keys (unique) and associated accounts as items. After the dictionary has been loaded, it looks up the Place parameter passed into the function and returns the account if found.

Option Explicit

Function Account(Place As String) As String
    Static d As Long, dict As Object

    If dict Is Nothing Then
        Set dict = CreateObject("Scripting.Dictionary")
        dict.comparemode = vbTextCompare
    Else
        dict.RemoveAll
    End If

    With Worksheets("Sheet2")
        For d = 2 To .Cells(.Rows.Count, "B").End(xlUp).Row
            dict.Item(.Cells(d, "B").Value2) = .Cells(d, "C").Value2
        Next d
    End With

    If dict.exists(Place) Then
        Account = dict.Item(Place)
    Else
        Account = "not found"
    End If

End Function

Note that beyond other corrections, the code to instantiate the dictionary object is CreateObject("Scripting.Dictionary") not CreateObject(Scripting.Dictionary).

Upvotes: 3

Related Questions