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
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.
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.
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.
If fzz ever does that, I’m buying.
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?
I just imported it into 6.4.1. Are you getting an error?
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
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.
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?
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?
I did finally get it installed.
Now for putting it into action.
Thanks.
Fred, how did you get this to work? Still doesn’t seem to function for me
@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++.
Not sure if anybody is still active on this thread, but I am also having some issues getting this working. I imported the language, and restarted Notepad++, created a new file, and selected the imported language. It doesn’t seem to do anything differently than when it was a normal text file, however; no auto completion or anything. Anybody know what I did wrong?
Also, @Justin I had your same issue, but figured out how to fix it. If you open up your preferences menu, and under the “Auto-Insert” category, check the box next to the single quote. That should fix it.
Does this not work with the latest version of Notepad ++? (6.8.2)?
I can’t seem to get it to add the folds…
There are multiple comments about people having difficulty installing the Excel .xml file from the http://docs.notepad-plus-plus.org/index.php?title=User_Defined_Language_Files location. I’m adding to the list. Unfortunately nobody seems to be stating what they did differently to get the highlighting for Excel formulas to work. So I’ll begin, and then can anyone finish what we are doing wrong? Seems to be across multiple versions of NP++. I’m on 6.8.3.
1. Install NP++
2. Click menu Language, Define Your Language, Import, find the unzipped XML file “xlf_udl.xml”, Open, and get “Import Successful” message popup. Great! But nothing new shows up in the Language menu indicating an Excel option.
What’s next to get this working?
Tim
Possibilities could be does the xml have to be placed somewhere specific? The page text on the languages page on docs.notepad-plus-plus.org said perhaps putting it in the users\AppData\Roaming\Notepad++, and also renaming the file to userDefineLang.xml, but I was unable to get either to work.
Not sure if it’s working as intended but it is working for me.
Steps after downloading zip and unpacking:
1. Go to Language->”Define your Language”
2. Click “Import”
3. Select the xlf_udl.xml file
4. If you get a message indicating success, you’re good. (otherwise, dunno what to tell you)
5. Go to Language->XLF (located under the “Define your language…” option)
6. The page you’re on is now using the XLF language
I was just looking for a language file for MDX, and came upon this page http://docs.notepad-plus-plus.org/index.php?title=User_Defined_Language_Files, where there is a list files. They have the Excel Formulas there, but bloody hell, I do the work and Dick gets the credit!
I was going to edit, it being a wiki, but it appears that it’s not editable by the general public. Not sure why it’s a wiki.
After following Anon C’s post, also restart the program and the entry “XLF” will appear.
Does it indent the code? If I paste code, it doesn’t format it, just a huge chunk of text
The FormulaDesk add-in adds this functionality directly into Excel. Just right-click the formula-bar when it’s in edit-mode, then click ‘Format formula’. See the bottom of this page: https://www.formuladesk.com/formula-tools
Formuladesk: Not just invaluable, but completely FREE too.
Jeez Gareth, I really must book you into a marketing course :-)
Hi,
Working with v7.4.2 (64bit).
I got Bob’s plugin to work. He provides an XML file that needs to go in the Notepad++\plugins\APIs folder. Then imported his UDL to the User Defined Language menu.
I can’t seem to get your imported UDL to do the folding you show in your post.
I can’t either. I revisit this every time someone comments, but I don’t what the hell happened to break it. I think it has something to do with “bad braces”. It thinks the open paren in
=IF(
is a bad brace and that somehow trumps it being recognized as a folding keyword.Here’s what I did to fix:
1) Follow normal steps to install.
2) Once installed, go to “define your language” under the language tab.
3)Choose user language ‘XLF’.
4) Under the Folder and Default tab, right click and select all under “Open” under “Folding in code 1 style”, then right click and copy
5) Paste into new Notepad++ page.
6)select all code then press CTRL-J to join the lines of code.
7)Copy and paste this code back where you originally copied it from.
This is useless, does nothing….
JJ’s steps about copying the opening function names and joining them all to a single line did the trick for me–was utterly baffled till I saw that.
I think they are manual and I love this idea
Thanks for this helpful language plugin. Just because I was struggeling with it I want to comment for others:
This seems to works just on English installations.
Excel is so stupid because it uses formula names different in different languages. E.g. COUNT in the German installation is called ZÄHLEN and the delimiter in English is a comma where in German we have to use a semicolon. So I think this is not covered by the plugin and so it doesn’t work for me here.
Regards
Marco
@marco Please forgive me – I don’t like self-promotion, but FormulaDesk FormulaSpy handles non-English formula names and any delimiter (comma, semi-colon) etc. as well, and might be your only solution. It also has a built-in formula editor.