VBA如何使用字典。-VBAhowtouseadictionary

3  

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.

folowing UDF装载一个字典对象，它的位置作为键(唯一的)和关联的帐户作为项目。在加载了字典之后，它将查找传递到函数中的Place参数并返回该帐户。

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).

注意，除了其他更正之外，实例化dictionary对象的代码是CreateObject(“scriptl . dictionary”)，而不是CreateObject(scriptl . dictionary)。

0  

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.

您的评论中提到的一个可能令人关注的领域是“Sheet1”和“Sheet2”的使用。在Excel VBA中，有两种不同的方法来引用工作表。是工作表的名称，这是用户在Excel中的选项卡上看到的，用户可以随意更改。默认名称为“Sheet1”、“Sheet2”等。

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.

每个工作表都有“Codename”。在Visual Basic编辑器中，project explorer窗口将列出“Microsoft Excel对象”下的所有工作表。在这里，您将看到每个工作表的代号，并在圆括号中显示工作表的名称。

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".

当您使用工作表(“Sheet1”)时，“Sheet1”指的是名称，而不是代号。有可能最终得到一个名为“Sheet1”和代号“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.

就函数而言，我注意到在这两种情况下都声明了局部变量——第一个是数组“cities”和“accounts”，第二个是两个加上字典“dict”。您有代码来填充这些局部变量，但是对它们什么都不做。函数的返回值不依赖于任何这些局部变量。

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.

一旦函数代码完成，这些局部变量就会丢失它们的值。VBA将用于将这些变量存储到可用内存池中的内存返回，以供其他用途重用。

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.

我不确定你打算在这些函数中完成什么。如果你能解释一下，会有帮助的。

-2  

To use CreateObject(Scripting.Dictionary) you have to reference "Microsoft Scripting Runtime". The problem is you probably have to upgrade the .dll file from microsoft website because your current .dll from standart windows installation doesn't support this method (I've been in the same situation myself with a Windows 7 version).

要使用CreateObject(script. dictionary)，您必须引用“Microsoft脚本运行时”。问题是，你可能需要从微软网站升级.dll文件，因为你当前的.dll文件不支持这个方法(我自己也有windows 7版本)。

However, I tried to reach microsoft page where I downloaded the update and can't find it. So, I recommend you cross your array contents:

然而，我试图进入微软的页面，在那里我下载了更新，却找不到它。所以，我建议你交叉你的数组内容:

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

For i = 2 To 500
    If cities(i) = Place Then
        placeName = accounts(i)
        Exit For
    End If
Next i

Account = placeName

End Function