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


Pride Dispensed, Pride Promised, And Pride To Go







Just got back from London yesterday after the UK
Excel Conference

A successful couple of days I think, we had a large audience,
few no-shows, and a general good time.

As this was organised mainly to avoid disappointing the many
wait-listed people from the previous, April, conference, the agenda was largely
the same as before. We had one new session on ‘Excel as a BI Tool’, which I
think that many delegates found enlightening; to see Excel being used as an
Enterprise reporting tool, querying huge amounts of data very quickly was an

It was good to see the presenters again, and we had a good first evening prior to the conference – at least until Roger persuaded me to have a pint of Timothy Taylor’s which was not nice, especially after the Pride!

As would be expected with an audience of circa 100 people,
we had a mixed bunch. There were some company men/women, some consultants; bankers,
auditors, and tax inspectors; a guy who works for Apple; we even had a pig
consultant – who would have thought that a pig consultant is a big Excel user.

Another good dinner at Zsi-Zsi’s with some of the delegates;
a decent Chardonnay at Ha-Ha’s’ on the last evening; a good meal at a most
unexpected looking Indian restaurant in Victoria that Simon, Andy and I went
to; and good hosting from Microsoft.

 To cap it all, I missed the last train home because I was too busy talking with Simon, I got on the wrong tube line.



Class Conscious








This is going to be the first in a series of items about Code Generation, automatically
generating your code. I have posted a couple of times previously on this topic,
and here,
but I thought a series of posts would be useful, so here is the first.

The Last One

I remember back in the 80s a UK company built a program
called ‘The Last One’,
its premise being that it was the last computer program because it would write
every program from thereon after. Now, after you have picked yourself up off of
the floor and stopped laughing, think about it somewhat objectively. Remember,
this was in the days of largely batch programs, they started at the beginning,
went through a file of instructions or updates, and reacted to each item
encountered. As such, with some very clear rules, it shouldn’t be too hard to
write a program to read those rules and then process the data files according
to those rules. Of course, it would all soon get messy, the types of rules are
infinite, and the types of data is also infinite, so some severe constraints
would have to be imposed, but wouldn’t a program that can handle multiple
applications, even if those applications have to adapt somewhat to that
program, be beneficial (of course, you could say this is exactly the same as
the SAP paradigm, but please, let’s
not talk about SAP when I am feeling good). Event driven applications add a
whole new, more difficult dimension of course, but still…

Automate It

I am a great fan of code generation, using it frequently,
and always look for further opportunities to use it. Why do I think it is so
good? There are many reasons:

  • maintenance
    is reduced, make a small change and rerun and all code affected by that
    change will be re-generated
  • the
    quality is improved, all changes are re-generated, no need to remember
    what needs changing
  • the
    work is done in the more critical and interesting areas, in analysis and

To me that third point is the most important. Code
generation makes you think about the application requirements and how they should be implemented, something you should do by right, but something that
cannot be avoided or done half-heartedly when using  code generation. As the design is so
critical, and as I find it more interesting than cutting code, this suits me

The greatest exponent of code generation that I am aware of
is Kathleen Dollard.
Her book, Code
Generation in Microsft .NET
, is the definitive work on the subject, a
remarkably comprehensive work that provide end-to-end solutions using
XSLT-based code generation based upon a series of templates. As she says … Code generation will turbo-charge your
development cycles by offering speed, reusability, agility, and consistency

Whilst this is a remarkable piece of work, I think that many
might find it daunting, even off-putting. The solution is comprehensive, but it
is based upon .NET and XML, and dare I say, can even been too purist for the
tyros amongst us. For this reason, I will address code generation from a much
simpler starting point, show how you can use code generation today using Excel,
and build upon it over the next few weeks,

Excel & Code Generation

What I have described so far are full-blown applications,
with their attendant complexity, which might discourage you from going any
further.  But let’s step back a bit, and
think about ways in which we can generate code in small doses. Later items will
go further, but we can show immediate benefits. In these examples, we will use
Excel as the code generating engine. Excel is a superb tool for this, as it is
with so many other things, with the grid for our data input, and VBA to handle
any complex code generating algorithms.

You may not be aware of it, but maybe you are already using
Excel to do code generation. If you ever have a list in Excel, and use formulas
and functions to create some more descriptive text that you cut and paste into
a code module, that is a simple form of code generation.

Let’s take a simple example. I use class modules a lot, and
I find that creating properties is a pain, you need to define a private
variable where the actual value is stored, and then Get and Let properties so
that the property can be read from and written to in the class (that is assuming two
things, your property is read/write, they may not all be so; that you don’t
use Public variables for read/write properties – I don’t, I always declare Get
and Let).

Using code generation, all we need are two pieces of
information, the property name and its data type. If we want to cater for some
properties being read only, we need a third denoting the access mode.

So, we can create a spreadsheet with 3 columns

column A – property name – free format text

column B – data type – Long, Double, String,
Boolean, Range (should suffice for now) – an ideal candidate for a data
validation list

column  C
– access mode – R, W, or RW – again another dv list

It would be wise to add a fourth, column D, derived column,
that takes the property name and replaces any spaces with _, and removes any
special characters, as this will be used in the class.

With a few simple formulae, we can easily generate all 3
elements of the property that the class needs,


=”Private m_”&D2&” As “&D2


“Public Property Get “&$D2&”() As ”
    “&$D2&” =
m_”&$D2&CHAR(10)&”End Property”,

Write Property

“Public Property Let “&$D2&”() As ”
”   m_ “&$D2&” =
“&$D2&CHAR(10)&”End Property”,


“Public Property Let “&$D2&”(ByVal val As ”
”    m_”&$D2&” =
val”&CHAR(10)&”End Property”,


That is all there is to it. Create a list of properties with
their attributes, and Excel generates the class code for you, all you need to
do is cut and paste it into the class module.

Next time, we will show how to use VBA to write back this
data into the class code, after all, one of the primary aims of code generation
is to save us from the boring bits. We will also look at  a slightly more complex generation.


RSS subscribers, this code is attached.