VBA and MINVERSE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook.
Best,
Daniel
 
Thank you JW,
However, I am reading 2 matrices from an external file (those matrices are
not in a workbook) and try to perform those Excel function operations in VBA
without having to write the values on Excel.

Best, Daniel
 
Where are the matrices coming from? Need to know that in order to
pass them to the worksheetfunction.
 
You don't have to write the values to an Excel worksheet. See the following
example code.

Sub AAA()
Dim Arr1(1 To 2, 1 To 2) As Double
Dim Arr2 As Variant
Dim R As Long
Dim C As Long

Arr1(1, 1) = 0
Arr1(1, 2) = 2
Arr1(2, 1) = 2
Arr1(2, 2) = 0
Arr2 = Application.WorksheetFunction.MInverse(Arr1)

For R = LBound(Arr2, 1) To UBound(Arr2, 1)
For C = LBound(Arr2, 2) To UBound(Arr2, 2)
Debug.Print R, C, Arr2(R, C)
Next C
Next R
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Chip,
thanks a lot,

I am not very familiar with VBA coding, can you perhaps explain it to me or
pass me a sample.
thx
 
I am not very familiar with VBA coding, can you perhaps explain it to me
or
pass me a sample.
Click to expand...

The code is pretty straight-forward. The variable declarations

Dim Arr1(1 To 2, 1 To 2) As Double
Dim Arr2 As Variant

define the variable named Arr1 to be two dimensional array, with 2 elements
in each dimension (2 rows, 2 columns), and each element in this array is a
Double data type (double precision floating point). Arr2 is defined as a
Variant type, a type which can take on the look and feel of any data type,
including an array. The code

Arr1(1, 1) = 0
Arr1(1, 2) = 2
Arr1(2, 1) = 2
Arr1(2, 2) = 0

just loads some arbitrary values in the array Arr1. This can be considered
the matrix:

0 2
2 0

R and C are just indexes for looping through the Rows and Columns of Arr2.

The code then calls the MInverse function, passing to it the matrix Arr1,
the matrix to be inverted. MInverse returns an array contain the inverted
matrix into the variable Arr2. The code then loops through each row of Arr2
and for in each row loops through each column of the matrix and prints the
element of the MInverse result stored in Arr2 to the debug window.

For R = LBound(Arr2, 1) To UBound(Arr2, 1)
For C = LBound(Arr2, 2) To UBound(Arr2, 2)
Debug.Print R, C, Arr2(R, C)
Next C
Next R

If you paste the code I supplied into a VBA module, you can play around
different sized arrays or, to see how the (lack of) error trapping works,
make Arr1 an array that doesn't have an inverse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
thanks lot, it worked!

Chip Pearson said:
The code is pretty straight-forward. The variable declarations

Dim Arr1(1 To 2, 1 To 2) As Double
Dim Arr2 As Variant

define the variable named Arr1 to be two dimensional array, with 2 elements
in each dimension (2 rows, 2 columns), and each element in this array is a
Double data type (double precision floating point). Arr2 is defined as a
Variant type, a type which can take on the look and feel of any data type,
including an array. The code

Arr1(1, 1) = 0
Arr1(1, 2) = 2
Arr1(2, 1) = 2
Arr1(2, 2) = 0

just loads some arbitrary values in the array Arr1. This can be considered
the matrix:

0 2
2 0

R and C are just indexes for looping through the Rows and Columns of Arr2.

The code then calls the MInverse function, passing to it the matrix Arr1,
the matrix to be inverted. MInverse returns an array contain the inverted
matrix into the variable Arr2. The code then loops through each row of Arr2
and for in each row loops through each column of the matrix and prints the
element of the MInverse result stored in Arr2 to the debug window.

For R = LBound(Arr2, 1) To UBound(Arr2, 1)
For C = LBound(Arr2, 2) To UBound(Arr2, 2)
Debug.Print R, C, Arr2(R, C)
Next C
Next R

If you paste the code I supplied into a VBA module, you can play around
different sized arrays or, to see how the (lack of) error trapping works,
make Arr1 an array that doesn't have an inverse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)
Click to expand...
 
Back
Top