Formula Editor in Notepad++

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

13 Comments

  1. dan l says:

    I just got this up and running myself. I think I’m going to try to see if the absurdly powerful find/replace feature or one of the addins will help me set it up so that the folder keywords will be stripped from the formula upon copy.

  2. Bob Phillips says:

    Dick,

    I like that approach, better than my invented folder keywords. I had tried using ( and came across the same problem as you, didn’t think of including the function names.

    I think you ought tp make the fold colour blue though, seeing function names in orange/amber just doesn’t look right :)

    If you get to use Notepad++ regularly, you should install the Explorer plugin. Just go to Plugins and install it there.

  3. fzz says:

    The problem with using an outside editor is the inability to use Point mode to select ranges.

    Myself, I use VBA to display formulas in text fields in dialogs, and I use regular expressions and dictionary objects to indent. More of a formula viewer than an editor, but it helps, and it doesn’t require any outside software (well, other than than references to the regex and WSH DLLs).

    If I get ambitious someday, I’d add a button to the dialog which would bring up another dialog containing a range reference field to select ranges in the workbook along with buttons to (1) simply insert the range reference, (2) replace the formula token where the cursor is, (3) replace that token and all other identical tokens, (4) cancel. Maybe another button, a (2.5) iterate through the identical tokens and prompt to replace each one. Or add a find and replace facility in the first dialog.

  4. dan l says:

    If fzz ever does that, I’m buying.

  5. Brian says:

    I love the idea of this, but I cannot get the language file to import into Notepad++ 6.3.2 Do you have any suggestions?

  6. Dick Kusleika says:

    I just imported it into 6.4.1. Are you getting an error?

  7. James Fox says:

    I just installed this last night (having found this and Bob Phillips’ post). It took some trial and error but it seems to be working. I get the popup box, autocomplete and syntax tips. I was wondering though, I see in your screen shots that there are tabbed indents under the functions. Are these supposed to occur automatically (they don’t for me) or have they been manually entered?

    Thanks for the nice work,

    Jim

  8. Dick Kusleika says:

    I think they are manual. I imported my xml into 6.4.1 but can’t seem to get anything to work, so I can’t say for sure if there was autoindenting, but I don’t think so.

  9. Justin says:

    Notepad++ doesn’t appear to close out the single quote properly.

    Something like : CELL(“ADDRESS”,’Master Pricing’!$A$1))-4
    Evaluates [‘Master Pricing’!$A$1))-4] as a string (shown as green)

    This prevents Notepad from recognizing the closing parenthesis and screws up the alignment. Any ideas how to fix?

  10. Fred Marshall says:

    I tried to import xlf_udl.xml into NP++ 6.6.7. Apparently I’m too much of a noob to get it done and working.
    It appears to Import OK but nothing shows up on the Languages list, etc.
    It also “sounds like” a plugin but that appears to require a .dll file which this isn’t.
    Any pointers?

  11. Fred Marshall says:

    I did finally get it installed.
    Now for putting it into action.
    Thanks.

  12. achillez says:

    Fred, how did you get this to work? Still doesn’t seem to function for me

  13. Jonas says:

    @achillez: Since I’ve been struggling, too:
    In NP++’s menu bar go to Language > Define your language… > and click the “Import…” button. That’s it. Might be you have to close and reopen NP++.

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: