Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to inverse a matrix in VBA ?

Posted on 1997-12-09
5
Medium Priority
?
9,268 Views
Last Modified: 2008-02-01
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
Comment
Question by:96404918
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:rantanen
ID: 1444350
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 Comment

by:96404918
ID: 1444351
Thanks for your answer, but I wanted to do it by another way.
0
 
LVL 4

Accepted Solution

by:
rantanen earned 400 total points
ID: 1444352
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
 

Author Comment

by:96404918
ID: 1444353
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
 
LVL 4

Expert Comment

by:rantanen
ID: 1444354
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question