Formulas Made Easy




I have been playing with PowerPivot recently, and one of the sites I have visited is Rob Collie’s PowerPivotPro blog, http://powerpivotpro.com/. One particular post that caught my eye was a guest post by Colin Banfield, on building complex DAX formulas in NotePad++. As Colin noted, the PowerPivot formula editor is severely limited, a better option is required when working with more complex DAX formulae. As Colin suggested, NotePad++ can provide this better option; with code folding, keyword highlighting, and Intellisense. In his post, Colin showed how to extend NotePad++ to provide this functionality for DAX formulas.


Being primarily an Excel guy, I couldn’t help but empathise, I feel exactly the same about creating complex Excel formulas. Sure, it is possible to break formulas over several lines by inserting Alt-Enter into the string, and Excel 2007 and 2010 has improved matters somewhat with the resizable formula bar, but these are tweaks around the edges, the core problem remains (the resizable formula bar is a good idea in concept, but in practice it takes up far too much real-estate, which when added to the space gobbler that is the ribbon, makes it far from ideal).


So, inspired by Colin’s post, I set about to extend NotePad++ to support Excel formulas. Believe me, with the huge number of functions, creating tooltips and argument lists, this was no trivial task. I was amazed at the number of functions I hadn’t come across before, besides those that I never use.


Assuming you have NotePad++ to hand, this is how to extend NotePad++ for Excel. This duplicates much of what Colin has written, but it is worthwhile repeating for clarity.


Notepad++ saves language definitions in an XML User Defined Language file (UDL), you can start the XML User Defined Language editor from the menu (View -> User Define Dialog…), or from the toolbar button.


The first thing to do is to give your UDL an identifier, I call mine XLF here, and to specify the extension of any files to use this UDL, again mine is xlf.


The first tab allows setting up the folder options. An Excel formula has no obvious fold points, so I have invented two keywords, BBEGIN and BEND, that  I embed in the formula simply to provide the fold points. Clearly, these need to be removed before copy-pasting the formula back to Excel. Figure 1 shows my folder tab settings.



Figure 1.


The second tab is used to define the keywords, and the highlight colour. Figure 2 shows my Keyword settings. You can see that even though I have used all four keyword sets, they have the same attributes. This is because I used an Excel spreadsheet to generate this data (surprise!), and I grouped them in standard functions, 2007 new functions, 2010 new functions, and cube functions.  



Figure 2.


Next, we have in Figure 3 the tab for defining comments. Unfortunately, an Excel formula doesn’t support comments, so I use this just to add some preliminary comments if I want to save the formula in a file.



Figure 3.


Finally, figure 4 shows the operator settings.



Figure 4.


So far we have defined the UDL for our particular edit type, and when XLF is selected from the Language menu, any input will be formatted according to these settings. Figure 5. Shows a ‘typical’ Excel formula created in Notepad++.



Figure 5.


Where it gets really good is by adding intellisense, description and argument tooltips. To do this, you have to create an XML file with the same name as your chosen language id, that is XLF.xml in this case. This file is stored in the NotePad++ file directory, in Notepad++\plugins\APIs folder. The format of a typical function is shown in Figure 6.


<KeyWord name="AVERAGEIFS" func="yes">
  <Overload retVal="number" descr="Returns the average (arithmetic mean) for the cells specified by a given set of conditions or criteria. Excel 2007 and later">
    <Param name="average_range"/>
    <Param name="criteria_range1"/>
    <Param name="criteria1"/>
    <Param name="[criteria_range2]"/>
    <Param name="[criteria2]"/>
    <Param name="…"/>
  </Overload>
</KeyWord> 

Figure 6.


After creating all of the xml code (auto-generated in Excel for me), and building the XLF.XML file, when inserting a function in NotePad++ you get the following assistance, as shown in figures 7 and 8.


 



Figure 7.



Figure 8.


Update


Notepad++ has to be enabled to allow auto-complete and function tips. To do this, go to menu Settings>Preferences>Backup/Auto-Completion and check the Enable auto-completion and Function Parameters hint boxes, as shown in Figure 9. 



Figure 9.


To save you the chore of recreating all of this data, I have provided a zip file containing the XLF.xml file, and the exported UDL data. The XML file has to be stored in the Notepad++\plugins\APIs folder, and the UDL file can be imported within the UDL.

16 thoughts on “Formulas Made Easy”

  1. I can’t get the box in Figure 7 to come up. I put XLF.xml in plugins/API and imported XLF UDL.xml. I’m getting the syntax highlight, but no box or intellisense. Did I miss something?

    When you’re done editing your formula, how do you get it back in Excel? Remove BBEGIN and BEND, copy and paste into a cell? Leave the linebreaks? Just curious how you use it.

  2. Mr K,

    I forgot to mention that you have to select XLF from the Language menu option before using this technique.

    I haven’t used this technique much yet, it only warrants that much effort for a very complex formula, but when I use this, I tend to save it as a text file (for further re-use), and paste it into Excel with the breaks.

  3. I had selected the language (bit of a pain not being able to set a default). Now I’ve totally munged things up messing with the folding options. So the fact that I don’t get intellisense now could be related to that. Anyway, check DDoE next week for the results of my experience.

    PS I can see this being a great time saver even on moderately big formulas. I’ve never used NP++, but now I have a compelling reason. Thanks.

  4. Good point Dennis. You could edit the UDL directly in Notepad++ via the dialog, or even open the Notepad++ UDL file and my additions UDL file in a text editor and cut and paste the changes (which is how Colin suggests), but the import option makes it so much simpler :)

  5. Hello,
    I discover your plug-in, and it(s very useful
    But, there’s a always a but, I use a French version of excel, and there’s few things to change.
    So I start adding the french function to your keyword list.
    There’re defenses in French and English functions, we use semi-colon (in France) and you use comma as a separator.
    We use comma as number separation and you use point.
    And so on.
    SO I’m going to do a French version for my purpose,
    Do I need to translate “XLF.xml”, I’m kind of lazy…
    Are you interested in, when my translated version will be over, to have it?
    This way you could to post it on your website?
    Why did you ever send it to notepad designer to add it as an included function?

    How to you obtain the function list directly from excel, it will save me some time, rather than typing one by one… I use excel 2003

    Best regards

  6. I’ve tried to install this with 6.2 and the User Defined Language Import results in nothing being imported.

    Is it possible for someone to post an exported version that would work with 6.2?

    Or I may be missing something altogether.

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>