excel - VBA:对矩阵求和

Question

为什么这个功能不起作用？

在 Excel 中输入 =funtest(2.1)，它会给出 #VALUE!。

Public Function funtest(a As Double) As Double

Dim z, j, i As Integer
Dim matrix(3, 3, 3) As Double

For z = 0 To 3 Step 1
For j = 0 To 3 Step 1
For i = 0 To 3 Step 1

matrix(z, j, i) = a

Next i, j, z

funtest = Application.WorksheetFunction.Sum(matrix)

End Function

Answer 1

WorksheetFunction.Sum 将适用于范围或二维数组。它会出错，因为您向它传递了一个 3 维数组。

所以，这有效

Public Function funtest(a As Double) As Double
    Dim z As Long, j As Long, i As Long
    Dim matrix() As Double

    ReDim matrix(0 To 3, 0 To 4)
    For j = LBound(matrix, 1) To UBound(matrix, 1)
    For i = LBound(matrix, 2) To UBound(matrix, 2)
        matrix(j, i) = a
    Next i, j

    funtest = Application.WorksheetFunction.Sum(matrix)
End Function

请注意，我稍微修改了您的声明，请参阅答案末尾的注释。

要对高维数组求和，您需要执行一些循环。

一个选项(可能适合也可能不适合您的总体要求)是稍微不同地声明您的数组，即所谓的锯齿状数组。

Public Function funtest2(a As Double) As Double
    Dim z As Long, j As Long, i As Long
    Dim matrix() As Variant
    Dim InnerMatrix(0 To 4, 0 To 4) As Double

    ' Dimension Jagged Array
    ReDim matrix(0 To 4)
    For i = LBound(matrix, 1) To UBound(matrix, 1)
        matrix(i) = InnerMatrix
    Next

    'Load Data into matrix
    For z = LBound(matrix) To UBound(matrix)
    For j = LBound(matrix(z), 1) To UBound(matrix(z), 1)
    For i = LBound(matrix(z), 2) To UBound(matrix(z), 2)
        matrix(z)(j, i) = a
    Next i, j, z

    ' Sum matrix
    For z = LBound(matrix) To UBound(matrix)
        funtest2 = funtest2 + Application.WorksheetFunction.Sum(matrix(z))
    Next
End Function

这是一个二维数组的数组。然后将 Sum 依次应用于每个内部数组。这样，至少您只循环一个维度，而不是所有三个维度。

关于 Dim 和 Integer 的注意事项
您必须指定所有As Type，否则变量默认为Variant
在您的代码中，z 和 j 将是 Variants

此外，在 32 位操作系统上使用 Integer 而不是 Long 实际上会适得其反:Long 会稍微快一些。