Wet Bread‎ > ‎

Tool for Parsing Formulas in Excel

Creating a tool to parse the Excel formulas was a pretty big challenge...

Use RegExp to do the job has doubled my enthusiasm!
Krisztina is the culprit / accomplice in this work. First she threw her glove .... then worked with me to achieve it. Also important is the contribution of Gábor ... here is the team is now complete.

There are 2 versions we have created for each addins: a version for Excel 2003 and an other for Excel 2007.
The heart of both is the function that uses a regular expression pattern to parse the formula.

The pattern consists of 7 alternating backreference.


Each group tries a different part of the formula syntax. The order in which they are arranged is very important. For example a text or the name of a sheet could contain parentheses deceiving the parsing process. Therefore they should be investigated first.

These are the 7 backreference patterns:
1.    ("[^"]*"|'[^']*')
2.    (\{[^}]+})
3.    (\;)
4.    ([^=\-+*/();:,.$<>^]+(?:\.[^=\-+*/();:,.$<>^]+)*\()
5.    (\))
6.    (^=|\()
7.    (.)



They perform the following tasks, in order:
1.    Represents a text (delimited by double quotes) or a sheet name (delimited by single quotes)
2.    Represents a constant matrix
3.    Represents a list (function parameter) separator
4.    Represents a function name followed by an opening parenthesis
5.    Represents a closing parenthesis
6.    Represents the beginning of the formula or an opening parenthesis (not part of a function)
7.    Each characters not matched by the previous patterns

The most important places are the points where the formula has to be broken. In the parsed formula the first and the last found group will be left unchanged, while others will be used to set breakpoints by adding indentation and line breaks.
Arrays of constants are rewritten for revealing the structure (using a special function).

The function searches all the results and performs a check on each of the submatches (groups). By taking the array of the matched results the function reconstructs the formula by adding carriage return to separate the elements.
An other outer function formats the text by adding tabs.
The simple version of the tool is limited to show this formatted result in a textbox. It contains the possibility to change the reference style and the language of the formula. It will be appreciated by those who want an agile and simple tool.


The more sophisticated version uses a treeview: adding hierarchical display that helps to analyze the fragments.
You can find a detailed explanation about the features in the User's manual.


Our Parser Tool will help you check and analyze your formulas – especially great help for nested formulas.
It is an add-in, you will need to initialize it.
Under Office button - Excel Options choose Add-Ins. At the buttom of the tab you find Manage: Excel Add-Ins. Click on Go… In the pop-up window click Browse… and select the file.
After it you can find the Parsing icon and User's Manual under Formulas on the ribbon.


Best regards

by The FrankensTeam

Links:

van Gelder
ewbi.develops


Quotes:

Frederic LE GUEN MVP (Analyze a formula with F9)


Ĉ
r,
Oct 13, 2011, 8:47 AM
ċ
parser_formule_with_textbox_v01_2007.xlam
(201k)
r,
Oct 13, 2011, 8:45 AM
Ĉ
r,
Oct 15, 2011, 1:54 PM
ċ
parser_formule_with_treeview_v01_2007.xlam
(252k)
r,
Oct 13, 2011, 8:45 AM
Comments