# Cooking With CUBEs

Microsoft sneaked a couple of ne

As we should all know by now, PowerPivot provides Excel with a powerful way to harness data from one or more sources, and to do further analysis on that data within familiar pivot tables.

Furthermore, because PowerPivot is creating an in-memory cube of the data, it is possible to build an analysis using CUBE formulae. I have blogged a couple of times about CUBE formulae, in Cycling Through The Fog and in Cracking The Code.

In Excel, as with any development, you want your solution to be as flexible and dynamic as possible. This blog is about building dynamic tables using CUBE formulae, but to start with the following formula shows an example of a value extracted from a PowerPivot model using CUBE functions

=CUBEVALUE(“PowerPivot Data”,
CUBEMEMBER(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”),
CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryName].&[Bikes]”),
CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”))

Equation 1

This formula gets the Sales Amount from the PowerPivot cube for the Bikes product category, for the fiscal year 2006. There will be many values at this intersection, there can be many dates in 2006 and many products within that category, all pre-aggregated in the cube; the CUBEVALUE function returns that aggregate amount

We could build the whole table of values using similar formulae. In our table we need to know what the value is related to, so we have row and column headers that identify the intersection points. We could define those headers using the CUBEMEMBER functions giving a table such as shown in Figure 1 below, which shows a table based on AdventureWorks.

Figure 1 – Table of values over year and product category

The formulas for the headings are

=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2006]”)
=CUBEMEMBER(“PowerPivot Data”,”[DimDate].[FiscalYear].&[2007]”)
etc. for the column headings,

Equation 2

and

=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Accessories]”)
=CUBEMEMBER(“PowerPivot Data”, “[DimProductCategory].[EnglishProductCategoryname].&[Bikes]”)

etc. for the row headings.

The values at the intersection points simply use these heading cells like so

=CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,\$A3,B\$1)

This is equivalent to the formula given in Equation 1.

## Slicing the Vegetables

Further richness is bestowed upon us because we can also link slicers to our table, giving us the sort of filtering we have with the pivot tables. For example, Figure 2 shows the same data table built using CUBE formulae with a fiscal year slicer; the data reflecting the fact that only the years 2006, 2007, and 2008 have been selected.

Figure 2 – Table of values reflecting years slicer selections

Showing the slicer selections on your report has been covered elsewhere, but it is so useful and asked so often that I thought I would also cover. I also have a couple of variations that I haven’t seen elsewhere, which are worth presenting.

Previously, as shown in the formulae in Equation 2, we built the row and column headers using hard-coded values for the year and category fields. We need to be more dynamic in how we list these values. To show the slicer selections as in E5, F5, etc., we need a list of values from which we can choose and display the individual ordered items. The CUBESET function gives us this. The syntax for CUBESET is

CUBESET(connection. set_expression, , [sort_order], [sort_by])

where connection is the cube, set_expression is the set of values required, and caption is a value to display. So, looking at cell D1 we have the formula

=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years

Equation 3

which would look as shown in Figure 3 when added to cell D1 to build our set of fiscal years.

Figure 3 – Slicer years set formula

As can be seen, we use Slicer_FiscalYear as the set_expression, so the set will include all selected values in that slicer, with the caption signifying the cell contents.

So far, so good, but we still need to list those selected values. For this, we use the CUBERANKEDMEMBER function, which returns the nth, or ranked, member in a set. The syntax of this

=CUBERANKEDMEMBER(connection, set_expression, rank, )

where connection is the cube as before, set_expression is the set of values to choose from, and rank is nth value. So, to get the first member, we use

=CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$1,1)

for the second

=CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$1,2)

and so on.

Because there are 5 years in the PowerPivot model, and when filtered in the slicer we might be showing less than 5, we need to cater for a variable number of items. The simplest way is just to add an error wrapper around the formula,

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$1,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$1,2),””)

etc.

Equation 4

## Why Extra Ingredients?

We could nest the CUBESET function within the CUBERANKEDMEMBER,but that would mean that a set is evaluated 5 times. By defining the set in its own cell and referring to that cell within the CUBERANKEDMEMBER function, it is evaluated just the once. A small matter, but it makes the spreadsheet easier to maintain, and is more efficient.

## Cooked To Perfection

It’s as simple as that.

But hang on a minute, have we overcooked it?

Looking at the syntax definition for these two functions, we can see that they both take set_expression as an argument. The CUBESET function is passed the slicer values as its set, and in turned is passed to the CUBERANKEDMEMBER function as its set.

As the slicer values is a set_expression, you would think that we should be able to pass the slicer values directly to CUBERANKEDMEMBER as a set and be done with. And so we can, these formulae

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)

etc.

Equation 5

work equally as well as those formulae in Equation 4

## Managing The Ingredients

The list of product categories can also be listed in a similar way using CUBESET and CUBERANKEDMEMBER. Here we do need CUBESET as there is no pre-defined set of values as we had with the fiscal year that we can pick up. The set will be all values for the Product Category English name in the Product Category table,

=CUBESET(“PowerPivot Data”,
“[DimProductCategory].[EnglishProductCategoryName].Children”,
“Set of Categories”)

Equation 6

As can be seen, .Children gets us all of the category values.

One thing to note is the use of the caption argument. Again, this helps to highlight the cell containing the set.

We now have formulae that can define our full table, such as

D1: the formula in Equation 3
=CUBESET(“PowerPivot Data”,Slicer_FiscalYear,”Set of Years”)

D2: the formula in Equation 6
=CUBESET(“PowerPivot Data”,
“[DimProductCategory].[EnglishProductCategoryName].Children”,
“Set of Categories”)

E5:I5: the formulae in Equation 5
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,Slicer_FiscalYear,2),””)

etc.,

D6:D9: formulae for the product categories

=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$2,2),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$2,1),””)
=IFERROR(CUBERANKEDMEMBER(“PowerPivot Data”,\$D\$2,2),””)

And finally, in E6:I9, the formulae for the values

=IFERROR(CUBEVALUE(“PowerPivot Data”,”[Measures].[Sum of SalesAmount]”,\$D6,E\$5),””)
etc., each cell reflecting the correct product category (\$D6) and fiscal year (E\$5).

Our table now has a full set of values, and reflects the choices made in the fiscal year slicer.

(If we wished, we could add the product categories to a slicer, and make our table dynamically reflect that.)

## Ready to Serve?

Although we have been diligent in storing the evaluated sets in one place rather than nest a CUBESET function within the CUBERANKEDMEMBER function, but there are still a number of things going on here that I just don’t like:

• The connection is hard-coded, multiple times
• If a new year is added to the data, just copying cell I5 to J5 won’t work because the rank is hard-coded in the formula, it will need a small change
• If no selection is made in the fiscal year slicer, the values shown are the total of all years, with a header value of All, as shown in Figure 4. This may be what is required in some instances, in others we may want to show each year’s values individually.

Figure 4 – Showing all years as a total with no slicer selections

These ‘difficulties’ can be overcome relatively easily.

Rather than hard-code the connection within each formula, put the connection text ‘PowerPivot data’ (without the quotes), in a cell, say D3, and assign an Excel name _cube, then use that defined name within the formulae. Note that this also makes the transition to Excel 2013 simpler, where the connection has now changed to ‘ThisWorkbookDataModel’ (again, without the quotes).

The rank is managed by using a function that returns a variable number depending upon the row or column of the cell, namely ROW([reference]) or COLUMN([reference]). You might think that you can use COLUMN(A1) in E5, and copy that across so that it updates to COLUMN(B1), COLUMN(C1), etc. Believe me, this is a very bad idea. Although everything will be fine at first, what happens if you decide to insert a column before column E? The answer is that COLUMN(A1) will update to COLUMN(B1) and whereas the first column of year table originally reflected the first selected year in the fiscal year slicer, it will now reflect the second. You might say that you would never do that, but no-one ever does until they do. For the sake of a simple change it is hardly worth risking it.

The suggested change is to use COLUMN()-COLUMN(\$D\$5), which uses the top left cell of our table as an anchor point. Thus, a formula in cell E5 using these functions will return 1 for that calculation, and so on. If a column is inserted to the left of the table, those parts of formulae will update to COLUMN()-COLUMN(\$E\$5), which means the formula that was in cell E5 which has now moved to cell F5 still return 1 for that calculation.

Similarly, the category list will use ROW()-ROW(\$D\$5).

Finally, how can we show each year in the column headers and the vales for those years when no slicer selection is made, rather than showing ‘All’ and totals for all years? We already have the formula in D1 that gets the set of selected slicer years, that is

=CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”)

As we showed before, we can get a set of all year regardless of slicer selection with the CUBESET function and the member’s children property, as in

CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”)

But how do we know when to use which? One way would be to test whether the first member of this slicer set returns All. If it does, there are no slicer selections so we show all years individually, if not we show the slicer selected years. We can check the first slicer set value with the following

IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>”All”, …

Adding all three elements together, we have the following formula in D1 that determines what goes into the set of years that will drive the table column headings

=IF(CUBERANKEDMEMBER(_cube,Slicer_FiscalYear,1)<>”All”,
CUBESET(_cube,Slicer_FiscalYear,”Slicer Years”),
CUBESET(_cube,”[DimDate].[FiscalYear].Children”,”Dimension Years”))

With this formula to get the years set, we can see all the years listed when no selections are made on the ribbon rather than showing all year totals, as in Figure 5.

Figure 5 – Showing all years with no slicer selections

## What’s For Dessert?

That’s about it. Using this technique we have a table that shows the value by year by product category, with a slicer for selecting specific years which is reflected in the years shown in the table. The years and product categories are dynamically built and so can accommodate extra years and extra categories in the source data, and the years can also handle a full slicer set without showing the values as totals for all years.

# Who Says The Ribbon Is Hard?

## Introduction

I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy.

The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the third can change the image when a certain cell is selected, from one image to another. This principle could easily be extended to have say different groups of buttons for each sheet in a workbook, hide/expose the group upon activation/deactivation of the sheet.

## CustomUI Editor

If you want to look at the xml, you should download the CustomUI Editor. It is hardly what you would call a sophisticated tool, but it does enable you to create xml for the ribbon customisations.

## Coding Techniques

A couple of things about my code. I do not hardcode any values in the xml, apart from the ids of the code. Instead I use callbacks for all of the properties. As an example, I could setup a button in the xml like so

<button id =”btnDynaRibbon1″
label =”Button 1″
onAction =”rxOnAction”
image =”ImportExcel”
size =”1″
visible =TRUE
screentip =”Button that toggles the image” />>

which has all of the properties, except onAction, hard-coded.

I could do it this way, but I don’t, instead my xml looks like this

<button id =”btnDynaRibbon1″
getLabel =”rxGetLabel”
onAction =”rxOnAction”
getImage =”rxGetImage”
getSize =”rxGetImageSize”
getVisible =”getVisible”
getScreentip =”rxGetScreentip” >

where all of the properties are using callbacks, which means that the value gets set within the VBA code. This is the code for the rxGetLabel callback

## Ribbon Code

Public Function rxGetLabel(control As IRibbonControl, ByRef label)

Select Case control.Id

Case CONTROLID_TAB_DYNA:                 label = LABEL_TAB_DYNA
Case CONTROLID_GRP_DYNA:                 label = LABEL_GRP_DYNA
Case CONTROLID_BTN_DYNA_1:             label = LABEL_BTN_DYNA_1
Case CONTROLID_BTN_DYNA_2:             label = LABEL_BTN_DYNA_2
Case CONTROLID_BTN_DYNA_3:             label = LABEL_BTN_DYNA_3
End Select
End Function

It simply tests for the control’s id, and returns the caption for the label appropriately. All of the values tested for and the values returned are stored as constants in the VBA (they could just as easily be stored in a table on a worksheet). The values of all of those constants in the above code snippet are

‘ CONTROLID_ constant values
Global Const CONTROLID_TAB_DYNA As String = “tabDynaRibbon”
Global Const CONTROLID_GRP_DYNA As String = “grpDynaRibbon”
Global Const CONTROLID_BTN_DYNA_1 As String = “btnDynaRibbon1”
Global Const CONTROLID_BTN_DYNA_2 As String = “btnDynaRibbon2”
Global Const CONTROLID_BTN_DYNA_3 As String = “btnDynaRibbon3”

‘ LABEL_ constant values
Global Const LABEL_TAB_DYNA As String = “Dyna-Ribbon”
Global Const LABEL_GRP_DYNA As String = “Dynamic Ribbon”
Global Const LABEL_BTN_DYNA_1 As String = “Button 1”
Global Const LABEL_BTN_DYNA_2 As String = “Button 2”
Global Const LABEL_BTN_DYNA_3 As String = “Button 3”

## Managing Changes

Taking one of the dynamic elements, the first button that can be made non-visible, the visible value is not a constant, but a variable as it has to be manipulated by the code

‘ VISIBLE constant values

Global VISIBLE_BTN_DYNA_1 As Boolean

This variable is initialised as TRUE in the Workbook_Open event, and in the callback procedure, the control’s visibility property is returned as whatever is in this variable

Public Function rxGetVisible(control As IRibbonControl, ByRef Visible)

Select Casecontrol.Id

Case CONTROLID_BTN_DYNA_1:             Visible = VISIBLE_BTN_DYNA_1

etc.

The visibility variable gets changed in simple worksheet event code (remember, I am using a data validation cell to drive this code).

Private Sub Worksheet_Change(ByVal Target As Range)

Case “\$C\$3”

VISIBLE_BTN_DYNA_1 = Target.Value = “Visible”

End Select

mgrxIRibbonUI.Invalidate
End Sub

As you can see, it sets the variable depending upon the cell value. Finally, it invalidates the ribbon variable so as to rebuild our ribbon customisations. The ribbon variable gets set in the ribbon onLoad callback

Public Function rxDMRibbonOnLoad(ribbon As IRibbonUI)

Set mgrxIRibbonUI = ribbon
End Function

Invalidating the ribbon variable causes the callback routines to be re-entered; this is how you change things dynamically.

## Summary

Hopefully you can see how simple it is to create dynamic ribbon customisations, and how to manage the changes within your VBA code. You should also be able to see how this can be extended to other areas of dynamic interaction.

As I mentioned, I keep the CustomUI very simple, and rely on the VBA code to set all of the properties of my ribbon customisations. This is not necessary, it is a style aspect that I prefer, I like to the XML simple, reducing the amount of changes that I need to make there, and instead drive it all from VBA. I use a host of global constants for my ribbon values, but again as mentioned earlier, it would be just as simple (and maybe pro vide more manageability of the values) if all of the control properties were added as a table on a hidden worksheet, and grabbed from there on ribbon load.

My example workbook is attached .

# Ribbon On The Fly Part2

Microsoft sneaked a couple of ne

## Introduction

In my last blog post, I described how I thought that I could build an Excel 2007 ribbon on th fly. The technique was founded upon having a ‘worker’ addin that handled the main functionality as well as the version management and ribbon building; and a simpler Excel 2007 ribbon wrapper addin.

This approach would be similar to the myriad of table driven menu solutions around, but the ‘table’ would be a configuration file in this case, to allow the user to control the menu/ribbon.

The ‘worker’ addin would read the configuration file that held the details of the each procedure to be run, and construct the menu or ribbon on the fly. In the case of the ribbon, the customUI XML would be generated, written back to the (as yet) unopened ribbon wrapper addin, then open the ribbon wrapper addin so as to display the changed ribbon in all its pristine glory.

At that point, this was just an idea, albeit an idea that I was confident that it could be implemented. Since then I have implemented it, so it is time to share the details.

Having a ‘worker’ addin and a separate ribbon presentation addin does create one problem, namely how dos a button on the ribbon run a procedure in the ‘worker’ addin. This is easily resolved using Application.Run, rather than a simple call to the procedure, as this allows specifying the file as well as the procedure name.

In fact, this allows even greater flexibility when deploying in the ‘real’ world. If we release our super application, with a configuration file, the ‘worker’ addin, and a ribbon addin, a user can add items to the configuration file as long as it runs a procedure already defined in our ‘worker’ addin. But what if they want to run a completely new process, how do we provide the ability to extend the applications overall functionality? We could just open up the addin and tell the user to do add thewir code to the ‘worker’ addin, but is this a good idea? I don’t think this is good, it could break the whole application. A better way is to tell the user that they can build their code in an entirely separate project, and addin, Person.als/xlsm, or whatever they wish, and the include the full file/procedure call in the configuration file procedure column.

## Format of Configuration File

It is probably a good point to describe the configuration file at this point.

I have set it up as a standard Excel workbook, in Excel 2003 format for backwards compatibility, and I have a separate tab for each (user) functional ribbon group (or sub-menu for Excel 2003).

I built this technique to service the running of a set of user reports, which are all template files and generally all run the same process, but it could be anything that you want, you would just setup the configuration data, and extract it in the configuration management code.
Figure 1 – Configuration File Group shows a typical layout. If you have any other attributes that you need to capture, just insert extra columns in the first part (in my application, I was importing XML files, so I had to specify the XML table worksheet etc.).

As you can see, some of the columns are relating to the report running, and some relate to the presentation of the ribbon.

Figure 1 – Configuration File Group

I have a column for Report Type, this is so that the client can have the templates in different locations, say company reports on a central server, departmental reports on a departmental server, and the user can have their own report templates locally.

This is all defined on the Client worksheet, Figure 2 – Configuration File Client.

Figure 2 – Configuration File Client

This simply allows the user to provide the client name to appear on the ribbon/menu, a logo file, and the various template directories. Again, if you have a requirement for other client related details, add them here.

The only amendable field here is the client name, the directory values are added by clicking the browse buttons.

I have also added a button to add new groups sheets, so as to keep a consistent format.

## Check the Configuration File

Writing the XML back to the ribbon addin is an expensive process, cracking open the zip file, updating the customUI.xml file and then re-writing the file, so I have added a check to ensure this is only done if and when the configuration file is updated.

This is simply achieved with some simple change event code in the configuration file, Figure 3 – Configuration File Change Code, lines 440-450 simply setup a defined name with a value of TRUE. This is checked in the ‘worker’; addin initialise code to determine whether to update the ribbon addin or not.

As you can see, there is a lot of code here for creating a simple name. In addition to this, the code also checks for a duplicate report ID, resetting if a duplicate value is added (this is to avoid compromising the ribbon).

`Private mcPrevValue As VariantPrivate Sub Workbook_SheetActivate(ByVal Sh As Object)    If Sh.Name = WS_TEMPLATE Then            wsClient.Activate    End IfEnd SubPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As  Range)      Dim mpName As  Name      Dim mpSheet As Worksheet      Dim mpDuplicateRepID As Boolean       Dim mpDuplicateGroupID As Boolean       Dim mpLastrow As Long      Dim i As Long10        On Error GoTo ws_exit          20        Application.EnableEvents = False          30        If Sh.Name = "Client" Then          40        ElseIf Sh.Name = "_template" Then          50        Else          60            If Target.Column = 1 Then              70                mpDuplicateRepID = False80                For Each mpSheet In Sh.Parent.Worksheets                  90                    If mpSheet.Name <> WS_CLIENT And mpSheet.Name <> WS_TEMPLATE Then                      100                       mpLastrow = mpSheet.Cells(mpSheet.Rows.Count, "A").End(xlUp).Row110                       If mpLastrow > 2 Then                          120                           For i = 3 To mpLastrow                              130                               If Sh.Name <> mpSheet.Name Or Target.Row <> i Then                                  140                                   If mpSheet.Cells(i, "A").Value2 = Target.Value Then                                  150                                       mpDuplicateRepID = True160                                       Exit For170                                   End If180                               End If190                           Next i                              200                           If mpDuplicateRepID Then Exit For210                       End If220                   End If230               Next mpSheet                  240               If mpDuplicateRepID Then250                   ShowMessage Replace(MSG_ERROR_DUPLICATE_REPORT, _                                      "", Target.Value), vbOKOnly + vbExclamation260                   Target.Value = mcPrevValue270               End If280           ElseIf Not Intersect(Sh.Range(NAME_GROUP_ID), Target) Is Nothing Then              290               mpDuplicateGroupID = False300               For Each mpSheet In Sh.Parent.Worksheets                  310                   If Sh.Name <> WS_CLIENT And Sh.Name <> WS_TEMPLATE And Sh.Name <> mpSheet.Name Then                      320                       If mpSheet.Range(NAME_GROUP_ID).Value2 = Target.Value Then                                  330                           mpDuplicateGroupID = True340                           Exit For350                       End If360                   End If370               Next mpSheet                  380               If mpDuplicateGroupID Then390                   ShowMessage Replace(MSG_ERROR_DUPLICATE_GROUP, _                                      "", Target.Value), vbOKOnly + vbExclamation400                   Target.Value = mcPrevValue410               End If420           End If430       End If440       Set mpName = ThisWorkbook.Names.Add(Name:="_Changed", RefersTo:="=TRUE")450       mpName.Visible = Falsews_exit:460       mcPrevValue = Target.Value470       Application.EnableEvents = TrueEnd SubPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)    mcPrevValue = Target.ValueEnd Sub`

Figure 3 – Configuration File Change Code

## Testing Excel Version

Determining whether to build a menu or a ribbon is very straight-forward, all we need to do is to check the application version, as shown in Figure 4 – Test Excel Version. There is nothing particularly trick here, but there are a few call to other procedures that will be covered in the next post, namely

• AppInitialise – opens the configuration file, does some basic validation of the template paths, and then grabs all of the report details
• OpenRibbonAddin – this checks if the configuration file has been changed since the last run, builds the XML, and writes it back to the ribbon addin.

There is one other procedure call, BuildMenus, which I will not go into, it is a standard commandbar menu builder.

`Private Const mmModule As String = "ThisWorkbook"	'-----------------------------------------------------------------Private Sub Workbook_BeforeClose(Cancel As Boolean)'-----------------------------------------------------------------    If Val(Application.Version) < 12 Then        Call DeleteMenus    Else        mgInsightRibbon.Close    End IfEnd Sub'-----------------------------------------------------------------Private Sub Workbook_Open()'-----------------------------------------------------------------Const mpProcedure As String = "Workbook_Open"    On Error GoTo Workbook_Open_Error    PushProcedureStack mpProcedure, True    Call AppInitialise    If Val(Application.Version) < 12 Then        Call BuildMenus        mgConfigWB.Close SaveChanges:=False        Set mgConfigWB = Nothing    Else        Call OpenRibbonAddin    End IfWorkbook_Open_Tidy:    PopProcedureStackWorkbook_Open_Exit:    Application.DisplayAlerts = True    If Not mgConfigWB Is Nothing Then mgConfigWB.Close SaveChanges:=False    Set mgConfigWB = Nothing    Exit SubWorkbook_Open_Error:    If Err.Number = AppBypassErrorNum Then Resume Workbook_Open_Tidy    If AppErrorHandler(mmModule, mpProcedure, True) Then        Stop        Resume    Else        Resume Workbook_Open_Exit    End IfEnd Sub`

Figure 4 – Test Excel Version

## Next Post

In the next post, I will give details on the getting the configuration file details, building the ribbon XML, and using the addin.

# Ribbon on the Fly, or the Zip

I recently needed to build an Excel addin that supported a variable number of options, with buttons to invoke the options. That in itself is not unusual, but I also wanted the numbers to be driven by the user; which always complicates matters.

The definition of the buttons is simple, even allowing for the fact that it will be maintained by users. I could use a separate configuration workbook, an INI file, or even XML. As I am looking to make this a user driven facility, within an overall Excel framework, an Excel workbook seems the best option.

For the addin, in Excel 2003, we have a simple solution, namely read the workbook and build a menu of options dynamically on startup. As seems to be the norm, it is not so simple for Excel 2007 and 2010. I could just build commandbars as for Excel 2003 and let them show in the Addins tab, but this is a very poor solution in my view. I showed in a previous post last year how we can manage addins in both Excel 2003 and later versions, and support menus or the ribbon, and it was my intention to use this technique here. This does not cater for the variable options in the ribbon however.

There couple of obvious ways to manage the variable options in the ribbon. One would be to use a dynamic menu control, which can be dynamically populated when the menu is invoked. This is a nice option for lists, such as file lists, but I don’t like it as the only option in my ribbons, it does not seem to fit the ribbon paradigm. Another way would be to define a fixed number of buttons, make the visibility of the buttons a dynamic setting, and use the values in the configuration workbook to determine which are made not visible. Again, this is not an ideal solution in my view, it is almost guaranteed that not enough will be defined.

As I mentioned above, I already have a technique for supporting menus and the ribbon in my addins, and this consists of an Excel 2003 addin which is the primary component that checks the version, builds commandbar menus if the Excel version is less than 12 (Excel 2003 or earlier), otherwise it opens a ribbon wrapper addin. All of the grunt work is done in the Excel 2003 addin, and these procedures are called from the ribbon wrapper addin. The key here for Excel 2007 and on is that the ribbon addin is not initially opened. As Excel 2007 is a zip file with various components, including a CustomUI XML component, it is possible to open this zip file and overwrite the CustomUI XML. So, before I need to open the ribbon addin, I can dynamically update the XML, and I have a fully dynamic ribbon.

Of course, there is still the issue of calling the correct procedure regardless of whether the addin is loaded in Excel 2003 or a ribbon enabled Excel. The issue arise in the ribbon wrapper, the button actions need to call procedures in the Excel 2003 addin, and in my technique I do this using Application.Run calls across projects. Because all of the code in my required addin will perform the same action, just on differing files which are defined in the configuration file, this works fine, but the configuration file could always be extended to provide the name of the project and procedure to be called.

So much or the theory, just need to get on and create it now.