About a month ago I produced a beta version of an add-in called AudXL. It’s a formula auditing tool which makes loooong worksheet formulas more readable:
I’m happy enough with the results it produces to release it as version 1.10.
The source code is unprotected, so rip all you want for your own projects.
Download AudXL.zip from my website: http://www.vangelder.co.nz/excel
thanks Rob, as someone who doesn’t like big formulas this is a great tool!
The perfect Formula tool would have 3 integrated windows:
1. The Formula Formatter – to Read formulas
2. The Function Arguments – to Edit formulas (linked to the Normal Edit/Insert Function button)
3. The F9 button – to Evaluate formulas (shows actual values – better then the poor Evaluate formula)
Read the formula in window 1. Click on the individual parts to edit in window 2 and at the same time window 3 shows all the values for the whole formula.
That would be perfect.
Ola Sandström
Ola,
For your item 2:
The method I used for parsing the formula is not intended for argument validation.
I didn’t take that path for two reasons :
1. In Excel’s formula bar you can use the mouse to select cells / ranges. Any attempt to replicate that function would be clunky and user unfriendly.
2. I would need to work out all the valid argument types for all functions for all versions of Excel (even COM Add-Ins, UDF, XLL, etc…)
So I settled on the “parse it blind” method – based heavily on the approach taken by Eric W. Bachtal.
On item 3:
F9 does work within the Formatter window. Select part of the formula, press F9 and the result appears in the lower textbox.
Cheers,
Rob
That’s a really nice piece of work. I have some issues with particular formatting decisions it makes, but formatting is a ‘religous’ war anyway.
mschaef,
The formatting options are Constants at the top of the modFormatter module.
Cheers,
Rob
Nice little add in!
a suggestion though … whenever i see conditionals nested that deep its probably time to consider “refactoring” the formula ie. using a different technique to get the same result. example using hlookup or vlookup … the shorter the formula the easier to understand and the lower the likelihood of an error.
cheers, Gary
Gary,
True…
Quite often I’ll be faced with maintaining a horribly long formula which I didn’t write.
I believe you’ve got to completely understand it’s logic before refactoring. Hopefully the tool assists at that stage.
Cheers,
Rob
Rob,
I just downloaded the program (Audxl 1.1) but can’t seem to find out how to find and use it!
Thanks.
There is no compatible version for XL97?
I run into an error upon loading the xla:
Copile error in hidden module: modFormulaParser.
Brgs Sige
Sige,
I dont own Office 97. I can’t write VBA compatible with that version.
When Excel formula is being calculated, can pre-saved excel be opened?
I am working with Excel UDF (written in C#, returns array result) which takes lot of time. When udf is running,I am not able to open any existing excel sheet.
I want to confirm, whether there is problem with my udf or excel behaviour is like this with all udfs (to maintain formula consistency)? Is this behaviour also persist with excel in-built formulas?
I realise I am a few years behind, but the link isn’t working to google sites to:
Download AudXL.zip from my website: http://www.vangelder.co.nz/excel
The only way I can get any of Rob’s stuff is through the Wayback Machine.
https://web.archive.org/web/20121221213101/http://vangelder.orconhosting.net.nz/excel/