Category Archives: 11522

Ribbon On The Fly Part2



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.


Linking the Ribbon Addin to the ‘Worker’ Addin


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 Variant

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name = WS_TEMPLATE Then

wsClient.Activate
End If
End Sub


Private 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 Long

10 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 = False
80 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).Row
110 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 = True
160 Exit For
170 End If
180 End If
190 Next i

200 If mpDuplicateRepID Then Exit For
210 End If
220 End If
230 Next mpSheet

240 If mpDuplicateRepID Then
250 ShowMessage Replace(MSG_ERROR_DUPLICATE_REPORT, _
"", Target.Value), vbOKOnly + vbExclamation
260 Target.Value = mcPrevValue
270 End If
280 ElseIf Not Intersect(Sh.Range(NAME_GROUP_ID), Target) Is Nothing Then

290 mpDuplicateGroupID = False
300 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 = True
340 Exit For
350 End If
360 End If
370 Next mpSheet

380 If mpDuplicateGroupID Then
390 ShowMessage Replace(MSG_ERROR_DUPLICATE_GROUP, _
"", Target.Value), vbOKOnly + vbExclamation
400 Target.Value = mcPrevValue
410 End If
420 End If
430 End If

440 Set mpName = ThisWorkbook.Names.Add(Name:="_Changed", RefersTo:="=TRUE")
450 mpName.Visible = False

ws_exit:
460 mcPrevValue = Target.Value
470 Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
mcPrevValue = Target.Value
End 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 If
End 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 If

Workbook_Open_Tidy:
PopProcedureStack

Workbook_Open_Exit:
Application.DisplayAlerts = True
If Not mgConfigWB Is Nothing Then mgConfigWB.Close SaveChanges:=False
Set mgConfigWB = Nothing
Exit Sub

Workbook_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 If
End 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.

Deploy Me Simple


Excel 2003 Addins In Excel 2007


Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version.


One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to find the new functions in the Excel 2003 UI.


As with many things, Excel 2007 changed all of that. Excel 2007 supports the old commandbar model, but in a particularly inelegant way. The menus and toolbars that an addin might create are dropped onto the Add-Ins tab. Figure 1 shows my Excel 2007, with two addins installed, NameManager1 and Snagit2



Figure 1 – Addins Tab



Figure 2 – NameManager Menu


As I said, this is not an elegant solution, far better to adopt the Ribbon paradigm and either add a ribbon tab, or add a group to an existing tab.


The problem here is that whilst Excel 2007 can handle commandbars, Excel 2003 doesn’t understand the ribbon. The obvious solution is to have two versions of your addin, one that is Excel 203 compliant, one for Excel 2007.  This is far from ideal, having to maintain two versions where 90+% of the code will be the same is a maintenance nightmare. Another alternative is to put all of the common code into a DLL, which would still be multiple files and might entail a large amount of work re-writing the code to manage an Excel instance.


The solution that I am proposing here is to have two addins, but two very different addins.


Overall Design


I will be using one of my addins in the discussion that follows. This addin, called ‘SP Builder3, is a small addin with a simple menu that will usefully demonstrate this technique without confusing the matter with too much detail.  The 2003 menu for ‘SP Builder’ is shown in Figure 3



 


Figure 3 – SP Builder Menu


Figure 4 shows the ribbon version of ‘SP Builder’. As you can see, I have added a three button group to the Developer tab in the ribbon.



Figure 4 – SP Builder Ribbon


The functionality of each addin is as follows:-


  • Excel 2003 – functionally unchanged, apart from modifying the workbook open code to test the Excel version, if it is 2003 or lower the commandbars are loaded, if not the Excel 2007 workbook is opened. The workbook close code deletes the commandbars or closes the Excel 2007 addin
  • Excel 2007 – a basic addin that has ribbon XML. The callback code invokes the actual procedures in the Excel 2003 addin. When this addin loads, the ribbon is automatically built, and, because the Excel 2003 addin checks the version, the coomandbars are not loaded so we have nothing in the Add-Ins tab.

Excel 2003 Addin


As mentioned above, the only changes to the Excel 2003 addin are the open and close routines.


The workbook open checks the current Excel version and either creates the commandbars, or loads the Excel 2007 workbook, depending upon the version, as shown in Figure 5.


     If Val(Application.Version) < 12 Then

         If Not mhCommandBars.CreateMenu(False) Then Err.Raise appErrorGeneric
     Else

         Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WB_RIBBON
     End If


 


Figure 5 – Addin Initialisation


Similarly, the workbook close changes are shown in Figure 6.


     If Val(Application.Version)< 12 Then

         Call mhCommandBars.DeleteMenu
     Else

         Workbooks(WB_RIBBON).Close SaveChanges:=False
     End If


Figure 6 – Addin Removal


That is all that is required in the Excel 2003 addin, as you can see this adds no maintenance overhead.


Excel 2007 Addin


The Excel 2007 addin is completely new, but again there is nothing difficult or overly complex in this code. It is basically a wrapper to present the ribbon to the user, it has no real functional purpose in itself.


First we will look at the XML required to define the ribbon changes. Figure 7 shows the XML.


The XML here is very straight-forward, a single group with three buttons. It is likely that your addin will have a richer interface which will require more XML but that is not the objective of this article. As you can see, the group is added to the Developer tab. This is because I see the SP Builder addin as a development tool, but your addin could be in any tab, or even add another tab to the ribbon. One other thing to note is that all of the buttons call the same callback routine, this is explained later.


<customUI xmlns=http://schemas.microsoft.com/office/2006/01/customui
          onLoad=rxspRibbonOnLoad>

  <ribbon startFromScratch=false>

    <tabs>

      <tab idMso=TabDeveloper>

        <group id=grpSPBuilder label=SP Builder>

 
          <button id=btnSPBuilder
                  label=Build SPs
                  imageMso=ControlsGallery
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPList
                  label=List SPs
                  imageMso=ConditionalFormattingHighlightDuplicateValues
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPAbout
                  label=About…
                  imageMso=Info
                  size=”large”
                  onAction=”rxspOnAction />
        </group>
      </tab>
   </tabs>
  </ribbon>
</customUI>


Figure 7 – Ribbon XML


In my Excel 2007 ribbon handling code, I always create a set of constants for the ids of each of the controls, as shown in Figure 8, which are then used in the callback code.


Option Explicit

Global Const CONTROL_ID_BUILD As String = “btnSPBuilder”
Global Const CONTROL_ID_LIST As String = “btnSPList”
Global Const CONTROL_ID_ABOUT As String = “btnSPAbout”


Figure 8 – Ribbon Constants


The final piece of this jigsaw is the ribbon callback code, Figure 9. As said earlier, I use a single callback and test the control id to determine the action to take. The key aspect of this code is that the action for each of the buttons is to run the ‘real’ procedure in the Excel 2003 addin, which is achieved with Application.Run4.


Option Explicit

Global rxspIRibbon As IRibbonUI

Public Sub rxspOnAction(control As IRibbonControl)
    Select Case control.ID

        Case CONTROL_ID_BUILD:  Application.Run “‘SP Builder.xla’!SPBuild”

        Case CONTROL_ID_LIST:   Application.Run “‘SP Builder.xla’!SPList”

        Case CONTROL_ID_ABOUT:  Application.Run “‘SP Builder.xla’!AboutSPBuilder”5
    End Select
End Sub

Public Sub rxspRibbonOnLoad(ribbon As IRibbonUI)
    Set rxspIRibbon = ribbon
End Sub


Figure 9 – Ribbon Callback Code


Summary


This provides a simple, and elegant, way to manage addins that are required to be deployed in Excel 2007 or earlier versions.


By using a ribbon wrapper to deliver the ribbon changes, we are keeping the code to a minimum, and it is not introducing a maintenance overhead.



1There is a 2007 version of NameManager which I believe has a ribbon, but I have not installed it

2 Interestingly, Snagit has not added to the ribbon although the product has adopted the Ribbon UI!

3 Although t is not material to this discussion, ‘SP Builder’ is a small addin that simplifies building stored procedures and loading them into a database

4 Although you cannot pass parameters directly in a callback, this technique also allows passing a paremeter to the 2003 procedure. This could be useful to let the ‘worker’ addin know that you are running from Excel 2007 if it wanted to do something differently in that instance

5 It would be a far better practice to create global constants with the called addin and procedure details rather that repeating this in hardcoded form, similar to how I manage the control Ids

Ribbons – Gain Without The Pain
































































































































Normal












































0






































































































































false












































false












































false







































































































































































































































































































































































MicrosoftInternetExplorer4








































































































































































































































































If you are in the habit of building or customising Excel 2007 Ribbons, there is an indispensable new tool that has just hit the market. Andy Pope has released his RibbonX Visual Designer, which can be found here.

 

I have blogged previously about how I find cutting XML tedious, and the inadequate tools available, so this is a more than useful addition to make ribbon design and coding simpler. It provides a GUI to facilitate the Ribbon build, simple point and click rather than the tedious line coding. The big advantage of this approach is that you can see the hierarchy of your ribbon, and visualise what it will finally look like. To make it even better, there is a preview option, which will build a workbook and inject the XML so that you can ACTUALLY SEE what it will look like.

 

The tool adds a group to the Developer ribbon tab (so make sure that you make this visible)






































































































































































































































































































































































































































































































































































































































































































































































































































































































































and here is a screenshot of the designer, with a few example controls added.




























































































 


In addition, you can load an existing workbook, and amend its ribbon. It has many other goodies which I won’t list here, check out the link above to read about them all.


 


As I said, a great tool, and at a good price – free!


 

Autogen’ed Ribbon Code





Normal

0





false

false

false















MicrosoftInternetExplorer4
















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
generation.


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
follows:


  • 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

=SUBSTITUTE(D6&”_”&E6&”_”&C6,”__”,”_”)


  • Control Id – a derived id for this control

=IF(A3=”group”,”grp”,IF(A3=”button”,”btn”,IF(A3=”separator”,”sep”,A3)))&$B$2&IF(A3=”button”,PROPER(E3),””)&B3


  • 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 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.


Considerations


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

































































Normal































0































































































false































false































false













































































































































































































































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 http://www.rondebruin.nl/ribbon.htm.


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="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

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

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

<button id="btnDemoButton1"
label="Currency"
image="DollarSign"
imageSize="Large"
screentip="Button 1"
superTip="Format activecell as currency"
keytip=""
onAction="procButton1" />

<button id="btnDemoButton2"
label="Percentage"
image="PercentSign"
imageSize="Large"
screentip="Button 2"
supertip="Format activecell as percent"
keytip=""
onAction="procButton2" />
</group>

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

<button id="btnDemoButton3"
label="Text Wrap"
getImage="WrapText"
getSize="Large"
getScreentip="Button 3"
getSupertip="Format activecell with text wrap"
getKeytip=""
onAction=" procButton " />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

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 


getLabel="rxDemoGetLabel"

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="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

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

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

<button id="btnDemoButton1"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />

<button id="btnDemoButton2"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />
</group>

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

<button id="btnDemoButton3"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

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()
mgrxDemoIRibbonUI.Invalidate
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()
mgrxDemoIRibbonUI.Invalidate
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.