
Downloads for beta release R102
- FinaquantInExcel_beta102.xll Excel add-in (digitally signed by Finaquant Analytics)
- FinaquantInExcel_beta102.zip Zip package including Visual Studio project to develop the add-in (open source)
- FinaquantExcelDemo_20140924.xls Excel workbook with a sheet named Parameters for Getting Started
- Visual User Guide Installing the add-in, table function examples, developer notes (pdf file)
Don't forget to visit article Empowering Excel with Table Functions for most recent releases and installation steps.
DEVELOPER NOTES
Three new code files are added to Visual Studio project FinaquantInExcel for matrix functions:
MatrixX.cs Class with matrix functions that can be called in Excel VBA. The methods of this class are simple wrappers that make some selected matrix functions of the underlying .NET library Finaquant Calcs available (i.e. callable) in VBA. Developers can extend this class to have more matrix functions in VBA.

Following code example shows how you can call the non-static methods of this class (matrix functions) in VBA:
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- Sub Test_MatrixX()
- Dim M1 As Object: Set M1 = CreateObject("Finaquant_KeyMatriX")
- Dim M2 As Object: Set M2 = CreateObject("Finaquant_KeyMatriX")
- Dim R As Object: Set R = CreateObject("Finaquant_KeyMatriX")
- ' read matrix from named range
- Call M1.ReadFromExcel("Matrix2")
- Call M2.ReadFromExcel("Matrix3")
- ' print properties
- Debug.Print M1.nRows
- Debug.Print M2.nRows
- ' print matrix
- Debug.Print M1.ToString()
- ' matrix multiplication
- Set R = M1.MultiplyMatrix(M2)
- ' write result to excel range
- Call R.WriteToExcel("Matrix4", "Sheet2", "", "B12")
- ' get inverse matrix
- Set R = M1.Inverse()
- ' write inverse matrix to excel range
- Call R.WriteToExcel("Matrix5", "Sheet2", "", "F2")
- ' determinant
- Debug.Print M1.Determinant()
- End Sub
- GeSHi ©
ExcelMatrix.cs A class with application oriented non-static methods (matrix functions). It includes a list of matrix functions that you want to make directly available in Excel as command menu item or a ribbon button.

The methods of this class are also available (i.e. callable) in Excel VBA, as shown in the example below:
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- ' Test ExcelMatrix class methods
- Sub Test_ExcelMatrix()
- Dim XM As Object: Set XM = CreateObject("Finaquant_ExcelMatrix")
- Dim nRows, nCols As Integer
- Dim SheetName, RangeName, TopLeftCell As String
- ' create matrix with random-valued elements
- nRows = 3
- nCols = 4
- SheetName = "Sheet2"
- TopLeftCell = "B3"
- Call XM.CreateRandomMatrix(nRows, nCols, RangeName, _
- ThisWorkbook.FullName, SheetName, TopLeftCell)
- End Sub
- GeSHi ©
Following code example (in C#/.NET) shows the typical flow of a matrix calculation (read, calculate, write) in a method of class ExcelMatrix:
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- public void MultiplyMatrices(string InRangeName1, string InRangeName2,
- string OutRangeName, string WorkbookFullName = null,
- string TargetSheetName = "ResultMatrix", string TopLeftCell = "A1")
- {
- // Typical flow of calculation:
- // Step 0: Get current application and active workbook
- Excel.Application xlApp = ExcelFunc_NO.GetExcelApplicationInstance();
- Excel.Workbook wbook;
- if (WorkbookFullName == null || WorkbookFullName == String.Empty)
- wbook = xlApp.ActiveWorkbook;
- else
- wbook = ExcelFunc_NO.GetWorkbookByFullName(xlApp, WorkbookFullName);
- // Step 1: Read excel ranges into input matrices
- KeyMatrix M1 = ExcelFunc_NO.ReadKeyMatrixFromExcel(InRangeName1,
- WorkbookFullName);
- KeyMatrix M2 = ExcelFunc_NO.ReadKeyMatrixFromExcel(InRangeName2,
- WorkbookFullName);
- // Step 2: Generate resultant (output) matrices
- KeyMatrix R = KeyMatrix.MultiplyMatrices(M1, M2);
- // Step 3: Write resultant matrices to excel
- ExcelFunc_NO.WriteKeyMatrixToExcel(wbook, R, TargetSheetName,
- TopLeftCell, OutRangeName);
- ((Excel.Worksheet)wbook.Worksheets[TargetSheetName]).Activate();
- }
- GeSHi ©
Note that each matrix is represented with the name of its range. That is, range names are used as input and output parameters. This is similar to table functions where the names of ListObjects (excel tables) are used as input and output parameters.
How to name a range in Excel: Select cells > right-mouse menu > Define Name
How to see the list of range names in a workbook: Press F3
How to delete range names: Press Ctrl-F3
See also: Deleting range names in Excel
ExcelMatrixDNA.cs The static methods of this class are attributed as either ExcelFunction or ExcelCommand.
As ExcelFunction attributed methods are available as worksheet functions in Excel:

As ExcelCommand attributed methods appear as menu option in the command menu named Table Functions.

A static method of class ExcelMatrixDNA can be called in VBA as follows:
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- ' Test ExcelMatrixDNA class methods
- Sub Test_ExcelMatrixDNA()
- Dim x As Double
- Dim RangeName As String
- ' call matrix sum
- RangeName = "Matrix1"
- x = Application.Run("MatrixSum", RangeName)
- ' display result in immediate window
- Debug.Print "Matrix sum = " & x
- End Sub
- GeSHi ©
Most methods of the class ExcelMatrixDNA do nothing other than calling the methods of the class ExcelMatrix.
Some methods that are attributed with ExcelFunction may return an array instead of a scalar value:
- Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
- [ExcelFunction(Description = "Matrix Multiplication,
- returns a 2-dim array (array formula)", Category = "Matrix Functions")]
- public static double[,] MatrixMultiplication(string RangeName1,
- string RangeName2)
- GeSHi ©

Press Control-Shift-Return to enter such functions (array formula) in a worksheet.

The formula will be automatically enveloped with curved brackets denoting that this is a function which returns an array.
In order to expand an array to see all the elements (1) select the cell with array formula with neighboring cells, (2) Press F2 to activate the array formula and (3) press Ctrl-Shift-Return to expand.

For more information about array formulas in Excel you may visit:
Introducing array formulas in Excel
Tunc