We help IT Professionals succeed at work.

Conditional compilation of customUI ribbon customisation for Office application [VBA] add-ins on Windows/Mac

93 Views
Last Modified: 2020-08-25
I have an application add-in (written in VBA) that is designed to work on both Windows and Mac version of Microsoft 365 (Office apps).

One part of it customises the built-in ribbon UI via the customUI XML part.

Some control names either don't exist on Mac or have different names and/or control types. For example, Insert / SmartArt is a button control on Windows and a control control on Mac!

That present the need for a conditional approach to writing the customUI XML for example:

If Mac Then
    <button id="ABC"/>
Else
    <button id="XZY"/>
End If

Does such an approach exist?

The alternative is to maintain two copies of the add-in, each with the same VBA code but with different customUI files. Yuck!
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Yes sir, if you are talking about the VBA code, then it's nearly what you laid out, slight syntax change, see below.  And here are the compiler defined constants you can trigger off.

EDIT : However if you are talking about a file that the VBA code reads in, then that's a different story...  I wasn't familiar with custom ribbons details, but I see now that you are asking about the XML file, so I guess this isn't going to solve that.  But I won't delete it in case there's any usefulness to it.

Compiler constants (VBA) | Microsoft Docs

#If Mac Then
    <button id="ABC"/>
#Else
    <button id="XZY"/>
#End If

Open in new window

In addition you can set your own too, and I often do that for early versus late binding, making it easy to swithch, since when I'm developing I prefer early binding, but for release I often prefer late binding.  Here's some example code related to that.

#Const EarlyBinding = False

' Global variables
#If EarlyBinding Then
Dim FSO As Scripting.FileSystemObject
#Else
Dim FSO As Object
#End If

    ' Set Outlook Application object (early binding)
#If EarlyBinding Then
    Dim objOutlook As Outlook.Application
#Else
    Dim objOutlook As Object
#End If
    Set objOutlook = CreateObject("Outlook.Application")

    ' Set Email object (early binding)
#If EarlyBinding Then
    Dim objEmail As Outlook.MailItem
#Else
    Dim objEmail As Object
    Const olMailItem = 0
#End If
    Set objEmail = objOutlook.CreateItem(olMailItem)

Open in new window


»bp
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Thanks Bill. It's a VBA add-in and as you correctly identified, what I'm looking for is the equivalent If...Then...Else construct in the customUI XML.

For example, if I want to hide the SmartArt control, this is how to do it in the customUI XML on Windows:

<button idMso="SmartArtInsert" visible="0"/>

However, it's not a button control on Mac but a menu control so that line has to change to this:

<menu idMso="SmartArtInsert" visible="0"/>

Is there a way to have a common VBA add-in with conditional customUI XML to deal with this difference between PC/Mac?
John KorchokProduction Manager
CERTIFIED EXPERT

Commented:
Probably not. There's almost no link between VBA and OOXML. .NET is cross-platform now, so in VB or C#, you could theoretically use the Open XML SDK for this. That's a serious learning curve.

OOXML does have conditional statements, but they're only used in SmartArt AFAIK. It's fairly similar to XSLT, but I don't know that PowerPoint would parse it in a context other than SmartArt:
<choose>
    <!-- Toggle the direction of the linear flow on reverse diagram between starting
    from the left (first case) and starting from the right (second case) -->
    <if func="var" arg="dir" op="equ" val="norm">
        <alg type="lin" />
    </if>
    <else>
        <alg type="lin">
            <param type="linDir" val="fromR" />
        </alg>
    </else>
</choose>
Another stumbling block is there's no parameter in OOXML for the operating system.

Probably an unintended consequence, but this design decision makes it impossible to add custom SmartArt files to a Mac installation. The Mac menu is preset to the default SmartArt files and can't add new files like the dialog in Windows. Maybe an MVP could raise this issue with MS ;-)
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Thanks John. I've read this a dozen times now and am amazed at your insight! I'll have a chat with one of the MVPs ;-)
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
AFAIK, the Office Ribbon does not support conditional statements in its XML definition.
One solution can be to define 2 ribbons (one for Windows, one for Mac) and programmatically set the active one (using conditional compile directives, as mentionned by Bill).

As for handling events, a generic approach can save you a lot of time:
1) Write a common event handler for everyone (or for as much ribbon controls as possible).
2) Call a function, that will according to the control ID (or whatever discrimining attribute) that will dispatch the events to a specialized function.
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Thanks Fabrice. If there was a way to dynamically load a customUI XML file based on the target platform, that would solve my problem. I don't think there is such a mechanism for VBA so it looks like there is no solution other than to maintain two copies of the add-in source file, which is a pain.

Regarding your second point, this is the mechanism I use in all of my add-ins, the structure being like this:

Public Sub CallbackXYZ(control As IRibbonControl)
   Select Case control.Id
      Case "MyControl1": Call Procedure1
      Case "MyControl2": Call Procedure2
      Case "MyControl3": Call Procedure3
      Case Else: Debug.Print "CallbackXYZ not handled for " & control.Id
   End Select
End Sub
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Public Sub CallbackXYZ(control As IRibbonControl)
   Select Case control.Id
      Case "MyControl1": Call Procedure1
      Case "MyControl2": Call Procedure2
      Case "MyControl3": Call Procedure3
      Case Else: Debug.Print "CallbackXYZ not handled for " & control.Id
   End Select
End Sub
That's the idea.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Jamie,

Have you looked at all at "customizing" the ribbon object at runtime, to handle the few things that are platform specific?  I don't have experience with custom ribbons, but in looking at the question and doing a little education to reduce my ignorance, I saw some references that seemed to indicate when the XML is loaded some VBA can be executed, and there is an IRibbonUI object that represents the stuff loaded from the XML file.  Just brain storming, but not sure if you could access that object and add/change the things that are different?


»bp
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Hi Bill. Thanks for the steer! I already use the IRibbonUI object in most of my add-ins. That allows me to use one of it's only 6 methods to activate tabs and invalidate controls. The Office library also has IRibbonControl (which I also use to manage callbacks from the ribbon to the VBA code). The last ribbon-related object in that library is the IRibbonExtensibility object and it has just a single method (no properties) which is GetCustomUI. It's functional description looks like it could be an answer:

Loads the XML markup, either from an XML customization file or from XML markup embedded in the procedure, that customizes the Ribbon user interface.

But the Microsoft documentation shows a C# example and I've never manged to figure out how/if it's possible to use this with VBA. I would love to fill this gap in my knowledge!
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Hmmm. This Microsoft article states that the IRibbonExtensibility interface is for COM add-ins only. If that's the case, why can I see it in the VBA Object Browser?
John KorchokProduction Manager
CERTIFIED EXPERT

Commented:
Microsoft documentation! This looks like the miscellaneous section, where they threw in all the odd bits.
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
From my knowledge, only one custom ribbon can be defined (for Excel).
On the other hand, you can toggle control's visibility, or use contextual controls to give the illusion of a dynamic ribbon.
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
That's the conclusion I'm coming to Fabrice, unless you use a COM add-in and the GetCustomUI method of the IRibbonExtensibility object (which does not appear to be available for VBA). I can't use COM as this is PC+Mac add-in and I think I now need to maintain two versions of the pptm/ppam (where the only difference is the customUI XML as I have a common code base) to deal with this crazy situation:

<!-- PC -->
<button idMso="SmartArtInsert"/>

<!-- Mac -->
<menu idMso="SmartArtInsert"/>
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
@Jamie Garroch,

Are you all set with this now, or do you need more help?  If all set, could you please close it out now.  If you need help with the question close process take a look at:



»bp
Jamie Garroch (MVP)PowerPoint Technical Consultant
CERTIFIED EXPERT

Author

Commented:
Hi Bill. I think the answer is that there is no solution so I'm not sure how to close this out based on that conclusion.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Understand Jamie.  I think there was some potentially useful discussion in this question, so for the benefit of future potential viewers I wouldn't suggest deleting the question.

But sometimes "there was no solution" is indeed the solution / answer.

I would suggest that if you're willing you take a few minutes and just add a closing comment to this question, indicating that based on expert suggestions and your own activities there doesn't seem to be a way to solve the original question / need.  Then close the question selecting your summary comment as the solution, and if you so desire toss some recognition at other helpful comments in the thread (if you think there were any).


»bp
PowerPoint Technical Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.