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.