Bob wrote a blog post last week, Formulas Made Easy, that I read with great interest. He uses a custom language in Notepad++ to allow for indenting formulas, editing formulas, and all the other stuff you can do in a text editor that you can’t do in Excel’s formula bar. I downloaded Notepad++ and Bob’s xml files and gave it a try.
The only thing I don’t like about it is the folding option. Folding is collapsing certain portions of the code so the rest is easier to read. I started by trying to make all of the function names Folder Open Keywords and a close parenthesis as the Folder Close Keyword. That worked OK, but didn’t work well with FALSE as FALSE is both a potential argument and the name of a function.
Then I thought that simply having an open parenthesis as the only Folder Open Keyword would be even better. However, it seems to require a space before the open parenthesis or to have it start a line to work. That still may be a better option, although it would require that I consistently line break before the open paren rather than after. Not the worst thing in the world, but not would I would do intuitively.
I ended up appending an open paren to the function names and using that as Folder Open Keywords. So ABS would be ABS(. I like the folding option this provides and it eliminates the problem with FALSE being interpreted as a function name when it’s used as an argument. Here’s how it ended up.
Pretty cool, I think. Happily, the intellisense portion works with Folder Open Keywords just like for Keywords. It remains to be seen if I’ll use it as a formula editor. But I think the next time I start counting parentheses I’ll pull this up and save myself some trouble. I pasted the above formula back in Excel and it retains the line breaks.
If you like this folding option, you can import my UDL xml file instead of Bob’s. By the way, thanks to Bob for doing all the hard work on this. I don’t know if I would have had the patience to put all those function names in there.
You can download xlf_udl.zip