excel - 在VBA中将两个大矩阵相乘

Question

我需要你的帮助在 VBA 中将两个矩阵 A 和 B 相乘。
其中 A(1000000*3) 和 B(3*3)
mmult 函数不适用于乘以大矩阵。知道如何做到这一点。

非常感谢提前，

Answer 1

我的猜测是 mmult它的索引使用 16 位整数，这不足以容纳 1,000,000 行。您可以编写自己的函数来获取包含数组的两个变体并返回包含产品的变体。使用 Long 就足够了而不是 Integer对于索引变量:

Function MatrixProduct(A As Variant, B As Variant) As Variant
    'Assumes that A,B are 1-based variant arrays

    Dim m As Long, n As Long, p As Long, i As Long, j As Long, k As Long
    Dim C As Variant

    If TypeName(A) = "Range" Then A = A.Value
    If TypeName(B) = "Range" Then B = B.Value

    m = UBound(A, 1)
    p = UBound(A, 2)
    If UBound(B, 1) <> p Then
        MatrixProduct = "Not Defined!"
        Exit Function
    End If
    n = UBound(B, 2)

    ReDim C(1 To m, 1 To n)
    For i = 1 To m
        For j = 1 To n
            For k = 1 To p
                C(i, j) = C(i, j) + A(i, k) * B(k, j)
            Next k
        Next j
    Next i
    MatrixProduct = C
End Function

为了测试这一点，我编写了一个函数来创建随机矩阵:

Function RandMatrix(m As Long, n As Long) As Variant
    Dim A As Variant
    Dim i As Long, j As Long
    ReDim A(1 To m, 1 To n)

    Randomize
    For i = 1 To m
        For j = 1 To n
            A(i, j) = Rnd()
        Next j
    Next i
    RandMatrix = A
End Function

然后我运行了这个:

Sub test()
    Dim start As Double
    Dim cases As Long
    Dim A As Variant, B As Variant, C As Variant

    cases = 1000000
    A = RandMatrix(cases, 3)
    B = RandMatrix(3, 3)
    start = Timer
    C = MatrixProduct(A, B)
    MsgBox (Timer - start) & " seconds to compute the product"

End Sub

在我的机器上大约需要 1.7 秒。