Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to inverse a matrix in VBA ?

Posted on 1997-12-09
Medium Priority
8,678 Views
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
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

LVL 4

Expert Comment

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

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

LVL 4

Accepted Solution

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

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

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

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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 Month8 days, 5 hours left to enroll

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

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