如何使用数组来提高效率，而不是在VBA

Question

查找

我已经被用于查找在Excel工作表中的信息的功能知道： - 键可以是一个变量列 - 变量字段可以搜索 表格通常不到一百列，但可以从几百到几十万行进行搜索。在我们最大的文件中，我试图优化的功能可以使用大约一百万次。

阅读 https://fastexcel.wordpress.com/2011/10/26/match-vs-find-vs-variant-array-vba-performance-shootout/

...并找到我们的函数用于查找（3次）之后，我尝试使用数组。

这是我写的

Function getInfo(Key As String, NameField As String, NameKey As String, WksName As String) As Variant 

On Error GoTo Error 

Dim iColumnKEY As Integer 
Dim iColumnFIELD As Integer 
Dim i As Integer 
Dim ListFields, ListKeys As Variant 

ListFields = Worksheets(WksName).Range("A1:ZZ1") 


i = LBound(ListFields, 2) 

'To identify which column contains the Key and which one contains the 
'information we are searching for 
Do While iColumnKEY=0 Or iColumnFIELD=0 
    If i > UBound(ListFields, 2) Then 
     getInfo = "//error\\" 

    ElseIf ListFields(1, i) = NameKey Then 
     iColumnKEY = i 
    ElseIf ListFields(1, i) = NameField Then 
     iColumnFIELD = i 
    End If 
i = i + 1 
Loop 

Dim iROW As Integer 

ListKeys = Worksheets(WksName).Columns(iColumnFIELD) 

i = LBound(ListKeys, 1) 
Do While iROW=0 
    If i > UBound(ListKeys,1) Then 
     getInfo = "//error\\" 

    ElseIf ListKeys(i,1) = Key Then 
     iROW = i 
    End If 
    i = i + 1 
Loop 

getInfo = Worksheets(WksName).Cells(iROW, iColumnFIELD) 

Exit Function 

Error: 
    getInfo = "//error\\" 

End Function 

代码工作的代码，但速度很慢。我在做什么会减慢速度？

它现在不在代码中，但我确实尝试了关闭屏幕更新以及自动计算。我没有看到速度上的差异，这表明我的基本算法是主要问题。

此外，该文章是在2011年。数组仍然比Match/Find快很多？作为一个方面说明：最终，我会建议让一个宏在批处理中搜索一系列键，而不是为每个键调用该函数。这意味着第一个Do ... While循环对于一个宏只能执行一次，并且只有Do_While for Rows才会针对每个键运行。但是，这在短期内不是一种选择。

感谢。任何帮助或建议将不胜感激。

Answer 1

要了解的部分代码是最慢的，你可以使用Timer：

Dim t as Single 
t = Timer 
' part of the code 
Debug.Print CDbl(Timer - t) ' CDbl to avoid scientific notation 

使用的.Value2代替.Value应该有点帮助：

ListFields = Worksheets(WksName).Range("A1:ZZ1").Value2 

搜索键和两个独立循环中的字段应该稍微快一点，因为比较次数会少一些。另外，我不知道这是否是有点慢或更快，但你可以遍历甚至多维数组：

Dim i As Integer, v ' As Variant 
i = 1 

For Each v in ListFields 
    If v = NameKey Then 
     iColumnKEY = i 
     Exit For 
    End If 
    i = i + 1 
Next 

Answer 2

在你的代码永远不会使用iColumnKEY

我想这就是你实际上是：

Function getInfo(key As String, NameField As String, NameKey As String, WksName As String) As Variant 

    Dim keyCol As Variant, fieldCol As Variant, keyRow As Variant 
    Dim errMsg As String 

    getInfo = "//error\\" 

    With Worksheets(WksName) 
     With Intersect(.UsedRange, .Columns("A:ZZ")) ' <--| reference a range in passed worksheet cells belonging to columns "A" to "ZZ" from worksheet first used row to last used one and from worksheet first used column to last used one 
      MsgBox .Address 
      fieldCol = Application.Match(NameField, .Rows(1), 0) '<--| look for passed 'NameField' in referenced range 
      If IsError(fieldCol) Then 
       errMsg = " :field column '" & NameField & "' not found" 
      Else 
       keyCol = Application.Match(NameKey, .Rows(1), 0) '<--| look for passed 'NameKey' in referenced range 
       If IsError(keyCol) Then 
        errMsg = " :key column '" & NameKey & "' not found" 
       Else 
        MsgBox .Columns(keyCol).Address 
        keyRow = Application.Match(key, .Columns(keyCol)) '<--| look for passed 'key' in referenced range 'NameKey' column 
        If IsError(keyRow) Then 
         errMsg = " :key '" & key & "' not found in column '" & NameKey & "'" 
        Else 
         getInfo = .Cells(keyRow, fieldCol) '<--| get referenced range "item" 
        End If 
       End If 
      End If 
      If errMsg <> "" Then getInfo = getInfo & errMsg 
     End With 
    End With 
End Function