VBA and MINVERSE
 

Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

Where are the matrices coming from? Need to know that in order to
pass them to the worksheetfunction.

Daniel wrote:
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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

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)

"Daniel" wrote in message
...
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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without reading
from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

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




"Chip Pearson" wrote:

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)

"Daniel" wrote in message
...
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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without reading
from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

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

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)


"Daniel" wrote in message
...
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




"Chip Pearson" wrote:

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)

"Daniel" wrote in message
...
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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without
reading
from
or writing on a workbook.
Best,
Daniel

VBA and MINVERSE
 

thanks lot, it worked!

"Chip Pearson" wrote:


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

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)


"Daniel" wrote in message
...
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




"Chip Pearson" wrote:

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)

"Daniel" wrote in message
...
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




"JW" wrote:

WorksheetFunction.MInverse
WorksheetFunction.MMult

Daniel wrote:
Is there a way to use the MINVERSE and MMULT functions without
reading
from
or writing on a workbook.
Best,
Daniel