Matrix manipulation in VBA without printing out the worksheet

[michelle439731]

Morning,

Please can you help me out with the following issue.
I want to apply a formula to a matrix in excel which will produce another
matrix of the same dimension.
I do not want to print this new matrix out onto a worksheet but manipulate
it first in VBA seperately.

I've tried the FormulaArray code :
Worksheets("Sheet1").Range("A1:C5").FormulaArray = fMyFunction(MyRange)
But you need to print the output onto a worksheet for it to work.

How can I keep everything internal?

Thank you!

Michelle

 

[Arvi Laanemets]

Hi

Define a Named Range, which contains all data you want to read into array
(it may be also a Dynamic Named Range) - let's it be p.e. MyNamedRange;
In VBA:

....
Dim arrMyArray() As Variant
....

Set arrMyArray = [MyNamedRange]
....


Arvi Laanemets

 

[Patrick Molloy]

in this demo, matrix1 is loaded from a spreadsheet, then that matrix is
inverted and saved into natrix2

Option Explicit

Sub MatrixManipulation()

Dim matrix1 As Variant
Dim matrix2 As Variant
matrix1 = Range("B68")
matrix2 = WorksheetFunction.MInverse(matrix1)
' do something with matrix2
End Sub

you should step (using F8) througj to the end sub then view both matrices in
the watch window to see their contents