Mega Code Archive
Formula Calculator
<![CDATA[
Title: Formula Calculator
Question: We want our application to evaluate the expressions like "2 + 3/6 - 5", as MS Excel does. Is it possible to do that without writing a sophisticated string parser? Absolutely.
Answer:
Can we compete with MS Excel with its sophisticated formula evaluation functionality and numerous functions? Doubtfully. Instead, we can make Excel work for us so a lot of its formula evaluation functionlaity would be available in a simple application.
First, we need to ensure that Excel is installed. It can be done with the following function:
{uses ActiveX}
{const sExcelApp: WideString = 'Excel.Application'}
{-------------------------------------------------------------------------}
function ExcelNotInstalled: boolean;
var
ClassID: TCLSID;
begin
Result := (CLSIDFromProgID (PWideChar(sExcelApp), ClassID) S_OK);
end; {--ExcelNotInstalled--}
If Excel is installed, let's open it behind the scene:
{uses ComObj}
{var ExcelApp, WorkSheet: Variant;}
ExcelApp := CreateOLEObject (sExcelApp);
ExcelApp.DisplayAlerts := False;
ExcelApp.WorkBooks.Add;
WorkSheet := ExcelApp.ActiveWorkBook.ActiveSheet;
We should not forget to quit our hidden Excel instance when done:
ExcelApp.Quit;
Now let's implement the most important and usually most difficult part - parsing the input string and evaluate the result. However, with the help of Excel this part becomes almost trivial:
{-------------------------------------------------------------------------}
function CalculateFormula (AFormula: string): Variant;
begin
WorkSheet.Cells [1, 1].Formula := AFormula;
Result := WorkSheet.Cells [1, 1].Value;
end; {--CalculateFormula--}
As we pass our input string to Excel, it can be as sophisticated as Excel can handle - not only "=2+3/6-5", but also something like "=2*ln(2.718281828)+pi()/2 + Fact(10)" or "=Concatenate("e=", exp(1)), or "=Today()". Of course, we need to follow Excel conventions - note a leading equal sign and double-quoted strings, for example.
That's it.
You may download a simple FCalc application using the functions described above from here (source code + executable).
]]>