Table Function Demo with Microsoft Excel

As mentioned earlier in a related article Table Functions of the .NET libraries finaquant® calcs (commercial) or finaquant® protos (non-commercial) can be called from an excel sheet.

Following example demonstrates:

• How Table Functions can be called from excel macros (VBA)
• Generally, excel-.NET integration: How C#/.NET methods can be called from within excel macros (VBA)
• How data tables in excel worksheets can be read and written by C#/.NET methods
• How simple parameters (string, integer, double) can be passed from excel macros to C#/.NET

In order to run the demo with excel:

1. Download and install finaquant® calcs, Calculation Engine based on Table Functions (.net library).
2. Download and open the Visual Studio project CalcsExcelIntegration as Administrator (Right-mouse MS Visual Studio, Run as Administrator).
3. Build (or rebuild if you have already built) the VS project (ensure “Register for COM interop” is checked as build option under Project Properties).
4. Open the excel file named CalcsExcelDemo (double click) under the folder named Resources in VS project CalcsExcelIntegration.

By setting the parameters and pushing the corresponding button you can execute a Table Function. Because each table resides in a separate worksheet of excel, you can select a table by simply entering the name of its worksheet.

In the following code (see Excel/Developer/Visual Basic/Module1) you may see how a method CalcsExcelFunc.CombineTables in C#/.NET is called from VBA excel (macro):

' Combine selected pair of tables
' Selected tables must have some common attributes
Private Sub FQ_CombineSelectedTables()
Dim pxl As New CalcsExcelFunc
Dim Table1Sheet, Table2Sheet, MetaDataSheet, CombTableSheet As String
 
MetaDataSheet = "MetaData"
 
' Read table names
Table1Sheet = Sheets("RunMacro").Range("A14").Value
Table2Sheet = Sheets("RunMacro").Range("B14").Value
CombTableSheet = Sheets("RunMacro").Range("C14").Value
 
' call .net method
MsgBox pxl.CombineTables(Application.ActiveWorkbook.FullName, Table1Sheet, _
    Table2Sheet, MetaDataSheet, CombTableSheet)
End Sub

Following steps need to be taken if you want to update or extend the C#/.NET code in the Visual Studio project CalcsExcelIntegration:

1. Open VS project CalcsExcelIntegration as administrator (right-mouse menu, Run as Administrator).
2. Make sure that “Register for COM interop” is selected as build option.
3. After code updates, make sure that the excel file CalcsExcelDemo is closed before re-building the project.
4. Rebuild the project (build does not always work with COM).
5. Check if “Finaquant Calcs and Excel Integration” is selected in the excel file under Excel/Developer/Visual Basic/Tools/References.
6. You can now call updated methods of C#/.NET from excel macros.

You can extend this Excel Demo yourself by adding new Tables and Table Functions. Note that fields of all tables must be defined centrally in the worksheet named MetaData so that the Calculation Engine (.NET Library) knows the type of each field.

For example, if you add a new table to excel by adding a new worksheet with the table’s name, you must extend the field list of MetaData if this new table contains some new fields.

This demo is a rather simple example for .NET-excel integration. Because a Table Function Library is so easily integrated with excel macros, you can use excel as your custom cockpit for much complexer table computations like sales commissions and simulations, as we will demonstrate in upcoming articles.

Copyright secured by Digiprove © 2013 Tunc Ali Kütükcüoglu