Category Archives: 14768

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.