# How to inverse a matrix in VBA ?

I want to inverse a matrix which is in a variable by using the Excel funcion MINVERSE().

Example :

Sub InversionMatrice()
Dim Matrice(1 to 4, 1 to 4) As Double, Inverse(1 to 4, 1 to 4) As Double, i As Integer, j As Integer
For i=1 to 4
For j=1 to 4
Matrice(i,j)=...
Next
Next
Inverse=Application.Minverse(Matrice)
End Sub

But it doesn't work !!! Why ???
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
MINVERSE is an array function and an assignment what you are trying to do is not allowed. You have to use a sheet as a "middleman" here, eg.

Sub InversionMatrice()
Dim Matrice(1 To 4, 1 To 4) As Double, Inverse(1 To 4, 1 To 4) As Double, i As Integer, j As Integer
For i = 1 To 4
For j = 1 To 4
Matrice(i, j) = Rnd ' Fill matrice with random numbers
ActiveSheet.Cells(i, j) = Matrice(i, j)
Next
Next
Range("A6:D9").Select
Selection.FormulaArray = "=MINVERSE(R[-5]C:R[-2]C[3])"
For i = 1 To 4
For j = 1 To 4
Inverse(i, j) = ActiveSheet.Cells(i + 5, j)
Next
Next
End Sub

0
Author Commented:
Thanks for your answer, but I wanted to do it by another way.
0
Commented:
Your question was: You want to use MINVERSE function to invert a matrix and why does the sub you showed not work?

First, the sub does not work because MINVERSE is a WORKSHEET function, not a VBA function, so the assignment you are trying to do is impossible. VBA does not know what to do with MINVERSE.

Secondly (as a corollary), the only way to use MINVERSE is what I showed you (pronciple, details can vary). It is a worksheet function, so it must be inserted into a sheet and let Excel evaluate it.

If you want to use a sub to invert your matrix then you have to write one yourself...VBA does not offer matrix functions. But that is totally another question.

I hope this clarifies my first answer.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Dear Rantanen,

NOTHING is impossible !!!

I finally succeed in what I wanted to do : inversing a matrix
with VBA and without using an intermediate worksheet.

The solution was to declare the variable Inverse as VARIANT and then to write (as I first said) :

Inverse=Application.MInverse(Matrice)
0
Commented:
I am ashamed (making that claim) and grateful (for you telling me). I have tried to learn never to say never, but...

I don't know how to give you back your points, because I'd really like to do that. If you ever feel like asking something and still have a little trust left in me, e-mail me at Lasse.Rantanen@sci.fi.

0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.