Solved

How to inverse a matrix in VBA ?

Posted on 1997-12-09
5
8,179 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

738 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