How to Parse the Excel Formula using Excel DNA

[Rajini K]

Hi Govert,

Using Excel-DNA, we have created the our custom  Excel Function.

I want to Read/Parse the Active Excel Cell formula. My requirement  is when user clicks the our Add-In Ribbon Button("Parse Current Function") will open WPF window to show the Function information like Native Excel Function Arguments Window(fx) behaviour .

Using the C# code, I’m able to get the formula

(i.e)  string currentFormula  = Globals.ThisAddIn.Application.Range[cellIndex].Formula

For example : If a Active Cell contains the formula,

   =Test.Data.Columns(I13:K14) or = Test.Data.Columns (1,23,333,2661,771), 

I need to read the both Function and its Parameters values.   

In your old post, you gives information to getFormula using ExcelDNA function :

In VB, the function could look like this: 

    <ExcelFunction(IsMacroType:=True)> _ 

    Public Function GetFormula( <ExcelArgument(AllowReference:=True)> 

input as Object) As String 

        Dim formula As String 

        If TypeOf input Is ExcelReference Then 

            formula = XlCall.Excel(XlCall.xlfGetFormula, input) 

            Return "Formula: " & formula 

        Else 

            Return "<Not a reference>" 

        End If 

    End Function

I tried to get formula using Excel –DNA when WPF window is Loaded. I’m getting the Exception. From here, I’m not able to access the XlCall.Excel(XlCall.xlfCaller). 

private void FunctionBuilder_OnLoaded(object sender, RoutedEventArgs e)

{

  try

  {

   ExcelReference cell = (ExcelReference)XlCall.Excel(XlCall.xlfCaller);

   string formula = (string)XlCall.Excel(XlCall.xlfGetFormula,cell);

  }

  Catch(Eception ex){

  }

}

To achieve my requirement, Is there any way to get Excel Formula information using Excel-DNA .?

Please Help on this issue. 

Thanks,

Rajini.

[Patrick O'Beirne]

You can get the properties using COM automation: .Formula, .FormulaR1C1

Parsing: is this any help?
https://github.com/spreadsheetlab/XLParser
A C# parser for Microsoft Excel formulas with a 99.9% compatibility rate

[Govert van Drimmelen]

Hi Rajini,

 

You have to be a bit careful when interacting with Excel from a form (whether you use WPF or Windows Forms).

 

Depending on which thread is running the form, you might find that Excel is not responding to your interaction, or that your calls to Excel might fail because of user interaction in Excel.

 

To some extent, using a Custom Task Pane instead of an independent form can be easier.

 

Your VSTO line:

    string currentFormula  = Globals.ThisAddIn.Application.Range[cellIndex].Formula

is using the COM interface to talk to Excel.

 

Inside an Excel-DNA add-in you can also do this, you just have to make sure you add the right References (Microsoft.Office.Interop.Excel) and get hold of the right COM Application object (with a call to ExcelDnaUtil.Application).

 

Once you have the Application object, you can get the ActiveWorkbook, or the ActiveCell or whatever, and get the formula from there.

 

The other code you show, using XlCall.Excel(XlCall.xlfGetFormula, …), is based on the C API. This will definitely not work if you are running on a separate thread – I think that’s why you’re getting an exception when running this from your forms’ event handler.

 

For your use, the COM object model will probably work better.

 

If you want to understand different ways of showing a form from Excel, you can read and look at the code here: https://github.com/Ron-Ldn/DotNetRefEdit

 

Regards,

Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.