Autogen’ed Ribbon Code







In a previous post, XML Is Such A Pain, I showed the technique that I use to
simplify the creation of XML code for custom ribbons.

This is a handy technique, as it means that creating the XML
is very simple, it is more or less a cut-and-paste job, and I spend very little
time thereafter  with the XML, which I
like as the tools for XML in Excel 2007 are not great.

As much as I like this technique, it still requires a lot of
VBA code. Whilst VBA is a great deal easier as a development tool than anything
we have with XML, there are many areas where errors can creep in, both
compilation and runtime errors.

In the old world of commandbars, I used a table driven
method of generating the commandbars. It is possible to use a similar technique
with Excel 2007, many such have been demonstrated.

I prefer to generate the code, from a similar setup table. This
way I can generate all of the code components; it only needs to be run when
anything changes; and I can view the code that is generated. I like code

In the technique that I will demonstrate here, I generate
all of the required code from the setup table

  • the
    ribbon XML
  • the
    VBA callback procedures
  • VBA
    constants used in the callback procedures.

In addition, the VBA code is automatically inserted into the
target workbook. As of yet, the XML is not automatically added as the workbook
is open at the time, but it should be possible to extend the technique to write
the XML file.

As in the previous post, all of the code shown here relates
to the following custom ribbon tab.

In that earlier post, you can view all of the XML and VBA
code, so it will not be repeated here, but rather this post will cover the
generation of that code. The autogeneration code here only caters for button
controls, but it can easily be extended for other controls such as menus,
toggles, and so on.

Autogen Setup Table

The table used to drive the autogeneration is structured as

  • Type
    – a simple Data Validation list of tab, group, separator, or button
  • Tag
    – an id for the control that is used to generate unique control ids
  • Tag
    Upper – an upper case copy of Tag
  • Group
    – the Tag Upper value of the current group
  • Separator
    – the Tag Upper of the previous separator within this group (if
  • Group/Sep/Tag
    – concatenation of Group & Separator & Tag Upper


  • Control
    Id – a derived id for this control


  • Caption
    – a value to assign as the control label
  • Procedure
    – the OnAction procedure name
  • Image
    File – the idMso of the required image
  • Image
    Size – a simple Data Validation list of small or large
  • Screentip
    – a value to assign as the control screentip
  • Supertip
    – a value to assign as the control supertip
  • Keytip
    – a value to assign as the control Keyttip

The example shown above consists of

  • a
    tab control
  • a
    group control
  • two
    button controls within that group
  • a
    second group control
  • a
    third button control within the second group

The table for this example is shown below. I have transposed
it (columns to rows) in order to show it on this page, but that aside, you
should get the principle.

Autogen Overview

The autogeneration works in the following way:

  • read the setup table and create a staging array
    of details
  • initialise the constants array, setting up on oversized array
  • initialise the output XML array with the
    namespace etc.
  • initialise the VBA code output arrays with
    function signature (a public ribbon variable and an onLoad callback
  • read the staging array and create items for each
    line of XML code
  • create output arrays for
    • procedure VBA code lines
    • label VBA code lines
    • image VBA code lines
    • image size VBA code lines
    • screentip VBA code lines
    • supertip VBA code lines
    • keytip VBA code lines
  • create constants arrays for
    • procedure ids
    • label VBA ids
    • image ids
    • image size ids
    • screentip ids
    • supertip ids
    • keytip ids
  • finalise the output XML array with the closing tags, and write the array to a text file (this is an area to be improved)
  • finalise the global constants output arrays by resizing the arrays to the appropriate size and write the arrays to a code module
  • finalise the VBA code output arrays by adding closing function statements, resizing the arrays, and write the arrays to a code module
  • write a procedure to the code module to invalidate the ribbon

In its present release, the XML code is written to a text
file. It needs to be copied to the custom UI file, either by unzipping the
file, then opening the customUI.xml file and replacing the XML within there, or by
using the CustomUI editor to replace the XML.

There is nothing particularly illuminating in this code, it
is a very straight-forward loop, depending entirely upon the setup table being
correctly formatted. It should be noted that some control types do not create
all of the array data, for instance a separator does not create constants or
VBA code, tabs do not create image code and so on.


As this technique writes VBA code on the fly, trusted access
to VBA has to be granted, but this is only required in the development
environment, if a workbook that this technique is applied to is given to a
colleague, that colleague does not to grant trusted access.

This was a relatively simple project to build, there were
not many problems encountered. The oddest involved the image size. In the XML,
the image size is normal or large, so you would naturally assume that the value
to be assigned in the callback procedure was normal or large. My original
attempt at the code assigned these values, but the images always showed as
normal size, I couldn’t get large images. Finally, I contacted Ken Puls a co-author on the RibbonX
book, which was and is my main reference in working with the Ribbon. Ken’s book
seemed to suggest that normal and large was the correct values to use in the
callback procedures, but he also found that this wasn’t working. Luckily he
found the values to use, which are 0 and 1. Ken has blogged this bug here.

Code Writing Code

The code that writes the VBA code on the fly is given below.
There is not much code as there is no decision logic required in this code, the
setup code creates complete output lines, apart from a test as to whether the
code is to be written new or appended.

Option Explicit
Option Private Module

Public Enum ModuleTypes
vbext_ct_StdModule = 1
vbext_ct_ClassModule = 2
vbext_ct_MSForm = 3
End Enum

Public Function WriteCode( _
ByVal CodeArray As Variant, _
ByVal Module As String, _
Optional ByVal Append As Boolean)
Dim mpModule As Object
Dim mpInsertLine As Long
Dim i As Long

On Error Resume Next
Set mpModule = ActiveWorkbook.VBProject.VBComponents(Module)
On Error GoTo 0

If Not Append Then

If Not mpModule Is Nothing Then ActiveWorkbook.VBProject.VBComponents.Remove mpModule
Set mpModule = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
mpModule.Name = Module

mpModule.CodeModule.InsertLines 1, _
"'== Generated Cube Formula Module " & _
Format(Date, "dd mmm yyyy") & " " & Format(Time(), "hh:mm:ss") & _
" By " & Environ("UserName") & _
" on " & Environ("ComputerName") & vbCrLf & _
"'== " & vbCrLf & _
" "
End If

With mpModule.CodeModule

mpInsertLine = .CountOfLines + 2
For i = LBound(CodeArray) To UBound(CodeArray)

.InsertLines mpInsertLine, CodeArray(i)
mpInsertLine = mpInsertLine + 1
Next i
End With

End Function

Example File

For RSS subscribers, you can download a zip file containing
the Autogen addin, and the example file shown above.


XML Is Such A Pain






Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve.  I will not go into the
details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at

The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this

requires the following XML code

<customUI xmlns="" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

<tab id="tabDemo" getLabel="Ribbon Demo" insertAfterMso="TabHome">

<group id="grpDemoGroup1" getLabel="Formatting Group">

<button id="btnDemoButton1"
screentip="Button 1"
superTip="Format activecell as currency"
onAction="procButton1" />

<button id="btnDemoButton2"
screentip="Button 2"
supertip="Format activecell as percent"
onAction="procButton2" />

<group id="Text Group" getLabel="rxDemoGetLabel">

<button id="btnDemoButton3"
label="Text Wrap"
getScreentip="Button 3"
getSupertip="Format activecell with text wrap"
onAction=" procButton " />

I have used all of the attributes in this example for the buttons, as I prefer to be explicit, not leave to defaults. 

So far, this piece of XML is not too bad. But I find the maintenance of this XML extremely tedious, adding more controls and/or changing any of the attributes of these controls can be long winded and extremely error prone. The tools for creating and maintaining the XML are very limited, the best is the CustomUI Editor, but this suffers from having no recent used file list, and has an irritating habit of moving the changed line to the bottom of the window (whoever thought that was a good idea?). Maintaining the XML involves switching between the CustomUI Editor and Excel 2007, and you have to close down the Excel file in between. 

Because of this, I have adopted the practice of not putting any of the values within the XML, but rather I use callback code to set the values. As an example, instead a line such as 

label="Text Wrap"

I would setup the XML as follows 


and within my Excel file I would have a procedure that loads that label, such as

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)

Select Case control.Id
Case "tabDemo": returnedVal = "Ribbon Demo"
End Select
End Function

I do this for all labels, and the other attributes Image, ImageSize and so on (onAction has to be a callback of course).
Using this technique, the XML code now looks like

<customUI xmlns="" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

<tab id="tabDemo" getLabel="rxDemoGetLabel" insertAfterMso="TabHome">

<group id="grpDemoGroup1" getLabel=" rxDemoGetLabel ">

<button id="btnDemoButton1"
onAction="rxDemoGetAction" />

<button id="btnDemoButton2"
onAction="rxDemoGetAction" />

<group id="grpDemoGroup2" getLabel="rxDemoGetLabel">

<button id="btnDemoButton3"
onAction="rxDemoGetAction" />

This means that I need to change the XML code far less frequently, if I wish to change a label it is all done in code, and so on. Adding a new button or group requires adding to the XML, but the additional code would be very similar to the existing code, just the control type and its id need specific values, so the maintenance is very simple.

This does mean that we need code to set the attribute values. In the rxDemoGetLabel example above, the tab’s label is assigned. As we have more controls with more labels assigned, we need more code to manage them, but this is simple extra case statements. For example

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = "Ribbon Demo"
Case "grpDemoGroup1": returnedVal = "Formatting Group"
Case "btnDemoButton1": returnedVal = "Currency"
Case "btnDemoButton2": returnedVal = "Percentage"
Case "grpDemoGroup2": returnedVal = "Text Group"
Case "btnDemoButton3": returnedVal = "Text Wrap"
End Select
End Function

As we can see, the code is very simple, very maintainable. The other callbacks would be very similar.

One other thing that I do is to create a set of global constants for all of the values, again to make maintenance simpler. For the labels I would have the following set of constants


Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

And the callback code
would be

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = LABEL_DEMO
Case "grpDemoGroup1": returnedVal = LABEL_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = LABEL_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = LABEL_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = LABEL_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = LABEL_GROUP2_BUTTON3
End Select
End Function

I find this a lot
easier to setup, and much simpler to maintain. It does mean that I have to invalidate the ribbon so as to refresh the attributes, but this is done
on opening the file, and should not be noticeable. For info, this is the code I add to invalidate the ribbon

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
Set mgrxDemoIRibbonUI = ribbon
Call RibbonSetup
End Function

Public Function RibbonSetup()
End Function

The full code for the constants is shown below

Option Explicit

Global Const PROC_GROUP1_BUTTON1 As String = "procButton1"
Global Const PROC_GROUP1_BUTTON2 As String = "procButton2"
Global Const PROC_GROUP2_BUTTON3 As String = "procButton3"

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

Global Const IMAGE_GROUP1_GROUP1 As String = ""
Global Const IMAGE_GROUP1_BUTTON1 As String = "DollarSign"
Global Const IMAGE_GROUP1_BUTTON2 As String = "PercentSign"
Global Const IMAGE_GROUP2_GROUP2 As String = ""
Global Const IMAGE_GROUP2_BUTTON3 As String = "WrapText"

Global Const IMAGESIZE_GROUP1_GROUP1 As String = "0"
Global Const IMAGESIZE_GROUP1_BUTTON1 As String = "1"
Global Const IMAGESIZE_GROUP1_BUTTON2 As String = "1"
Global Const IMAGESIZE_GROUP2_GROUP2 As String = "0"
Global Const IMAGESIZE_GROUP2_BUTTON3 As String = "1"

Global Const SCREENTIP_GROUP1_GROUP1 As String = ""
Global Const SCREENTIP_GROUP1_BUTTON1 As String = "Button1 "
Global Const SCREENTIP_GROUP1_BUTTON2 As String = "Button 2"
Global Const SCREENTIP_GROUP2_GROUP2 As String = ""
Global Const SCREENTIP_GROUP2_BUTTON3 As String = "Button 3"

Global Const SUPERTIP_GROUP1_GROUP1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON2 As String = ""
Global Const SUPERTIP_GROUP2_GROUP2 As String = ""
Global Const SUPERTIP_GROUP2_BUTTON3 As String = ""

Global Const KEYTIP_GROUP1_GROUP1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON2 As String = ""
Global Const KEYTIP_GROUP2_GROUP2 As String = ""
Global Const KEYTIP_GROUP2_BUTTON3 As String = ""

and for the callback code

Option Explicit
Option Private Module

' Publicly exposed callbacks
' No values are set in the XML apart from ids, all labels, images,
' tooltips and actions are assigned in the callback code

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
Set mgrxDemoIRibbonUI = ribbon
Call RibbonSetup
End Function

Public Function rxDemoGetAction(control As IRibbonControl)
Select Case control.Id
Case "btnDemoButton1": Application.Run PROC_GROUP1_BUTTON1
Case "btnDemoButton2": Application.Run PROC_GROUP1_BUTTON2
Case "btnDemoButton3": Application.Run PROC_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = LABEL_DEMO
Case "grpDemoGroup1": returnedVal = LABEL_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = LABEL_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = LABEL_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = LABEL_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = LABEL_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetImage(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "btnDemoButton1": returnedVal = IMAGE_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = IMAGE_GROUP1_BUTTON2
Case "btnDemoButton3": returnedVal = IMAGE_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetImageSize(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "btnDemoButton1": returnedVal = IMAGESIZE_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = IMAGESIZE_GROUP1_BUTTON2
Case "btnDemoButton3": returnedVal = IMAGESIZE_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetScreentip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = SCREENTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = SCREENTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = SCREENTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = SCREENTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = SCREENTIP_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetSupertip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = SUPERTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = SUPERTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = SUPERTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = SUPERTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = SUPERTIP_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetKeytip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = KEYTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = KEYTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = KEYTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = KEYTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = KEYTIP_GROUP2_BUTTON3
End Select
End Function

Public Function RibbonSetup()
End Function

I think you can see this is a simpler method than holding the attribute values in the XML, all of the working code is in one place, and it makes for easier setup and maintenance.

This overall approach requires the setting of all of the values for the global constants and the code. In a future post I will describe how I have automated this.




The End Of VBA

The title was just to get your attention, it really should be entitled something along the lines of “What Made VBA The Success It Is Today?’, as this post is more about what VBA is and isn’t, albeit somewhat precipitated by the thought that VBA must be close to its end game.

I recently saw a thread in a forum where one of the responses was along the lines that “VBA was not designed for programmers – i.e. MS dumbed down requirements …”. I found this to be a very facile argument and said so in my response. I won’t bother retracing that thread here, but I thought it raises an interesting question.

When MicroSoft introduced VBA into Office, just what was their objective, how did they envisage its use, and what concessions did they make in their implementation? BTW, although it was introduced into Office, I will concentrate solely on Excel, for obvious reasons.

Although Excel already had a macro language, these were not the easiest to use, and so, for whatever reason that it was done, introducing VBA into Excel 97 was a masterstroke, it opened all sorts of possibilities to Excel users. Like all projects, I am sure that MS’ implementation of VBA had some key must-do objectives, some secondary should do objectives, and some nice to haves; some of those objectives would be customer focussed (such as giving users a simpler development tool), some would be MS focussed (for example, as it was a superset of VB, they would probably want to keep it as closely aligned to VB as is possible, controlling development and maintenance costs). And like all projects, the timeframes and the budgets would be balanced, large enough to suggest that a great product was achievable within the projects  goals, but not so large that everything was easy and achievable – that is just the way of the world.

I have no inside knowledge, but I would think that when MS decided to give the world VBA, they were not looking at IT shops, they were specifically looking to give real users extra capability. They probably anticipated macros that formatted worksheets, shunted data around a sheet/book, all of the standard, simple one-off macro solutions. I very much doubt that they anticipated quite how its usage would take-off, how Excel especially has morphed into a development platform capable of running highly complex applications.

The sheer creativity and inventiveness displayed in VBA is staggering. In Excel alone, there have been some amazing development, from the simple to the hugely complex. We have seen  a whole gamut of solutions, for example

  • simple, but highly effective, tools that make using Excel functions simpler, such as Rob Bovey’s Chart Labeller
  • tools that extend Excel’s native functionality and help spreadsheet users to use Excel more efficiently, such as Jan Karel Pieterse’s NameManager
  • true developer tools for spreadsheet users that help better spreadsheet development, such as Charles Williams FastExcel
  • some incredible tools that do things previously thought not possible, many such examples but a classic is Stephen Bullen’s PastePicture
  • giving us back functions that we used to have, such as Andy Pope’s Pattern Filler utility in Excel 2007
  • database applications where Excel is providing a presentation and data management layer, and a database, Access or SQL Server, maintains the data and provides the richness of a relational database
  • ETL applications where Excel harvests data from disparate sources to provide a composite picture, using tables, charts, dashboards etc.
  • end-to-end data capture, data management, and reporting applications using multiple technologies and multiple layers.

What is even more impressive is that these solution have often been produced by one man outfits, no big IT departments to support the developer here, and often by developers that are mainly business users, people who would never claim to be coders (one of the best Sparklines implementations I have seen was by a guy who made no claim to being a coder, indeed he said that he built this implementation to help develop his VBA skills).

Did MS envisage all of this when they were looking to add VBA? Maybe, but somehow I doubt it. As I said previously, users building macros to automate a few tasks, formatting, capturing data, and so on, is a far more realistically envisaged deployment, presumably they felt that just bringing VB into the Office apps would be a lot simpler for users to get to grips with. If they had envisaged all of this use, I would have hoped that they would have been far more ambitious in their objectives, but I don’t believe this so it is a sterile argument for me.

As I mentioned above, VBA would have been a project, and as such, it would have been subject to typical project constraints. This must have meant that, throughout the course of the project, implementation decisions would have had to be made. Some of these decisions probably resulted in cutting or constraining some of the functionality that was originally envisaged. I am sure this would have been done for all of the right reasons, and that the VBA developers and project leaders would have justified it in their minds, always taking into consideration what they knew or thought at that time. Sitting here in 2009, we might argue that some of those constraints were ill-advised, but that is hindsight, and it is certainly worlds away from saying that MS dumbed down VBA.

Sure there are some things that are better in VB than they are in VB6.

  • forms are better in VB, you have a built-in menu facility, and control arrays, but both of these can be emulated in VBA, with varying degrees of success and completeness
  • you can create stand-alone executables in VB6, but not in VBA – this is true but when VBA was introduced I am sure this was not even a consideration, without being able to foresee the types of addins deployed today and the security issues that have manifested, it seems impossible to think back then that this was necessary or even desirable
  • packaging and deploying – again, VBA is by default packaged within its host container application, targeted at individual users, how could MS have envisaged this need, and anyway, there are many good third party tools that provide this capability

Some things are simpler in VBA, working with Excel for example in Excel, by virtue of its implementation, just as some things are harder by virtue of its implementation.

Acknowledging some things are easier in VB is a completely different matter from saying that MS dumbed down VBA. I no more believe that MS deliberately dumbed down VBA than I believe that many of the techniques that have been developed over the years to make Excel such a versatile development platform were deliberately introduced by MS, it is just consequences of the original implementation.

To me, this is a relevant topic today as we stand on the brink of moving, either willingly or not so willingly, to the .Net world. It originally struck me that MS were not prepared to make any concessions to the world of VBA and the millions of users that have built solutions using VBA. My impression was that they were trying to dig their heels in and say that it is .Net in the future or nothing. I believe that attitude has changed somewhat, not enough yet, but at least they concede that these users have to be accommodated or else they just will not, or cannot, upgrade. The interesting thing will be to look back in 5 years time and reflect on what we think that MS have and have not done well in introducing .Net into Office. I for one feel that we are on the brink of a much better experience with .Net, and am cautiously looking forward to it.

In the meantime, let us all celebrate VBA, the way it has been stretched, bent and moulded over the years by the thousands of real users who just wanted to be able to do something, and do it today; by talented Excel developers like those mentioned above, and by all of the ‘novice users’ who have dared to try and do something in Excel and VBA that helped them to work faster, less, and/or better. Of course, there have been some horror stories along the way, I have seen a few myself, but there have been many successes. Excel has not become the de-facto application development platform that it is today (what other desktop application could have a book like PED written about it?) because MS dumbed down VBA, but because Excel has an incredible function set, and VBA has exposed that function set to all manner of users, and it is they that have shown the world just what is possible.

Keeping It Clear

It is not my intention for EDDD to be another ‘Excel Tips & Tricks’ blog, there are plenty of good examples of these already, but I saw someone struggling in a demonstration recently so I am thinking that this technique is not as well know as I had presumed, nor as it should be. Therefore I shall share it with you all.

Firstly, I hope we all agree that the Pivot table Field List in Excel 2007 is a big improvement over earlier versions. However, sometimes the cost of a nice facility is that comes at the cost of a lot of real estate, the field list is quite big. And it is rooted to the right of the Excel window. Or is it?

Actually, you can tear it off, move it and resize it.

Move the cursor towards the top of the field list until the cursor changes to  a cross with arrows at all points

At this point, you can tear off the field list using the mouse left button, and drag it off to wherever you want it on the screen, and resize it to whatever size is good for you.

This is so much easier than scrolling the data and pivots around so that the field list does not obscure the point being made.

To restore it to its original position, just double-click in the title bar.

Server Stage 1 Installed

In a previous post, I mentioned that I had just got a new server, and what I intended to do with it.

Progress has been slow. It had been my intention to load Windows 2008 on the machine, but I didn’t have an ISO image on disc and so I had to download it from MS. Unfortunately, that was far from simple. The download kept crashing saying that it could not read the file (after about 500Mb). I eventually managed a successful download last Saturday (it took hours).

That was not the end of my problems, far from it. The ISO image that I downloaded was not be recognised by the server installer, neither as a straight dump of the ISO file onto a DVD,  nor as a DVD burnt as an image. I had used Active@ ISO Burner to burn it, and it all seemed to go okay. As an alternative, I tried to burn it usng ISO recorder V2, but that kept telling me that my (blank) DVDs were unusable!

Finally, I decided to ditch Windows 2008, and try Windows 2003, which I do have an ISO file on DVD for. Restarted the server installer, selected Windows 2003 Enterprise R2 x64 this time, and held my breath when it asked for the media. It asked for 2 image files which took me aback, but the DVD had a Disc1 and a Disc2, so all was okay at this point. Pointed it these two file and it started installing. Phew!

The installation was relatively quick, but of course Windows 2003 is a mature product (read old), so there were a host of critical updates to apply, including SP2. That alone took hours to download, but when it tried to install it just got stuck after the installation intialisation.

I decided to cancel the SP2 install, and install the other 48 (sic!) critical updates. Rebooting on completion of these, I tried again with SP2 and it flew through. Why?

I hate these darn computers, no logic in them at all.

At least I have an OS on the server now;, tomorrow we will try with SQL Server.

I was right though, it has taken more time than I can spare, and we are just at the start [^o)]

Custom UDFs







In Excel 2007, if you start to enter a formula, such as =I, in
the formula bar, it immediately provides you with intellisense of all functions
starting with I. As with normal intellisense, the more you type, the more
precise the list becomes. I am sure that we all agree that intellisense is a
wonderful feature and its addition to formulas in Excel 2007 is a welcome step.

I recently had a client who was building some UDFs, each of
which would start with a particular id. They rightly thought that it would be a
great idea to be able to type their id and the list of their UDFs would come up
in intellisense. Sound thinking, but unfortunately it doesn’t work, because
UDFs do not show up in the intellisense. Why? Only MS knows I am afraid, I
certainly don’t, but suspect it is because MS do not care about VBA anymore.

Anyway, I had read an article by Jan Karel Pieterse some
months previously about registering UDFs. The objective of registering the UDFs
in this article was to categorise the UDFs, and provide help in the function
wizard. It occurred to me that perhaps if the UDFs were registered in this way, with Excel ‘owning’ them,
they might show in intellisense. I revisited the article to check that my
recollection was correct, and a quick test of this with a few of our UDFs in
Excel 2007 showed my assumption to be correct, so we had the perfect solution.

The article in question can be found at Registering A User Defined Function With Excel.
The technique is not Jan Karel’s, it originates from Laurent Longre and KeepItCool,
but Jan Karel has provided a real service in documenting and publishing this
technique. The link also provides a download file for you to get started.

One thing to note about this technique is that if your UDFs
are public, they will show up in you defined category, but will also continue
to show up in the ‘User defined’ category. Believe it or not, this can be
overcome by making the UDFs private, they still function correctly, still show
in intellisense, but are not shown in the ‘User defined’ category. The article
does state this, but I mention it here because I missed it on first reading,
and it is so useful.

The example file allows for just 15 UDFs to be
registered, and has a small error in the example in that the dll procedure
CharPrevA is in the list twice. If you try to register a UDF on line 6, you
will get an error. We had a need for a lot more UDFs, and so we needed more dll
procedures. As this technique uses the Excel 4 macro language’s register
function to register a function residing within any system dll, using an alias
name which is identical to the name of a UDF, we needed to find more dll procedures
that we could add to the list. Another wonderful little utility from NirSoft, the
DLL Export Viewer, gave us a list of all of the exported functions in
user32.dll, so we were able to add to the list, and now have over 40 UDFs in
our own category, all showing in intellisense. Wonderful!

Here is a picture of the technique in action. First my setup

And this is the intellisense in action

The next step is to add more. There are two choices I
believe, the first is to find another system dll that we can utilise in this
manner, I admit to being wary of using functions such as GetFileAttributesW in
user32.dll, or write our own dll. I think the latter way is what I will be


Too many Modules Spoil the VBA Project?

So many times you see someone asking about whether they should split their VBA code into many modules, if so, how should they structure it?

We probably know that there is a limit to the amount of code that a VBA code module can handle. The code module is essentially an in-file text file (if exported it can be viewed with a simple text eitor), and can accomodate a size of approx 64k bytes. As such, the answer to that previous question should always be an emphatic yes. But it also should be yes from a code maintainability persepctive, even if it never approaches the 64k limit. The code should be organised into functionally discrete modules, for instance a module that contains all of the file handling procedures, another that contains all of the graphing procedures and so on.

For my projects, I tend to have a global module, which contains all of my global variables (although I try to keep these to a minimum), and all of my application constants, such as messages and enumerated lists. This way, it is simple to track the source of these variables, rather than scattering them amongst many modules.

I would normally have an application class, specific to the particular application, which would hold any procedures common to that application, such as a common validation procedure, and application variables, such as the AppId, AppName, AppVersion and so on. It could be argued, and I have gone this route in some projects myself, that many of those global variables are application specific and should be in the application class. I think I subscribe to this in theory, but don’t always practise it.

Of course, I have a common error handling procedure, a utilities procedure (which would hold my generic procedures such as SheetExists), and a history module, which is pure comments.

If I need to trap application events, I would have such a class. I tend to hand off the actual processing of these events to a helper module, the class module simply traps the event, decides whether it applies to this application in these circumstances, and then calls the procedure in another module. Theoretically, I am not happy with this from a design perspective, but I do find it makes the code a lot more manageable, and convince myself it is good from a maintainability perspective.

I also always have a scratch code module, where I have procedures to easily setup data, reset things, test specific aspects of the application, and so on.

Finally, I apply a strict naming convention, and add two underscores to a few modules to force them to the top of the list, and a single underscore to some others to forec them into the next batch. I find this categorisation also helps with maintenance.

Here is an example of one of my projects.

As you can see, it is a good size with over 8,000 lines of code, the 363 procedures are nicely scattered over the modules, no module having more than 30 procedures except AppClass (many of which would be property Let/Gets). Apart from AppClass, no module has more than 700 lines of code, and each procedure is circa 30 lines line. Of these code lines, nearly 1,500 are comment lines (circa 15%). I think this is a high comment rate for me, not typical, as I do not over-comment my code.  In fact, without the history module, the comments reduce to 12%. 10-12% I would think is my typical comments percentage.

The Glory of Pivots

Surely we all know how useful Pivot Tables are in Excel by now? They are great for knocking up a filterable, drillable list of the data, but they can be much more.

For instance, it is realtively simple to create a Top 10 (or top n) list from your data in a pivot table. To my mind, this sort of table should form the basis of all dashboards, far better than those pointless guages, pie charts and stacked bar charts (but we digress!).

Anyway, as I said, a top 10 list is simple to do. Here are the instructions for excel 2003.

We will start with a simple table of data like so

which we create a pivot table from, the result looking like this.

Then we double click on the item we want to rank (or we wcould right-click and select Field Settings), Company in this case, which presents the following dialog

From here we select the Advanced button, and get the following dialog


Here it is a simply a matter of choosing whether the data is ascending or descending, clicking the On option of Top 10 AUtoshow, and spinning through the number to be ranked for our choice.

The results of the top 10 would then look like this

Simple, quick and effective, what more can we ask? Of course, you need to refresh the pivot whenever the data changes, but that can be automated.

New Server

Big day today. Just taken delivery of a new server box, dual core with 8Gb and Raid-1. By the cringe, it was a heavy beastie.

I will be installing Windows 2008 and SQL Server 2008 on it, as I intend to do some more heavy duty BI (still using Excel to be the presentation layer of course). In due course, I will add Sharepoint, and do some Excel Services (but let’s walk first).

I am not really a hardware or OS guy, in the past I have always had team mates who did that sort of thing, so this will be a new experience. Sort of looking forward to out, but I am worried it will take up more time than I can afford at present.

Let’s see how it goes.

Why Does Excel Make It So Hard?

I had a simple requirement recently, I wanted to create a form where it was possible to navigate around using shortcut keys. Easy you would think, just assign accelerator keys to labels and/or commandbuttons … problem solved.

Unfortunately, I also have a number of frames with multiple checkboxes in them, groups of
checkboxes. And guess what, frames don’t support the accelerator property.

My first thought was to overlay the frame with a label and blank out the frame caption. As labels have an accelerator property, this should work, using a shortcut key would hotkey to the label and then you tab through the checkboxes within the frame. Unfortunately, I couldn’t blank out the frame caption because the top of the frame is taken from the top of the caption, when there isn’t one it gets shunted up 6 points, so the label has to be precisely positioned to avoid blurring. I
managed to get this approach to work as long as the overlaid label has a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forscing that control to take focus on hotkeying.

So, there you are, a bit of creative thought and the problem was solved, albeit a tad kludgy and inelegant.

Of course, life is never that simple, certainly not with computers and certainly not with Excel. I have since come across a problem
with this approach. These frames are on a single page of a
multipage control, and if it is switched to another page and then back again, some of the underlined characters are losing their underline.
Not all, just some.

Presumably the frame caption has come forward over the label caption, but how
and why? What seems to be happening is that the label doesn’t overlay the frame caption as I had hoped, but the top half gets hidden by the caption, the bottom half still shows. Thus, the underlined character that is the accelerator key does show, or at least it does on first showing. I still have no idea why some disappear when showing another page and returning.

This is a picture of part of the form, showing how it looks on startup

I was stuck, I couldn’t think of a solution, so at this point I decided to call on the help of friends and colleagues in the Excel community.

Two Excel MVPs offered their help. Peter Thornton suggested an approach that was intrinsically the same as mine, although he used a nice method of using a commandbutton to accept the actual accelerator key, and then redirect the focus to the first checkbox within that frame. This was a technique that I had employed on another page of the multipage to have a shortcut key to a listbox, so why I hadn’t thought of it here as well is a bit odd, but although I could not demonstrate on Peter’s example file that it could also lose the underlines, as it was essentially the same as my solution I felt that it would probably suffer the same fate.

When I first saw the other solution, I thought that it too was a similar approach, although I was at first confused as to how this approach was managing to get the label to properly overlay the frame caption, and how it had the frame caption blanked out without the effects mine had suffered in this way. Further investigation showed it was a lot cuter than this. Here are the essentials of what had been done:

– rather than using a frame to contain the checkboxes, there is a label, sized to encompass all of the original frame’s controls. This label’s SpecialEffect property was set to fmSpecialEffectEtched and the BackStyle property to fmBackStyleTransparent, to make it look like a frame. The label caption was blank

– a standard label was overlaid onto this label, this being the label for which the accelerator key was targettedr, and as before, this was given a TabIndex of 0,
and the first (real) control with a TabIndex of 1, forcing the first real control to take focus when the shortcut is used

– finally, both of these labels, and all of the encompassed controls were encapsulated in a frame, to keep the grouping aspect required, but this frame’s SpecialEffect property was set to fmSpecialEffectFlat, and its caption to blank, so that it didn’t show.

As can be seen from this image, the effect is exactly as required,

but because the target label is not half-buried at the top of a frame, it doesn’t suffer the loss of the underline when navigating through the pages. The invisible frame(s) can be positioned so that they overlap a little, in order to reduce the gap between them.

I have to thank Andy Pope for this really creative solution. Nice one Andy! And thanks again to Peter, an Excel MVP who is interested in the atypical Excel capability.

But I have to ask, why is it so difficult to do something so simple?


For anyone interested, an example file of this technique is attached in the RSS feed.