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

Calling Table Functions from Excel

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.


Visual Studio file 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.
Selecting tables with sheet names

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.

Meta Data for Tables

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
This entry was posted in Calculation engine and tagged , , . Bookmark the permalink.

2 Responses to Table Function Demo with Microsoft Excel

  1. ugurtanriverdi says:

    Hi Tunc,

    excel macro sample is quite useful, but i couldn’t figure out, “why you preferred to add library functions, which get directly, excel file name or sheets etc… as input parameters?”

    i would rather expect to see, a sample code, which shows how to call your library, with vbscript for example;

    This code suppose to handle, creation of the library objects and their usages, combined with similar call to excel, word, access etc…

    Is there any special user requirement, use case etc… behind this functionality?

    Is it also possible to give an example like the one i have tried to explain above?

    Shortly, i am expecting something like the code below. Of course, instead of IE, i want to create objects related to your library and call your methods…

    Thanks & Regards

    Sample vbscript code:
    Set objIE = CreateObject(“InternetExplorer.Application”)
    objIE.Visible = 1
    objIE.Navigate “http://www.google.com”
    Do Until VarType(objIE) = 9
    WScript.Sleep 1000
    Loop
    Set objIE = Nothing

Leave a Reply