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?

I found my mistake. I had wrong the values of the matrix. Instead of using the above values i was using [11 12 13] [21 22 23] [31 32 33] from another matrix, which can not be inverted mathematically... Sorry for wasting your time but my mind was stuck for abt 2 hours. Thank you — men
– men, Commented Dec 11, 2018 at 1:53

John Alexiou · Accepted Answer · 2018-12-11 02:47:47Z

3

Try the code below to read a 3×3 array from cell A1 and write the inverse on cell A5.

Private Sub CommandButton1_Click()
    Dim A() as Variant, B() as Variant
    A = Range("A1").Resize(3,3).Value
    B = WorksheetFunctions.MMinverse(A)
    Range("A5").Resize(3,3).Value = B
End Sub

There is no need to loop through each cell, which is a slow operation. Read and write whole tables with one command using the Range().Resize().Value syntax.

answered Dec 11, 2018 at 2:47

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2018-12-11 01:51:57Z

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)

answered Dec 11, 2018 at 1:51

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

John Alexiou Over a year ago

You can skip the loops and write A = Cells(1,1).Resize(3,3).Value, but you have to declare Dim A() as Variant first. Also works in reverse Cells(5,5).Resize(3,3).Value = A.

Collectives™ on Stack Overflow

VBA array inverse

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related