WorksheetFunction 物件的執行速度比 UDF 等效物件快

VBA 是在執行時編譯的,它對它的效能有很大的負面影響,內建的所有內容都會更快,嘗試使用它們。

作為一個例子,我正在比較 SUM 和 COUNTIF 函式,但你可以使用 if 來解決任何你可以使用 WorkSheetFunctions 解決的問題。

對它們的第一次嘗試是迴圈遍歷範圍並逐個單元地處理它(使用範圍):

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

一個改進可以是將範圍值儲存在陣列中並處理:

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

但是不是編寫任何迴圈,而是可以使用 Application.Worksheetfunction,這對於執行簡單的公式非常方便:

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

或者,對於更復雜的計算,你甚至可以使用 Application.Evaluate

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

最後,每次執行超過 2.5,000 次,這裡是平均(5 次測試)時間(以毫秒為單位)(當然,每臺電腦上都會有所不同,但相互比較它們的行為類似):

  1. UseWorksheetFunction:2156 ms
  2. UseArray:2219 毫秒(+ 3%)
  3. UseEvaluate:4693 ms(+ 118%)
  4. UseRange:6530 毫秒(+ 203%)