数组的工作表函数与代码的 Excel VBA 性能

Question

为了加快我的 VBA 代码速度，我在线搜索了方法。许多方法都会经过，其中一个不断返回的方法似乎是尽可能使用工作表函数而不是代码。

然而，我的经验与该提示相反。我发现工作表函数往往比我的代码慢。我下面的简单测试表明该代码比工作表函数快大约两倍。我发现其他函数(例如 MATCH)也有相同的结果。

我的问题是，你们 VBA 人员倾向于使用代码还是工作表函数？是否有理由在代码上使用函数(除了几行额外代码)？

Sub testSum()

    Dim testarray(0 To 10000) As Variant
    Dim val As Variant
    Dim valSum As Variant
    Dim i As Long, j As Long
    Dim t As Single

    ' create testarray
    For i = 0 To 10000
        testarray(i) = Rnd
    Next
    For i = 0 To 10000 Step 100
        testarray(i) = "text"
    Next

    ' measure code
    t = Timer
    For j = 1 To 10000
        valSum = 0
        For Each val In testarray
            If IsNumeric(val) Then valSum = valSum + val
        Next
    Next
    Debug.Print "Array: ", Int(valSum), Timer - t

    ' measure function
    t = Timer
    For j = 1 To 10000
        valSum = 0
        valSum = Application.WorksheetFunction.Sum(testarray)
    Next
    Debug.Print "Sum: ", Int(valSum), Timer - t

End Sub

Answer 1

我更喜欢在工作表中使用工作表功能。在宏中测量函数是一种不公平的测试，因为 VBA 环境必须切换到 Excel 环境才能获得结果，这增加了其他方式所不存在的开销。

作为一般编码规则，我的优先事项是:

• 使用 native Excel 公式，包括设置辅助单元格或列。
• 将 VBA 与数组结合使用
• 使用带有范围的 VBA

作为测试:

• 设置 10,000 行随机数
• 在一个单元格中设置公式 (B1) =Sum(A1:A10000)
• 选择B1并计算单元格

设置一个宏 - 下面是基于上面代码的快速和肮脏的 - 通过使用正确限定的范围和Option Explicit，它可能会更整洁。

Sub TestNativeSpeed()
    Dim j As Long
    Dim t As Single
    t = Timer
    For j = 1 To 10000
        Range("B1").Dirty
    Next
    Debug.Print "Native Sum: ", Int(Range("B1").Value), Timer - t
End Sub

我使用公式 =Rand() 设置单元格，并得到结果(仅单次运行):

Native Sum: 4990 16.53125

我仅将它们重置为值(与您的代码一致)，并得到结果(仅单次运行):

Native Sum: 4990 1.765625

我运行了你的代码并得到了(仅单次运行):

Array: 4917 4.386719

Sum: 4917 27.73438

上面显示的是 Native Excel 通常是最快、最有效的方法。但就效率而言，数组(不必在 VBA 和 Excel 模型之间不断切换)是下一个。