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.
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:
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:
Following code example (in C#/.NET) shows the typical flow of a matrix calculation (read, calculate, write) in a method of class ExcelMatrix:
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:
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:
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