We could build models much more quickly if we could directly parse formulae.
A good start on fomulae parsing in VBA has been made by Rob Vangelder with his AudXL add-in for breaking formulae into their component parts (but see below for an updated version).
On 12 Jan 2010, Rob wrote: “You can use AudXL however you wish. I’m ok with the licensing, and I do appreciate the thought with regard to acknowledgement. AudXL was written about 5 years ago. About a year ago I re-wrote the engine. You’ll probably want to use that instead.” Rob’s new add-in has been updated here.
The project http://poi.apache.org/spreadsheet/formula.html includes Java code for reading and writing Excel spreadsheets. This includes setting a formula in a cell, which requires parsing the formula and storing it using easy-to-evaluate token. The format for these spreadasheets is documented by OpenOffice at http://sc.openoffice.org/excelfileformat.pdf
Other resources (from http://stackoverflow.com/questions/2049355/c-evaluate-excel-logical-formulas) include:
- Excel Formula Parsing, in C#
- Invesitgation into Excel Syntax and a Formula Grammar
- Runtime code generation to speed up spreadsheet computations
- Excel Formula Formatter
- Implementing an Excel-like formula engine
- A .Net Excel formula parser and evaluator
- Excel formula to C# code
- Excel Formulae Parsing, including C# for .Net 2, Python, PhP, C++ and Delphi.
- Compiling Python to Excel, and then using this within a non-linear optimizer