How to repeat table function sequence in excel?

Table-valued functions in finaquant .net libraries

How to repeat table function sequence in excel?

Postby descart » 16 Mar 2015, 19:15

Hello, my question is related with your excel add-in for table functions. How can i replay a sequence of table functions in excel?

For example, i have 3 input tables, applied some table functions on these data tables like table multiplication, aggregation, combination etc. in a certain order to generate some results. How can i repeat exactly the same sequence of table functions in a practical way after making some changes in input tables, in order to update the output tables? Is there a single push-button solution? Thanls
descart
 
Posts: 2
Joined: 03 Oct 2013, 19:59

Re: How to repeat table function sequence in excel?

Postby tunc » 14 Apr 2015, 13:36

Hello descart
There can be two approaches to execute a chain of table functions in Excel:

1) Write a new table-valued function in C#/.NET (or in VB.NET) which calls all the table functions sequentially, as explained here:

How to Add a New Table Function to Excel

2) Write an Excel Macro in VBA to call the table functions in sequence.

Following example shows, how you can call a table function in VBA macro:
C# code in Excel add-in:
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. [ComVisible(true)]
  2. [ClassInterface(ClassInterfaceType.AutoDual)]
  3. [ProgId("Finaquant_ExcelTable")]
  4. public class ExcelTable
  5. {
  6. ...
  7. public void GetPriceTable(string xCostTblName, string xMarginTblName,  
  8.     string xMetaTblName = null, string WorkbookFullName = null,  
  9.     string xPriceTblName = "Price", string TargetSheetName = "PriceTable",
  10.     string inKeyFig1 = "costs", string inKeyFig2 = "margin",  
  11.     string outKeyFig = "price", double StdMargin = 0.25,  
  12.     string CellStr = "A1", bool ClearSheetContent = true)
  13.      
  14. public void GetPriceTable_macro()
  15.  
GeSHi ©
...
Calling Table Functions in Excel VBA (macro):
Code: [Select all] [Expand/Collapse] [Download] (Untitled.txt)
  1. ' Calling GetPriceTable in Excel VBA
  2. Sub Test_GetPriceTable()
  3. Dim ExcelTbl As Object: Set ExcelTbl = CreateObject("Finaquant_ExcelTable")
  4. ' Call .NET method with parameters
  5. Call ExcelTbl.GetPriceTable("Cost", "Margin1")
  6. ' Call .NET method without parameters (macro)
  7. Call ExcelTbl.GetPriceTable_macro
  8. End Sub
GeSHi ©


In my opinion, writing a higher-level new table function in .NET is the simpler and better solution.

The names of the input tables (ListObject in Excel) can either be hardwired in the code, or they can be read from some Excel cells, or they can be entered in user forms as explained above.

Tunc
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 1 guest

cron