Browse DevX
Sign up for e-mail newsletters from DevX


Build this IF Function Parser to Simplify Debugging Excel Formulas : Page 4

If you're tired of trying to decipher long Excel IF formulas displayed in a single line of code, try this convenient Formula Formatter add-in that displays such formulas neatly indented in a popup window.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Setting Up the Formula Formatter
At this point, the add-in is set up and you are ready to use the Formula Formatter. Open any workbook of interest. To bring the Formula Formatter into your environment, you need to set a reference to the FormulaFormatter.xla add-in. Go to the Tools menu and select Add-in. Click the Browse button, and navigate to the location of the FormulaFormatter.xla on your file system.
Figure 1. Using the Formula Formatter Add-in: To use the add-in, right click on any cell containing an IF function, and you'll see the option "Format Formula" on the popup menu.
Figure 2. The Formula Formatter Window: Selecting the "Format Formula" option from a cell's context menu displays the IF formula formatted and indented for enhanced readability.
After successfully registering the add-in, right click on any cell where the formula contains an IF function, and you should see the option "Format Formula" as shown in Figure 1.

When you select the "Format Formula" option, you'll see the formula appear nicely formatted and indented in a popup window (see Figure 2). This tool is a starting point. I've tested it against a number of formulas and so far, it's worked just fine; however, because it's impossible to envisage all the possible formulas that people might use in real life, it's very possible that the Formula Formatter may need more tweaking. The good news is that you can download the code for the tool, so feel free to tweak it to your heart's content.

One convenient enhancement you might consider making to the Formula Formatter is adding the ability to let users edit the formula in pseudo code, and then re-apply their changes to the worksheet. Essentially, you'd need to perform the reverse of the formula parsing process shown in this article. If you examine the code and understand how the formula was converted to pseudo-code, it should become apparent what you would need to do.

Tade Oyebode holds MSc degrees in Computing and Finance from Birkbeck College, University of London, and has written several articles for Devx. He lives in London, in the United Kingdom with his wife Kemi and works as a software developer at the Royal Bank of Canada.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date