Solved

Evaluating Excel formulae (formulas if you prefer)

Posted on 2004-10-18
3
438 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Assisted Solution

by:vadim_ti
vadim_ti earned 250 total points
ID: 12337373
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
ID: 12341570
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
ID: 12344141
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

696 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