VBA array inverse

Question

I am trying in Excel VBA to get some values from a spreadsheet in a square array invert this array. I have the following code:

Private Sub CommandButton1_Click()
Dim A As Variant
Dim i As Integer, j As Integer
ReDim A(1 To 3, 1 To 3) As Double

For i = 1 To 3
    For j = 1 To 3
        A(i, j) = Cells(i, j).Value      
    Next j
Next i

A = Application.WorksheetFunction.MInverse(A)

End Sub

In the line:

A = Application.WorksheetFunction.MInverse(A)

I get the error:

run-time error 1004: application defined or object defined error

Can anyone assist me on this?

Answer 1

You may be trying to invert an ill-conditioned matrix. I tried your code on an easy example:

Sub dural()

    Dim A As Variant
    Dim i As Integer, j As Integer
    ReDim A(1 To 3, 1 To 3) As Double

    For i = 1 To 3
        For j = 1 To 3
            A(i, j) = Cells(i, j).Value
        Next j
    Next i

    A = Application.WorksheetFunction.MInverse(A)

    For i = 1 To 3
        For j = 1 To 3
            Cells(i + 5, j + 5).Value = A(i, j)
        Next j
    Next i
End Sub

and got:

which appears to be correct. (the product of the two matrices is very close to a unit matrix)