Archive for December, 2009

RibbonX Visual Designer

Over at Daily Dose of Excel, Juan Pablo Gonzalez posted (in a comment) a link to Andy Pope’s site, where he has a very slick looking tool for Ribbon extensions, the RibbonX Visual Designer. I plan to download it and give it a go: more anon.

Happy New Year, and thanks for visiting.

Excel Value Distribution

I recently wanted to take a long list of values and find the distribution of these. So if my data consists of integers between 1 and 9 (or could be converted into such), then I want to see the count of 1s, the count of 2s, …, the count of 9s. Obviously, this would lend itself to presentation as a column chart:

So how do we get the count values in column C? A quick skim through Walkenbach’s Formulas didn’t reveal the answer, though I might have missed it.

As you might guess, we can use a single-cell array formula. In C2, we want to handle the boolean array {B2=data}, where data is the name of the whole set of data values in column A.

Note, incidentally, that we can’t use named-range-intersection inside an array formula: {value=data}, where value names the column B values, does not work.

So we need to OR the boolean array {B2=data}. However, we now hit the problem that AND and OR always produce a single result value, even inside an array formula. The trick is to convert TRUE to 1 and FALSE to 0; then add for disjunction, multiply for conjunction. So the formula we have is:


(with the braces indicating array-formula entry – Ctrl-Shift-Enter). This then fills down column C. The type conversion is effected by: FALSE * 1 = 0, TRUE * 1 = 1.

A more general version would allow us to set a value interval for the counts:

The min and max are generated from the interval. In E2 (for example), the formula now has two comparisons:

{=SUM((data >=C2)*(data<=D2))}

Here, the pairwise multiplication of the two boolean arrays effects the type conversion. SUM then operates on a single array of 0s and 1s.

The need for this type conversion from booleans to integers is a bit nasty, and comes from the implementation of AND and OR, which flattens arrays into a single set of values. So:


returns a single FALSE value, not an array {TRUE;FALSE}.

It’s instructive to think about how you might do this in VBA: with a function that takes the data range and returns an array of count values. There are clearly two levels of iteration, the one over data, which is implicit in the array formula, and one over the values/intervals, which is represented by filling the formula down the column.

Office 2007 Ribbon – Part 3

In the previous part we started to implement a custom tab in the Excel ribbon:

We defined an XML configuration file for the tab and the Link group, which lives inside the xlsm archive. We also wrote VBA callback procedures for the buttons’ onAction events.

For the next stage, we want to be able to create, go to and delete ‘bookmarks’ within our workbook. Bookmarks are range names (maybe temporary) which are distinct from the range names used in formulas. We’ll make them distinct using a suitable prefix – say “_BM_”. Our combo box will display only these names (minus prefix), not the full set visible in the Name Box combo.

We can define a bookmark by selecting the target range, and clicking the Make Bookmark button. An input box has a default value of the first (or only) cell; this can be changed, but the name must not be already in use. The combo box then needs to be updated with the new name (minus prefix).

Selecting from the combo box goes to the bookmark range. We need to update the combo to clear the selected value (otherwise selecting the same value again does not raise an onChange event). The Delete Bookmark button does what it says; again we need to update the combo.

First the configuration. At the same level as the Links <group> element, we need to add:

<group id="BookmarkGroup" label="Bookmarks">
  <button id="BookmarkButton"
	label="Make Bookmark"
	onAction="Bookmark.MakeBookmark" />
  <comboBox id="BookmarksCombo"
  <button id="BookmarkDelButton"
	label="Delete Bookmark"
	onAction="Bookmark.DeleteBookmark" />

The buttons are as the previous examples. The combo box is rather more interesting, in that it is built by means of callbacks: GetItemCount, GetItemLabel, GetItemID, GetText. We need to provide VBA implementations for these:

Sub GetItemCount(control As IRibbonControl, ByRef count)
    count = UBound(bmarks)
End Sub

Sub GetItemLabel(control As IRibbonControl, index As Integer, _
           ByRef label)
    label = bmarks(index + 1)
End Sub

Sub GetItemID(control As IRibbonControl, index As Integer, _
           ByRef ID)
    ID = "bookmark" & index
End Sub

Sub GetText(control As IRibbonControl, ByRef text)
    text = ""   'visible value in combo when initialized
End Sub

The Item Labels are the visible items in the combo box drop-down. In our case, these come from an array of the Bookmark names bmarks, which is held as a module variable. The bmarks variable is set by a procedure which goes through the ActiveWorkbook Names, extracting the Bookmark ones.

The Label and ID procedures take an index parameter, which implies that they are called iteratively by the ribbon. Notice that these are subs (rather than functions), which pass back their result via a ByRef parameter.

Now we need to make the combo list update whenever an action has been performed. We do this by making a call to the ribbon (I suppose this is a ‘callforward’):

Sub ResetBookmarks()
    Bookmarks   'reset bmarks array
    ribbon.InvalidateControl ("BookmarksCombo")
End Sub

The argument is the control Id, as in the configuration. We can also invalidate the whole ribbon, should multiple controls need re-initializing.

How do we get the ribbon reference? Via a callback when the ribbon loads initially. The callback is specified on the root node of the configuration:


The RibbonLoad procedure gets a ribbon reference, assigns it to a module variable, and also sets the Bookmarks array:

Sub RibbonLoad(rb As IRibbonUI)
    Set ribbon = rb
End Sub

That’s about it. As I said earlier, the documentation – for example of the callbacks – isn’t great, so a certain amount of experimentation is needed.

Here’s the XML configuration file for the ribbon extensions; here’s the Definition module; and here’s the Bookmark module.

Office 2007 Ribbon – Part 2

Carrying on from the previous post

The first thing is to think of some example facilities that will be useful independently of a particular workbook structure. Some ‘smart’ navigation, perhaps:

  • Pick up the value of the currently selected cell, treat it as a range name, and go to that range. This could be useful where we have lots of data values that reference people, locations, parts, etc, and elsewhere in the workbook we have definitions or further information about those entities, with the definitions being named accordingly. Having looked up a definition, we then want to go back to the original reference.
  • Create, go to and delete ‘bookmarks’ within our workbook. Bookmarks could well be temporary, and should be distinct from the standard range names.
  • In both cases, we’d like some user interface other than the Name Box drop-down.

Here’s what it might look like:

This is a custom tab (called Navigation), with four buttons and a combo box. The extensions could have been added to a built-in tab, instead.

The key difference from earlier versions of Excel (Word, etc) is that the configuration for the ribbon extensions is specified in an XML file that lives within the document ‘file’ (.xlsm, in our case). This is really a zipped archive, which you can open; when I refer to ‘archive’, this is what I mean. The XML file is customUI\customUI.xml (create the folder initially). Here’s the configuration with the Definition controls:

<?xml version="1.0" encoding="UTF-8"?>
      <tab id="NavTab" label="Navigation">
        <group id="LinkGroup" label="Link">
          <button id="DefinitionButton"
            onAction="Definition.GoToName" />
          <button id="BackButton"
            onAction="Definition.GoBack" />

The hierarchical structure of ribbon, tabs, groups, controls is fairly clear.

The button images are standard MS ones, of which there are many. Ron de Bruin has pointers to information on these (see his Section 5).

Note the onAction attributes, which are callbacks to procedures in our VBA code, of the form Module.Procedure.

Names of attributes and built-in entities are case-sensitive, unlike VBA code – beware! Errors in the XML configuration result in the extensions not loading, with no error messages or other indication (unless I’m missing something).

In order to get the customUI extensions picked up by the application, you need to add a declaration in the archive file _rels\.rels:

<Relationship Id="customUIRelID"

Once added, this does not need to change.

Incidentally, I’m putting this in an xlsm document, but it would in practice go in an xlam (add-in).

We now need to put the callback procedures somewhere in our VBA project. The module doesn’t matter, but must be the one named in the ribbon configuration: Definition.

Dim source As Range 'module level

Sub GoToName(ctrl As IRibbonControl)

 On Error Resume Next 'ignore if name not defined

 Dim val As String
 Dim name As String

 Set source = Selection
 val = source.Value
 name = ActiveWorkbook.Names(val).name
 Application.Goto Reference:=name

End Sub

Sub GoBack(ctrl As IRibbonControl)
 On Error Resume Next
 Application.Goto Reference:=source
End Sub

Note the standard parameter to control callbacks, of type IRibbonControl. From this reference you can get the control Id (as in the configuration). Casing on this would allow you to use the same callback procedure for multiple controls. You can also get the Context, which in this case at least is the Window.

The alert reader (I love saying that) might have spotted a callback in the root <customUI> element in the configuration:


This allows our VBA to get and hold on to a reference to the ribbon itself. We’ll need this in order to change the contents of our combo-box dynamically (see next installment).

Now, if this all seems a bit of a performance, you might have a point. If you want a bit of help with the XML editing, and updating the document archive, there is a simple helper application, the Custom UI Editor. Personally, I’d rather use a proper XML editor, and just drag-and-drop files into the archive. Note that you can’t have the document open as both an archive and an Excel workbook at the same time – unsurprisingly.

I’ve found the Microsoft documentation on this a bit patchy – I haven’t found a proper reference manual. The main source seems to be a set of three articles on MSDN – search for “Customizing the 2007 Office Fluent Ribbon”.

In the next post we’ll add the Bookmarks controls, with a combo-box that we update dynamically.

Office 2007 Ribbon

I’ve not previously done much with CommandBars in 2000/2003, since my applications were tied to particular workbook structures, and thus used forms or on-worksheet controls. However, I think it’s time to have a look at the Ribbon extensibility in Office 2007.

Although the Ribbon has been criticized for taking up a lot of screen area, its size means that there is more scope for displaying useful information, rather than just holding toolbar buttons. So it might be possible to use a custom ribbon tab as a permanently visible form, with combo boxes and other controls. Obviously, any facilities still have to be applicable to a wide range of differently structured workbooks. I’m thinking of various ‘smart’ navigation facilities, as an example.

A significant difference with the 2007 ribbon is that the customizations are expressed in XML, and added to the workbook ‘file’. As is well known, an Office 2007 file is actually a zipped archive containing various folders and files. You can open it with WinZip (or 7-Zip, if you prefer a free tool).

It’s apparent from the Microsoft technical articles that the approach is not specific to VBA, but is also intended for heavier-weight usage by C#/.Net developers – hence the use of XML configuration. However, this means that while it’s quite easy to stick a few command buttons on a ribbon tab, things can get quite a lot more complicated… I’m still investigating, and will post again in a day or so.

December 2009
« Nov   Jan »