VBA中MInverse与MMULT函数调用失败问题咨询

使用Excel 2407版本（内部版本17830.20166 即点即用），在VBA中创建方阵pow，将其写入Excel后用MINVERSE函数可正常运算，但在VBA中调用WorksheetFunction.MInverse(pow)时出现错误：Unable to get the Minverse property of the WorksheetFunction class。

尝试过Option Base 1无效，将inv_pow声明为Variant类型并执行以下代码后调用成功：

ReDim inv_pow(r_num - 1, r_num - 1)
inv_pow = WorksheetFunction.MInverse(pow)

疑问：该方法是否为最优解？

另外，调用MMULT函数时出现相同错误，出错代码：

coef = WorksheetFunction.MMult(pwr, vector)

完整VBA代码如下：

Function data_coef()

    Dim r As Integer, c As Integer
    Dim r_start As Integer, r_stop As Integer, c_start As Integer, c_stop As Integer, cnum As Integer, rnum As Integer
    Dim m() As Double
    Dim pow() As Double, inv_pow() As Variant, coef() As Variant, vector() As Double
    Dim temp As Double
    
' given an array of data starting at cells(r_start,c_start)
    
    r_start = 5
    c_start = 6
    
' find the last row of non-blank data and calculate the number of data rows (max(rows) = 101)):

    For r = r_start To r_start + 100
        If Sheets("data").Cells(r, 6) = "" Then Exit For
    Next r
    
    r_stop = r - 1
    r_num = r_stop - r_start + 1
    
' find the last column of non-blank data and calculate the number of data columns (max(colums = 21)):

    For c = c_start To c_start + 20
        If Sheets("data").Cells(5, c) = "" Then Exit For
    Next c
    
    c_stop = c - 1
    c_num = c_stop - c_start + 1
    
' Read the data into matrix m:

    ReDim m(1 To r_num, 1 To c_num)
    
    For r = 1 To r_num
        For c = 1 To c_num
            m(r, c) = Sheets("data").Cells(r + r_start - 1, c + c_start - 1)
        Next c
    Next r
    
' Create a sequence of powers of the data values in the second column starting with the one in the second row:

    ReDim pow(r_num - 1, r_num - 1)
    
    For r = 1 To r_num - 1
        For c = 1 To r_num - 1
           pow(r, c) = m(r + 1, 1) ^ (c - 1)
           Sheets("POWER").Cells(r, c) = pow(r, c)
        Next c
    Next r

' Create inverse of matrix pow():

    ReDim inv_pow(r_num - 1, r_num - 1)
    
    inv_pow = WorksheetFunction.MInverse(pow)
    
    For r = 1 To r_num - 1
        For c = 1 To r_num - 1
           pow(r, c) = m(r + 1, 1) ^ (c - 1)
           Sheets("MATVER").Cells(r + 20, c) = inv_pow(r, c)
        Next c
    Next r
    
' Calculate coefficients:

    ReDim vector(r_num - 1)
    ReDim coef(r_num - 1)
    
    For r = 1 To r_num - 1
        vector(r) = m(r + 1, 2)
        'MsgBox (vector(r))
    Next r
    
'  THE FOLLOWING CODE CAUSES THE ERROR:

    coef = WorksheetFunction.MMult(pwr, vector)
    
End Function

1. 将inv_pow声明为Variant是否是MInverse报错的最优解？ #

不是最优解，甚至没必要提前ReDim。WorksheetFunction.MInverse返回的是二维Variant数组（哪怕原矩阵是Double类型），正确做法是直接声明inv_pow为Variant，不用提前指定维度：

Dim inv_pow As Variant
inv_pow = WorksheetFunction.MInverse(pow)

你之前提前ReDim属于冗余操作，因为赋值时MInverse返回的数组会直接覆盖预先定义的维度。而且Variant数组完全兼容后续的遍历、写入工作表等操作，不需要额外转换。

原错误的核心原因是：最初如果把inv_pow声明为Double类型数组，MInverse返回的Variant数组和它类型不匹配，导致报错。改成Variant是正确的解决方向，但提前ReDim完全没必要。

2. 解决MMULT函数的调用错误 #

你的MMULT报错有两个关键问题：

• 变量名拼写错误：代码里写的是pwr，但定义的矩阵变量是pow，这是笔误！
• 数组维度不匹配：MMULT要求第一个参数的列数等于第二个参数的行数。你的vector是一维数组，在VBA里一维数组默认是列向量，pow是(r_num-1, r_num-1)的方阵，所以MMULT(pow, vector)是合法的（方阵列数=向量行数），但要确保vector维度正确。

另外，coef声明为一维Variant数组，但MMULT返回的是二维数组（哪怕结果是列向量），直接赋值给一维数组会报错。正确写法：

Dim coef As Variant
' 修正变量名拼写错误
coef = WorksheetFunction.MMult(pow, vector)

如果需要转成一维数组，可以用Application.Transpose，但直接用二维数组更稳妥，写入工作表时也不用额外处理。

补充：使用WorksheetFunction的矩阵函数时，若矩阵不可逆（比如行列式为0）也会触发报错，建议加错误处理：

On Error Resume Next
inv_pow = WorksheetFunction.MInverse(pow)
If Err.Number <> 0 Then
    MsgBox "矩阵不可逆，无法计算逆矩阵"
    Exit Function
End If
On Error GoTo 0

内容的提问来源于stack exchange，提问作者Christopher Paul