Category Archives: 11518

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)


    Select Case Target.Address


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

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

You Say CustomUI Editor, I Say TextPad

 


I have written before about hand-crafting XML so as to customise an Office 2007 ribbon at XML Is Such A Pain and Autogen’ed Ribbon Code


 


As I have mentioned before, the CustomUI editor is far from perfect. It does allow adding of  image files and to validate the XML, but the editing experience is absolutely awful.


 


My favourite editor is TextPad, and one of the useful features of TextPad is the facility to incorporate syntax files that are file type dependent, for instance a Visual Basic syntax file for .bas and .vbs file types. The syntax files can highlight elements of your files which assist in  entering script as well as making the script more readable.


 


Although I rarely use the CustomUI editor when I am developing Excel ribbons these days as I autogen everything, my autogen doesn’t yet handle Word or Access ribbons. As I still don’t want to use the CustomUI editor, I built myself a CustomUI syntax file for use with TextPad. I can now edit my XML in TextPad, and copy and paste into the CustomUI editor when it is ready. I find this much more productive.


 


It is still necessary to setup TextPad with a document class for these files, setup the keyword colours and so on, but if you think it could be useful, you can get it a copy via the RSS feed, or download it here.

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.