Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and MINVERSE
Is there a way to use the MINVERSE and MMULT functions without reading from
or writing on a workbook. Best, Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Minverse, how come this function only returns one value? | Excel Worksheet Functions | |||
MINVERSE errors | Excel Worksheet Functions | |||
Invert a matrix larger than 52 x 52 using MINVERSE | Excel Worksheet Functions | |||
How do I use MINVERSE in Excel? | Excel Worksheet Functions | |||
Trouble with MINVERSE | Excel Worksheet Functions |