VBA SUMPRODUCT对数组列

问题描述

有没有在Excel VBA的方式来使用Application.WorksheetFunction.Sumproduct拿到2阵列SUMPRODUCT？例如，如果A和B各自两个数组有3行3列（VBA阵列，而不是Excel中的数组），有一个简单的方法来获得A的第3列的SUMPRODUCT与B的第2列？如果是这样，什么是语法？
谢谢

Is there a way in Excel VBA to use Application.WorksheetFunction.Sumproduct to get the sumproduct of 2 array columns? For example, if A and B are two arrays each with 3 rows and 3 columns (VBA arrays, not Excel arrays), is there an easy way to get the sumproduct of the 3rd column of A with the 2nd column of B? If so, what is the syntax?Thanks

推荐答案

虽然它可能是很有诱惑力的尝试和使用 WorksheetFunction.SumProduct 来做到这一点，在循环中的VBA阵列将的多的比使用工作表函数更快。在一个小的测试我有一个关于 X40 比其他发布答案的性能提升。

While it might be tempting to try and use WorksheetFunction.SumProduct to do this, looping over a VBA array will be much faster than using worksheet functions. In a small test I got about a x40 performance improvement over the other posted answer.

这是你会如何做一个简单的例子。你应该对输入和错误处理添加有效性检查。

This is a simple example of how you might do it. You should add validity checks on the inputs and error handling.

Function ArraySumProduct(aA As Variant, aB As Variant, col1 As Long, col2 As Long) As Variant
    Dim i As Long
    Dim dSumProduct

    For i = LBound(aA) To UBound(aA)
        dSumProduct = dSumProduct + aA(i, col1) * aB(i, col2)
    Next
    ArraySumProduct = dSumProduct
End Function

这篇关于VBA SUMPRODUCT对数组列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！