Solved

Evaluating Excel formulae (formulas if you prefer)

Posted on 2004-10-18
3
430 Views
Last Modified: 2010-04-05
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




 
0
Comment
Question by:Mutley2003
3 Comments
 
LVL 6

Assisted Solution

by:vadim_ti
vadim_ti earned 250 total points
Comment Utility
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
 
LVL 31

Accepted Solution

by:
moorhouselondon earned 250 total points
Comment Utility
The attached link deals with the parsing half of the problem:-

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


0
 

Author Comment

by:Mutley2003
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now