excel - Excel VBA中引用相邻单元格的最有效方法是什么？

Question

我试图使用with语句，因为它们比循环快。

有72,000行，确切的数目可以变化。根据B列中的货币代码，物料代码需要进入A列。

我正在引用一个集合以根据货币代码检索代码。我能做到这一点的最快方法是什么？这是我的代码...不起作用。

Sub Collector()

Dim cn As Collection
Dim LastRow As Long
Dim cur As Long
Dim destws As Worksheet

Set destws = ThisWorkbook.Worksheets("Data")

Set cn = New Collection
cn.Add "120000037650264", "AUD"
cn.Add "140000028802654", "CAD"
cn.Add "106000061411232", "CHF"
cn.Add "100700037144679", "CNY"
cn.Add "108000077165454", "EUR"
cn.Add "100900028865402", "GBP"
cn.Add "100700034152263", "HKD"
cn.Add "103000037165403", "HUF"
cn.Add "100400055172256", "INR"
cn.Add "100090035614270", "JPY"
cn.Add "100600035472288", "KRW"
cn.Add "100040036172267", "MXN"
cn.Add "100004036162300", "PLN"
cn.Add "121000037176585", "RUB"
cn.Add "133000040272294", "THB"
cn.Add "100430020172276", "TWD"
cn.Add "109790029172291", "UAH"
cn.Add "100004007305201", "USD"
cn.Add "100003051687277", "ZAR"

LastRow = destws.Cells(Rows.Count, 2).End(xlUp).Row


    With destws.Range("A2:A" & LastRow)
        .Value = cn.Item(Cells(cur, 2).Value) 'generates object defined error
    End With

End Sub

例：
如果单元格B2的值为USD，我希望单元格A2的值为100004007305201。

任何帮助将不胜感激！

Answer 1

通过索引访问Collection项绝对是性能问题。集合要在For Each循环中进行迭代！如果您事先知道需要多少个项目，最好使用数组。通过索引访问数组项正是数组最擅长的（这就是为什么最好使用For循环对其进行迭代的原因）。

循环写入Range的效率也很低。

现在，您没有将集合/数组项转储到Range中，而是在查找键/值对。最简单的方法是使用Dictionary。 Collection也可以被设置为键（就像您一样），但是我喜欢称呼猫为猫，因此我将Dictionary用于键值对。


注意：我将假设您的键/值对是帐户/货币。根据需要进行调整；想法是命名事物，以便代码能说明一切。


您可能有一个创建，填充并返回Private Function CreateAccountsByCurrencyDictionary的Dictionary，然后您的宏可以具有Static局部变量（这样，每次调用该宏时，它不会被无用地重新初始化）来保存它：

Static accountsByCurrency As Scripting.Dictionary 'reference Microsoft Scripting Runtime
If accountsByCurrency Is Nothing Then
    Set accountsByCurrency = CreateAccountsByCurrencyDictionary
End If

然后获取工作范围并将其转储到2D数组中-最简单的方法是将数据保存在 ListObject（即命名表）中；您可以通过从“首页功能区”标签中选择“表格格式”轻松地将范围转换为表格-然后，您无需跟踪最后一行的位置，表格即可为您完成！


这里的 Sheet1是您需要使用的工作表的代号。始终用特定的工作表对象限定 Range调用。通过使用工作表的代码名称，无论 ActiveSheet是什么，您都可以使代码工作。

Dim target As Range
Set target = Sheet1.ListObjects("TableName").DataBodyRange

Dim values As Variant
values = target.Value

现在您有了一个2D数组（ values），通过 For循环对其进行迭代并进行查找：

Dim currentRow As Long
For currentRow = LBound(values, 1) To UBound(values, 1)

    ' never assume you're looking at valid data
    Dim currentKeyValue As Variant
    currentKeyValue = values(currentRow, 1)
    Debug.Assert Not IsError(currentKeyValue) ' there's a problem in the data

    ' key is a valid string, but might not exist in the lookup dictionary
    Dim currentKey As String
    currentKey = currentKeyValue
    If accountsByCurrency.Exists(currentKey) Then
        ' lookup succeeded, update the array:
        values(currentRow, 1) = accountsByCurrency(currentKey)
    Else
        Debug.Print "Key not found: " & currentKey, "Index: " & currentRow
        Debug.Assert False ' dictionary is missing a key. what now?
    End If
Next

如果一切顺利， values数组现在包含您已更正的值，则可以更新实际的工作表-并且由于您将值保存在2D数组中，因此只需一条指令！

target.Value = values

CreateAccountsByCurrencyDictionary函数可能看起来像这样：

Private Function CreateAccountsByCurrencyDictionary() As Scripting.Dictionary
    Dim result As Scripting.Dictionary
    Set result = New Scripting.Dictionary
    With result
        .Add "AUD", "120000037650264"
        .Add "CAD", "140000028802654"
        '...
    End With
    Set CreateAccountsByCurrencyDictionary = result
End Function

或者，可以从另一个工作表表中填充值，而不必进行硬编码。重要的是，如何获取查询值本身就是一个问题，并且属于其自身的范围/过程/功能。