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 applicable)
- 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.
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 procedure)
- 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 Private Module
Public Enum ModuleTypes
vbext_ct_StdModule = 1
vbext_ct_ClassModule = 2
vbext_ct_MSForm = 3
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 & _
mpInsertLine = .CountOfLines + 2
For i = LBound(CodeArray) To UBound(CodeArray)
.InsertLines mpInsertLine, CodeArray(i)
mpInsertLine = mpInsertLine + 1
For RSS subscribers, you can download a zip file containing the Autogen addin, and the example file shown above.