I have created a matrix of numerical data in Excel version 2407 Build 17830.20166 Click-to-Run.

From part of that data, in VBA I’ve created a square matrix pow. I’ve written pow back to another location in excel using the same loop I used to assign its values. The Minverse function works just fine on the pow in excel. But when I execute Miverse on pow in VBA, I get “Unable to get the Minverse property of the WorksheetFunction class.’

I have searched on this error and found My MInverse function will not work in VBA.

It suggested I try option base 1. The error message did not change.

It also suggested that the range of the function was incorrect. As I mentioned before, the same data (as best I can tell) in excel works fine with excel’s MINVERSE function.

So I declared the parameter inv_pwr as variant instead of as double and then wrote

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

It worked! Is this the best way to solve this problem?

Unfortunately, when I tried the same approach with a different set of variables and the function MMULT, it again worked in excel, but I got the same error message in vba that I had before.

Here’s my code:

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