Evaluating Excel formulae (formulas if you prefer)

Anyone know how to do this ?

Given an Excel formula like
=SUM(D4:H12,A14-B15*2,100-(A19+B19*2))
how can I parse it and evaluate it???

There are expression evaluators at
http://swiss.torry.net/expressions.htm
but none specifically mention Excel.

Is there some formal definition of the Excel formula language? If so, one might expect that something like Lex and Yacc could be used. But that is a bit too much heavy lifting for me.

What about tweaking one of the existing expression evaluators ? Anyone have experience/suggestions. And yes I do want to parse AND evaluate, because I want to be able to identify cells that have been referenced in formulae




 
Mutley2003Asked:
Who is Participating?
 
moorhouselondonConnect With a Mentor Commented:
The attached link deals with the parsing half of the problem:-

http://www.mcs.vuw.ac.nz/~db/FishBrainWiki?Excel


0
 
vadim_tiConnect With a Mentor Commented:
i think you can try to use "Excel.Application" object

  FExcel := CreateOleObject('Excel.Application');

info below is from MSDN about "Excel.Application" object

Visual Studio Tools for the Microsoft Office System  

How to: Run Excel Calculations Programmatically
The Calculate method runs all calculations in the specified object. Use the method of the object that limits the calculation range to the smallest number of cells that you want to recalculate. The recalculation engine in Microsoft Office Excel 2003 is very fast, but if you can limit the number of cells involved, you can optimize the operation.

To run calculations in a range

Call the Calculate method of the specified range. The following example requires a range named "SomeNamedRange" in the worksheet:
' Visual Basic
ThisApplication.Range("SomeNamedRange").Calculate()

// C#
ThisApplication.get_Range("SomeNamedRange",Type.Missing).Calculate();
To run calculations in all open workbooks

Call the Calculate method of the Application object:
' Visual Basic
ThisApplication.Calculate()

// C#
ThisApplication.Calculate();
0
 
Mutley2003Author Commented:
moorhouselondon --the fishbrain stuff is cool .. a good link, thanks.

vadim - not quite what I had in mind, but there are some good ideas there.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.