手动调用('=')或通过宏调用函数性能的差异
我写了一个查找函数。它的性能与有很大的不同，这取决于我是在工作表本身(通过'=')还是通过宏手动调用它。
我的宏执行以下操作：
Function betterSearch(searchCell As Range, aCol As Range, bCol As Range)
For Each cell In aCol
If LCase(cell.Value) = LCase(searchCell.Value) Then
betterSearch = bCol.Cells(cell.row, 1)
Exit For
End If
betterSearch = "Not found"
Next
End Function该宏打开resultsWorkbook和dataWorkbook，然后从dataWorkbook的resultWorkbook中搜索列A中的四个值，并从dataWorkbook的其他一些列返回相应的数据。
'...opening resultWorkbook and dataWorkbook
For aRow = 6 To 9
resultWorkbook.Worksheets("B3").Cells(aRow, 125).Value = _
betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _
, dataWorkbook.Worksheets("page 1").Range("A:A") _
, dataWorkbook.Worksheets("page 1").Range("Z:Z"))
resultWorkbook.Worksheets("B3").Cells(aRow, 126).Value = _
betterSearch(resultWorkbook.Worksheets("B3").Cells(aRow, 1) _
, dataWorkbook.Worksheets("page 1").Range("A:A") _
, dataWorkbook.Worksheets("page 1").Range("I:I"))
Next aRow它真的很慢--一个文件需要分钟的。但当我手动打开该文件并键入公式并按Enter键时，它会立即计算(所以在这方面，我的函数不需要优化-它已经计算了fast，但只在工作表中，而不是在宏中)。
我不是在寻找来优化函数。我想要理解为什么会出现这样的性能差异，，。我为运行的主应用程序和隐形应用程序都使用了Application.Calculation = xlCalculationManual：ExcelApp.Calculation = xlCalculationManual，它似乎将工作簿的状态更改为手动，但性能很慢，就好像工作簿仍然是自动的一样。
回答 3
Stack Overflow用户
发布于 2017-07-05 17:29:17
可能与使用整个列范围有关。
我认为，当从宏调用函数时，您强制Excel为每个完整的列引用向VBA传输一百万个值(从Excel向VBA传输数据的速度非常慢)。
当从工作表中调用函数时，您只是传输一个range对象，然后逐个单元格循环，因此传输到VBA的数据量可能要少几十万倍(这取决于要查找匹配项所需的范围)。
尝试在宏中为完整的列引用设置范围变量，然后将范围变量而不是范围(i:i)传递给BetterSearch
Stack Overflow用户
发布于 2017-07-05 16:54:06
您可以使用Find函数，并使用MatchCase:=False，这样它将不区分大小写。
这将比循环遍历您的列快得多。
代码
Function betterSearch(searchCell As Range, aCol As Range, bCol As Range) As String
Dim FndRng As Range
Set FndRng = aCol.Find(what:=LCase(searchCell.Value), LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlRows, searchdirection:=xlNext, MatchCase:=False)
If Not FndRng Is Nothing Then ' Find was successful
betterSearch = bCol.Cells(FndRng.Row, 1)
Else
betterSearch = "Not found"
End If
End FunctionStack Overflow用户
发布于 2019-01-18 04:55:21
原因是，因为您循环遍历宏中某个范围的单元格。与数组相比，这是非常慢的。每个cell.value调用都在减慢。在Excel本身中，这种减慢并不是因为Range对象是应用程序本身的对象。
一次将所有需要的数据从应用程序传递到VBA要比多次只传递少量数据要慢。
如果你重写了少量的代码，它将很快依赖于你调用它的位置。
首先将searchValue存储在一个变量中。因此，您不需要在每次调用时都计算公式。在您的代码中，每次比较都会计算搜索值，但这没有任何意义，因为它不会改变。
第二，将aCol范围的值存储在一个变量数组中。对于整个列，只需非常快地完成一次。之后，您可以非常快速地比较列中的每个值(比查找或匹配函数Performance comparison of Find / Match / Variant-Array更快)
以下是您更改后的代码
Function betterSearch(searchCell As Range, aCol As Range, bCol As Range)
Dim searchVal as String
Dim colVals() as Variant
Dim r as Long
searchVal = LCase(searchCell.Value)
colVals = aCol.Value ' Read data of whole column only once
For r = 1 to Ubound(colVals,1)
If LCase(colVals(r,1)) = searchVal Then
betterSearch = bCol.Cells(r, 1)
Exit For
End If
betterSearch = "Not found"
Next
End Functionhttps://stackoverflow.com/questions/44920998
复制相似问题