Coding Hurts, But the Ribbon Breaks Your Heart











I was
building a ribbon this week (yes, I can’t get enough of them), when I cam across
what is a really stupid problem.

application is building a cascading dynamic menu , a list of all files in the
selected directory, and all sub-directories in the parent directory are added
as sub-dynamic menus (creating the cascade when they are clicked).

No sweat I
thought, just use FSO to get the files and the folders, and string the XML
together dynamically … I’ve done it a hundred times, it will be a breeze.

Problem was
that on testing, I kept getting a rather unhelpful message like the following

It is most
unhelpful as the XML is being built dynamically, so it is not easy to see the
string (you need to debug  the code and output
the string!).

This wasn’t
happening every time, just sometimes. Because it was in an application with
other functionality, I decided to build a test workbook focussing
specifically on the problem.

I built a
workbook with a single dynamic menu control. That control invoked code that
queried for a directory, and then builds the XML for the files and the
first-level of sub-directories.

One thing
to note, the crux of the problem, is that I use the tag property to hold the
full file or directory path, as this is intended to be a technique to open
these files, or trigger another dynamic list for the sub-directory.

It turned
out to be that when the directory name includes an & (ampersand) character,
and we load the tag property with that value, the XML complains.

It also
turns out that you cannot load the tag attribute with such a directory name in
the CustomUI editor.

I know I
can replace these characters with some other unique string before loading, but
then I would need to decipher it before using it. I feel madness rushing up on

I am sure
there is some excuse, I mean valid reason, that is all down to the way that XML
handles these characters, but … it is just text and the tag attribute is just a
string value; an & is perfectly allowable within a directory name, so this
strikes me as being nuts.

This is the
custom XML and code in my test workbook

<customUI xmlns=>



            <tab idMso=TabHome>

                <group id=rxgrpInvalid label=Invalid Chars>


                    <dynamicMenu id=rxdynInvalid

                                 label=Dir List



                                 getContent=rxGetContent />


                    <button id=rxbtnRefresh












Private rxIRibbonUI As IRibbonUI

Global cntDiv As Long

Sub rxIRibbonUI_onLoad(ribbon As IRibbonUI)
    Set rxIRibbonUI = ribbon
End Sub

Public Sub rxGetContent(control As IRibbonControl, ByRef Content)
Dim mpDialog As FileDialog
Dim FSO As Object
Dim TemplateFolder As Object
Dim XML As String

    Set mpDialog =
    With mpDialog
        .AllowMultiSelect = False
        If .Show = -1 Then

            Set FSO =
            Set TemplateFolder =

            XML = “<menu
& vbNewLine
            XML = XML &
XMLFiles(TemplateFolder, True)
            Content = XML &

            Set TemplateFolder = Nothing
            Set FSO = Nothing
        End If
    End With

    Set mpDialog = Nothing

End Sub

Public Sub rxGetButtonAction(control As IRibbonControl)
    Select Case control.Id

        Case “rxbtnRefresh”

        Case Else
            If Left$(control.Id, 9) =
“rxbtnFile” Then Workbooks.Open (control.Tag)
    End Select
End Sub

Public Function XMLFiles( _
    ByRef Folder As Object, _
    ByVal TopMenu As Boolean) As String
Dim thisXML As String
Dim subFolder As Object
Dim file As Object
Dim cntFiles As Long

    For Each file In Folder.Files

        ‘Check if file is a temporary
        If Not Left(file.Name, 2) =
“~$” Then

           Select Case True

                Case file.Type Like

                    cntFiles = cntFiles +
                    thisXML = thisXML
& “<button ” & vbNewLine & _
                                  id=””rxbtnFile” & cntDiv
& cntFiles & “”” ” & vbNewLine & _
                                  label=””” & file.Name
& “”” ” & vbNewLine & _
imageMso=””FileSaveAsExcel97_2003″” ” &
vbNewLine & _
onAction=””rxGetButtonAction”” ” &
vbNewLine & _
                                  tag=””” & file.Path
& “””/>” & vbNewLine
           End Select
        End If
    Next file

    If cntFiles = 0 Then

        ‘Create a “No
Templates” button if no applicable files found
        thisXML = thisXML &
“<button ” & vbNewLine & _
                      id=””rxbtnNoFiles” &
cntDiv & “0”” ” & vbNewLine & _
                      label=””No Files
Found””/>” & vbNewLine
    End If

    For Each subFolder In

        cntDiv = cntDiv + 1
thisXML = thisXML & “<menuSeparator id=””div”
& cntDiv & “””/> ” & vbNewLine & _
” & vbNewLine & _
id=””rxdmnuSub” & ClearId(subFolder.Name) &
“”” ” & vbNewLine & _
                      label=””” &
subFolder.Name & “”” ” & vbNewLine & _
” & vbNewLine & _
” & vbNewLine & _
                      tag=””” & subFolder.Path
& “””/>” & vbNewLine
    Next subfolder

    XMLFiles = thisXML

End Function

Private Function ClearId(ByVal Id As String) As String
Dim RegEx As Object
    Set RegEx =
    RegEx.Pattern = “[\
    RegEx.Global = True
    ClearId = RegEx.Replace(Id,
    Set RegEx = Nothing
End Function


5 thoughts on “Coding Hurts, But the Ribbon Breaks Your Heart

  1. Not having done any XML coding, I have an ignorant question: why

    rather than


    ? Are there still be problems with special chars between start and end tags?

  2. Hi Harlan,

    The dynamicMenu is a “special” (for lack of a better term) element that needs to be passed all of it’s XML the way Bob specified. At runtime it is executed and essentially turned into something like this:

    Why? You’d have to ask MS.

    Bob, I’ve run into this issue as well. I believe that if you replace & with & it should work. The challenge is that the & sign is a symbol to XML to interpret something.

    Iirc, I’ve also had to substitute the | for \ on occassion as well. It’s a pain as you have to encode/decode the path, and can end up running the risk that something doesn’t decode properly.

  3. Hi Bob,

    You may adapt this to your needs, I used it to create valid url’s, which seem to have similar problems:

    Const sCriticalchars As String = “$&+,/:;=?@ “”<>#%{}|\^~[]`”
    Const sReplacementCodes As String = “%24,%26,%2B,%2C,%2F,%3A,%3B,%3D,%3F,%40,%20,%22,%3C,%3E,%23,%25,%7B,%7D,%7C,%5C,%5E,%7E,%5B,%5D,%60”

    Function MakeProperData(sData As String) As String
    Dim lCount As Long
    Dim vReplacementCodes As Variant
    Dim sNewData As String
    vReplacementCodes = Split(sReplacementCodes, “,”)
    sNewData = “”
    For lCount = 1 To Len(sData)
    If InStr(sCriticalchars, Mid(sData, lCount, 1)) > 0 Then
    sNewData = sNewData & vReplacementCodes(InStr(sCriticalchars, Mid(sData, lCount, 1)) – 1)
    sNewData = sNewData & Mid(sData, lCount, 1)
    End If
    MakeProperData = sNewData
    End Function

    Function MakeProperDataReverse(sData As String) As String
    Dim lCount As Long
    Dim vReplacementCodes As Variant
    Dim sNewData As String
    vReplacementCodes = Split(sReplacementCodes, “,”)
    sNewData = sData
    For lCount = 0 To UBound(vReplacementCodes)
    sNewData = Replace(sNewData, vReplacementCodes(lCount), Mid(sCriticalchars, lCount + 1, 1))
    MakeProperDataReverse = sNewData
    End Function

  4. Ken,

    I read Harlan’s question as much more fundamental than that. I think he was asking why does some stuff get declared as attributes, and some as content.

    For instance, instead of

    Where everything is declared as an attribute, why not


    I must admit I have always struggled with this ever since I first came across XML, and indeed early on tried to make everything content and came a real cropper.

    I came to the conclusion that each XML element only had one content item, so everything else was declared as attributes, but then you look at MS’ implementation, and the dynamicMenu and button elements have no content! I admit, I don’t get it fully.

    I agree with your conclusion on encode/decode though, that seems the only solution, but you have to know which are problemmatical. But I still have to ask why. The & is part of textual content, not any of the identifying element, so XML should treat it as text. XML is supposed to make it simple to share a textual data structure, this is anything but easy.

Leave a Reply

Your email address will not be published. Required fields are marked *