Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9703
  • Last Modified:

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 ???
0
96404918
Asked:
96404918
  • 3
  • 2
1 Solution
 
rantanenCommented:
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
 
96404918Author Commented:
Thanks for your answer, but I wanted to do it by another way.
0
 
rantanenCommented:
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
 
96404918Author 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
 
rantanenCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now