Dutch Gemini's Weblog Pages

January 20, 2011

Custom Excel 2007 Ribbon and VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 4:04 pm
Tags: , , , ,

This page is not yet another treaty on the “how to” of the Office ribbon —many books have been written on this subject— but more a collection of my experience with and pitfalls of the Fluent Ribbon in combination with VBA.

I have (finally, can we say that?) started programming the Excel 2007 ribbon. Since now, I have been busy building Add-Ins with VBA using old-style CommandBars and was fairly happy with it, mainly because most customers for one reason or the other were still using Office 2003. Nevertheless, Microsoft had made to move to the so much-advertised Fluent Ribbon at the end of 2006, so I thought the time was finally ripe to start building my own ribbon, also because I didn’t like the visual of my CommandBar on the Add-Ins tab of Excel 2007.

The first aim of this exercise was to understand the Fluent Ribbon and how I could customise it. I bought books on the subject and scanned the Internet. Finally I decided to keep the Add-In as it was so it could continue to work on every PC running whatever release of Excel, and elected to create a Ribbon interface that would copycat my own CommandBar.

It took me finally full 2 weeks to get to a decent result and during that time I have learned a lot of things about the Fluent Ribbon, about its customisation possibilities and the interaction with VBA. Honestly, I am not at all impressed by the Ribbon and I think Microsoft has still a lot of work to do. The part I like less is the interface for customisation using only VBA: it is simply not possible.

Just to give you an idea about what you should do to start customising the Ribbon (copied from http://msdn.microsoft.com/en-us/library/aa338202.aspx and adapted for Excel):

  1. Create a folder on your desktop named customUI.
  2. Create the customization file in any text editor by writing XML markup that adds new components to the Fluent UI, modifies existing components, or hides components. You can use the XML markup from the previous example, to test the behavior. Save the file as customUI.xml (or any other name) in the folder you just created.
  3. Validate the XML markup against your custom Fluent UI schema (optional).
  4. Create a document in Excel, and then save it as an Office Open XML Formats file with the .xlsm extensions. File name extensions for files that contain macros have an “m” suffix. These files can contain procedures that can be called by Ribbon extensibility commands and controls.
  5. Exit Excel.
  6. In Microsoft Windows Explorer, add the file name extension .zip to the document file name, and then double-click the file to open it as a compressed folder.
  7. Add the customization file to the container by dragging the customUI folder from the desktop to the compressed folder.
  8. Drag the _rels folder to the desktop. A folder named _rels containing the .rels file appears on the desktop.
  9. Open the new folder, and then open the .rels file in a text editor.
  10. Between the final <Relationship> element and the closing <Relationships> element, add a line that creates a relationship between the document file and the customization file. Ensure that you specify the folder and file names correctly (the Id attribute supplies a unique relationship ID for the customUI—its value is arbitrary).
  11. Save the .rels file.
  12. Drag the .rels file from the desktop to the _rels folder in the compressed file, replacing the existing .rels file.
  13. Remove the .zip extension from the container file.

Have you noted that, apart from step 4, all other steps are actually performed outside of Excel?

And I have not yet spoken about the VBA CallBacks needed in your code. The CallBacks are used by Excel to obtain customisation information about each control. Since Excel provides different types of ribbon controls it also calls these CallBacks with varying parameters, some ByVal, others ByRef. You need to find which parameters are passed and what value they expect back.

Issue 1: You cannot create ribbon elements dynamically in VBA

It is not possible to create ribbon elements dynamically via code as with Office 2003, where you could manage your own CommandBars and CommandBarButtons.

In Excel 2007 each ribbon element (Tab, Group, Buttons, etc.) needs to be defined statically and embedded in the Excel document using a specially crafted XML file and with quite a few manual steps, including renaming and modifying contents of the Excel document —factually a ZIP with the XLSM or XLAM extension.

Once you have embedded your ribbon definition in the Excel file then you can use VBA to control size (small or large), labels, visibility, enabled and toggled status but other things, like controlling the position on the ribbon, is not possible.

Issue 2:  Assigning custom icons dynamically is cumbersome

Excel offers the possibility to change the image of a ribbon element at run-time via a “getImage” VBA CallBack. The VBA CallBack has a ByRef parameter that allows you to specify the image you want to use. This parameter accepts 2 types of value: string or object.

When you specify a string value (eg. “FileSave”) then Excel looks up the value in the list of internal images (the so-called Mso images) and draw the corresponding icon on the ribbon element. As long as you want to use the  Mso images then there is little problem. If, however, you do not like the internal images and want something of your own mill then you are in trouble.

You can embed images in the Excel workbook but Excel can only “see” these images when you are using them statically on the ribbon element. If you want to use the VBA CallBack to get another custom image on the ribbon then you must provide, for the ByRef parameter, an object in IPictureDisp format. VBA’s LoadPicture() returns such object but it can’t read all picture formats (the recommended PNG for instance are excluded) and it also needs a solid file to read from. LoadPicture() cannot extract the image file from the Excel document, which means that you must find a way to make your custom icon available in solid form at run-time (either during installation or when booting Excel).

And if your icon file is in a non-readable format for LoadPicture, then you must use the GDI+ to convert the picture for you (look at LoadPictureGDI on Stephen Bullen’s Excel Page).

Issue 3:  If you use custom icons you may receive errors when trying to Customise the QAT

When your ribbon tab uses custom images (static or dynamic makes no difference) then as soon as you try to customise the Quick Access Toolbar (QAT) you will get prompted with an empty error for each non-Mso image on your ribbon:

Error message when customising QAT

This error occurs when your custom image is not 16×16 pixels. To obtain this error I have used, because I needed “large” buttons, only images with 32×32 pixels. On a few ribbon items I used 16×16 images and these were not triggering errors.

Issue 4: Tag property of Ribbon elements pointing to item with idMso not propagated

When you need a standard Excel ribbon element on your custom tab, forget about the Tag property. I have tried using it in my VBA CallBack but unfortunately the value was not propagated. I now check the Id property instead, which contains the internal Id (eg. “FileSave”).

Issue 5: You cannot hide item Separators dynamically at run-time

If you use separators in ribbon groups and want to hide them at run-time because the element at its right is being hidden, forget about it. There is a bug in Excel 2007 that does not permit setting the visibility of a separator with a VBA CallBack. If have reverted to a using a Ribbon Label made up of 6 non-breakable spaces (ASCII 255) that I show or hide upon need.

Issue 6: If you use a SplitButton don’t use the ‘getVisible’ CallBack of the button’s items

A SplitButton is made of a Button and a Menu. If you click the button its ‘onAction’ executes, and when you click the menu, a pop-up menu is displayed offering other options. The Paste button on Excel’s Home tab is such button.

Technically, the SplitButton contains a Button ribbon element and a Menu ribbon element. You are allowed to control the visibility of the SplitButton via VBA CallBack but it is not allowed to assign a VBA CallBack to control the Button.

Issue 7: The “List” items use a different Option Base

On the old-style CommandBar, controls that contain lists such as the Combo Box start with their first entry at 1 (one). In the new-style Ribbon, the first element has index 0 (zero).

Recommended Tools and Tips

Microsoft does not offer an integrated solution for editing the ribbon but luckily the community has made available tools that bring you really much ahead. I recommend the following free-as-beer products:

The Custom UI Editor is “endorsed” by Microsoft. This tool is fast, proofs the customisation of your XML against the schema. Lacks a visual designer and the entire definition is hand-written, which does not offer a guarantee that it will work in Excel, as a matter of fact mine crashed regularly until I discovered that one of the custom image ID’s started with a number (“3DIcon” — after I changed it to “_3DIcon” everything worked again). It also keeps track of the custom image resources that you are using. However when you remove one of such resources the tool does not properly remove all the links and you may need to manually correct the “customUI.xml.rels” file located under the “customUI/_rels” folder in the Excel document.

The RibbonX Visual Designer made by Andy is an excellent Office Add-In (sits on the Developer tab) enabling you to “design” the ribbon customisation using a tree-like structure. It also offers “preview”. I have found some small problems with this tool, such as no validation of XML schema, deleting entries crashes the Add-In, regularly I get Zip/UnZip errors and once in a while Excel disappears but I have learned to Save regularly. Overall this tool has greatly supported my transition to the Ribbon interface and I will always grateful to Andy for making this tool available to us.

Both tool generate the code for the VBA CallBacks in a separate window. You need to copy this code and paste it in the file’s code module. However, if you add more ribbon elements in the above tools you can’t copy the entire VBA CallBacks to the code module anymore, but need to add only the missing CallBacks.

It would be nice if —and as an Excel Add-In Andy’s tool could do this— the VBA CallBacks could be generated automatically in the file’s code modules, closing the loop from this point of view.

Caution: do not use these tools at the same time on the same files. Sounds strange, but I had the same file open in the UI Editor and in Excel with the RibbonX Designer. At some point I saved the file in Excel and —without reopening it in UI Editor— I started making some changes which inevitably destroyed the modifications I had saved just minutes earlier in Excel.

NotePad++ is an excellent replacement for NotePad and has the necessary XML syntax highlighting. I also recommend IcoFX (www.icofx.ro) if you want to make fancy looking icons.

Tip 1: Names of VBA CallBacks

I recommend creating a single VBA CallBack procedure for each property you want to control at run-time for similar types of ribbon items. For instance, the visibility CallBack for each button ribbon item I use is named “AllButtons_getVisible” and in this procedure I use a Select Case based on the control’s Id.

This also helps keeping your VBA code module manageable and small.

Tip 2: Create a dummy Code Module with all VBA CallBacks to import in your document

I have available a VBA code module (.bas) with all possible callbacks already in place, using the indications in Tip1.  Each CallBack returns a default value that allows the ribbon to become visible and the ribbon elements to be evaluated in a proper manner. For instance, all Boolean CallBacks receive True, all textual ones receive a proper text such as “Label”, “ToolTip”, “SuperTip” and images receive “HappyFace”

When I want to create a new Ribbon, I import this code module in my macro-enabled Office document before I start working on the customisation. In this way, I can immediately preview the ribbon without error messages about missing procedures being generated.

Before I finalise the customisation, I use a Debug.Print "name_of_procedure" statement to verify whether a particular routine is being used or not and remove the unused ones.

You may also create a template file with all the necessary stuff inside and use that as a starting point.

Tip 3: Wrap a Ribbon Object made in Excel 2007 around your Excel 2003 style CommandBar

If you have an Add-In designed for pre-2007 Excel also, that creates its own CommandBar, do not embed the ribbon customisation in this file but create a separate Excel macro-enabled workbook containing the ribbon customisation. In this workbook’s Open event, hide the original CommandBar, and use the ribbon buttons’ onAction property to ‘click’ the corresponding button on the old-style CommandBar.

Tip 4: Use Windows Explorer’s Zip functionality

I recommend using Windows Explorer’s compression and decompression functionality to pack or unpack your Office documents. This functionality is normally available on the right-click contextual menu.

Explorer uses the same compression routines —and thus compression method— used by Excel. This will minimise the chances that your document gets “corrupted” by an “unknown” algorithm.

This I learned the hard way after I used IZArc (I’m a addicted freeware junkie) to re-compress a folder structure to an Excel 2007 file and, as you might expect, Excel didn’t like it too much. Probably if I’d used different parameters in IZArc the problem would never popped up, but at least now I know from the beginning that this is a potential for problems.

Tip 5: Keep it simple

In my first attempt I set VBA CallBacks with individual names for all items on my custom Ribbon. This generated a massive 2300+ line code module and I totally lost overview. Without doubt quite an achievement, and the code still didn’t do anything!

Now my entire module with comments and shared VBA CallBacks scores a little above 800 lines of code.

Tip 6: Re-think your CommandBar

The Ribbon offers a new way to present your CommandBar buttons. I think this is probably the most but only appreciated feature of this object.

Tip 7: After invalidating the *entire* Ribbon, issue a ‘DoEvents’

In Tip #3, I recommend wrapping the ribbon around the CommandBar. During the development, working with an XLSM workbook, everyhing worked without problem. The various tabs, panels, groups, labels, etc., are read at run-time from the equivalent values of the hidden, Excel pre-2007, CommandBar.

I created the final (password-protected) XLAM of my Ribbon wrapper and started a new round of tests and at that point Excel locked up almost completely. Although Excel was flashing on the TaskBar, I could not get control back, the only possibility was killing it the hard way via Windows Task Manager.

Then I opened and unlocked the Add-In in the VBIDE before running the test and there it was: the same blank VBA error message shown in Issue #3. After some googling with “excel 2007 blank vba error message” I was able to focus the problem. Excel refreshes the Ribbon in an asynchronous thread and probably during this thread it cannot access the [hidden] CommandBar it is reading from. This results in the blank VBA error message. Unfortunately Excel appears to popup this error while running macro code and when the VBIDE is *not* open, the message is shown in an unaccessible and invisible layer.

The solution that worked for me was adding a ‘DoEvents’ statement right after invalidating the entire Ribbon:

' In a Code Module of the Ribbon Wrapper
'
Public mRibbonUI As Office.IRibbonUI

Public Sub InitialiseAddInsRibbon(ribbon as IRibbonUI) ' Initialise CallBack
    Set mRibbonUI = ribbon
End Sub

Public Sub RefreshAddInsRibbon()
    '
    ' Code for refreshing the Ribbon.
    '
On Error Resume Next

    ' May not exist yet.
    '
    If mRibbonUI Is Nothing Then Exit Sub

    ' Invalidate the entire ribbon.
    '
    mRibbonUI.Invalidate

    ' --------------------------------------------------------------------- '
    ' Without this next statement, Excel can run out-of-sync, popup empty '
    ' vbCritical errors, lockup and/or destroy the custom ribbon. '
    ' --------------------------------------------------------------------- '
    '
    DoEvents ' DO NOT COMMENT OR REMOVE THIS LINE.
    '
    ' --------------------------------------------------------------------- '
End Sub

Nevertheless, re-thinking the layout of your CommandBar, of the buttons (and icons) can generate a very positive return on the acceptance of the application by your customers. Your customers don’t see and likely do not understand the triple salto’s your code makes but they will absolutely be impressed by how it presents. A well-designed look of an application often let people presume that the same cure was dedicated to the underlying code (this is no guarantee, the Ribbon itself demonstrates that this can also be the other way around).

Honestly, would you use or buy an ugly-looking software even though it promises miracles? I don’t think so, why would software vendors otherwise put so much emphasis on screenshot?

Dutch

Advertisements

10 Comments »

  1. I’m developing an Excel-2007/VBA application in which the ribon is customized and do experience a big problem.

    In my Robbpn there are 10 button who have attached to them a getVisible callback and one also has a dynamic label (also using a call back) (= a total of 11 callbacks). These all work fine.

    Two problems however do appear:

    1 not so important but possible related) When a run my macro program having set a breakpoint in VBA then as soon as that breakpoint is and the VBA editor appears I get 11 time an Alert showing the message: Can’t execute code in breakmode.

    2 (important error). In my VBA-application i’m doing an sql query using Connection.ODBCConnection.Refresh. This query is slow. After about 10 secondes my window background changes (to light blue) although screenupdating=false. At that moment the first of 11 error alert appears. These alerts do not contain any text only a vbCritical icon an Ok and a Help button. When pressing that OK button (11 times) my program continues completing the refresh and all is fine.

    Is there anyway to eliminate these alerts. (by the way an on error does not trap them, nor are the influenced by Application.displayAlerts true or false or screenUpdating true or false).

    Anyone has some tips to solve this?

    the doevents is already added, also having a”n application specific name of the call back (and not a generic lich getEnabled. Alos the module nam eis part of the callback name: “myModule.getEnabled_myApp”. All these sugestions were found on the internet but do not help.

    The blank messages do also constatntly apear when running a slow action (like the refresh), activating outlook, maximize outlook, close ouutlook to have the excel=app reappear. The blank error simmidealtely are shown.

    Comment by John S — August 21, 2012 @ 9:22 pm | Reply

    • As far as I have been able to detect, the refresh callback is called for every of buttons on the ribbon, the standard ones and all of the custom ones which includes your 10 buttons. This makes also sense because the ribbon is updated as a whole. Likely this is the reason for getting the messages so many times.

      I have found that the “empty” error gets triggered mainly for 2 reasons:

      1) when the refresh is performed while the application is busy
      2) when the chosen button icon/image does not contain all possible sizes (16×16, 32×32, …).

      The “can’t execute code while in breakmode” tells Excel is running a background process, the ribbon refresh is such background process and Excel does not like this to be interrupted. Hence, in your callbacks do not set a breakpoint but use Debug.Print or similar to get the value of the requested variables.

      The light blue Excel is an indication that it is in a ‘not responding’ state. This is set by Windows and you have no control over it.

      For what I read I presume there is some other application/add-in running that is forcing a ribbon refresh outside the control of your application, and this also triggers the update of your ribbon (see first line).

      ScreenUpdating and DisplyAlerts will not help: the Screen updating setting does not affect the outer frame of Excel including the ribbon and commandbars but only the internal area (i.e. the windows). DisplayAlerts is only there to suppress certain system alerts/warnings giving a default answer, e.g. during Save File you won’t get the “Xxx exists, overwrite?” warning and the answer will be automatically ‘Yes’.

      What I can think of for solving or mitigating your problem is that you use a Boolean variable, for example ‘bImBusy’, which you set to ‘True’ when doing the CPU-intensive and ‘False’ when finished, and break out of your callback routines upon ‘True’ including NOT doing a DoEvents which in my case often triggered the empty message.

      Hope this helps.

      Dutch

      Comment by dutchgemini — August 22, 2012 @ 8:41 am | Reply

  2. Thanks, all the element of your answer I had already expected. some remarks remain:
    1 is there no way (a value in the registry) which influenced the period after which Excel things it is not responding (and the window get light blue)?
    2 the bImBussy is not meaning full (I expect) because the Call back gteVisible is not activated (as can be seen in debug.print , but I will investigate this issue more detailed).
    3 indeed the blank message also appear triggered from outside my program ( see the description of acivating outlook and minimizing that). Is there no way to trap these alerts?

    Comment by John S — August 22, 2012 @ 9:24 pm | Reply

    • @1: You may want to Google for it, I did and there are some tips and registry hacks but I can’t tell whether they’re effective or not.
      @2: Apart from trapping “Ribbon.Invalidate” during execution of long lasting procedures, I see no other way. If you run other Add-Ins turn them off to see which one may interfere as Excel does in my knowledge not refresh the ribbon by itself (unless you perform actions on the workbook). Have you tried to set the “EnableEvents” setting already?
      @3: These alerts are triggered by the Excel core and you have no control over it.

      Comment by dutchgemini — August 23, 2012 @ 9:12 am | Reply

  3. What I Have detected so far:
    The same core functionality that makes your window turn light blue also tries to invalidate the ribbon and by that all callback for getvisible or getLabel ar executed. But because the application was in the “not responsive” state each one fails and for each you get a blank error message. Now I had the following idear (and it works).
    If all your ribbon customization is in one ribbon tab, add a getVisible callback to that tab. This callback should always return true. So in normal situations this callback has no effect. But in the exact situation stated above with al those blank error, then first the getVisible callback of your ribbon tab is executed and fails (and you get a blank error). By that the tab is made invisible and no other callbacks are done, so you just end up with one single blank error, while as long as the application is working normaly no changes are experienced.
    WHen the time intensive function is ended you just call ribbon.invalidate and your ribbon tab appear again. (still if your application has not reached the not responsive state the ribbon invalidet ahs no visual effect (as is what you want).

    Hope this helps a little elimination all but one blank errors

    Comment by John S — August 25, 2012 @ 8:57 pm | Reply

    • It is Windows detecting that Excel is in a non-responsive state and making it look light blue, not Excel which, being non-responsive, is not in the position to do it.

      Glad you found some sort of solution or workaround to mitigate the problem. If was guessing whether making your tab(s) invisible before you go for the CPU-intensive would eliminate the last warning as well.

      Dutch

      Comment by dutchgemini — August 27, 2012 @ 8:20 am | Reply

  4. yes that would be true, but would also disturb the user when the task would not take long. So because only now and then the task is cpu intensive I decided to live with that one blank error.

    Comment by John S — August 28, 2012 @ 2:10 pm | Reply

    • Makes sense.

      I don’t know if this came out of my post, but keep the DoEvents to the absolute necessary. I have seen that too many DoEvents also appear to trigger the empty messages.

      Comment by dutchgemini — August 28, 2012 @ 2:41 pm | Reply

  5. Hi,
    I would be interested in your solution for the Separator getvisible issue, Can’t seem to get it to work?
    Cheers
    Juls

    Comment by Juls — February 7, 2013 @ 5:50 pm | Reply

    • The Separators always remain visible even if you tell them to “hide”, they do not work.

      As I wrote in “Issue 5”, I have chosen to avoid separators completely and am using Label items as substitutes. Labels can be hidden easily.

      Each of such items is made out 6 “unbreakable” spaces that mimic a sufficiently large gap (you can play around increasing or decreasing this number to suit your taste). I tried using other characters, such as the pipe “|” but none of them worked decently or were horrible to see.

      Comment by dutchgemini — February 7, 2013 @ 6:15 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: