Coding Hurts, But the Ribbon Breaks Your Heart













Normal


0


false











false


false


false





EN-US


X-NONE


X-NONE









































MicrosoftInternetExplorer4



















































































































































































































































































































































































































































































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.

My 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 me.


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=http://schemas.microsoft.com/office/2006/01/customui>


    <ribbon>


        <tabs>


            <tab idMso=TabHome>


                <group id=rxgrpInvalid label=Invalid Chars>


 


                    <dynamicMenu id=rxdynInvalid


                                 label=Dir List


                                 size=large


                                 imageMso=SmartArtOrganizationChartRightHanging


                                 getContent=rxGetContent />


 


                    <button id=rxbtnRefresh


                            label=Refresh


                            imageMso=HighImportance


                            size=large


                            onAction=rxGetButtonAction“/>


                 </group>


            </tab>


        </tabs>


    </ribbon>


</customUI>


 


Option Explicit

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

    Stop
    Set mpDialog = Application.FileDialog(FileDialogType:=msoFileDialogFolderPicker)
    With mpDialog
       
        .AllowMultiSelect = False
        If .Show = -1 Then

            Set FSO = CreateObject(“Scripting.FileSystemObject”)
            Set TemplateFolder = FSO.GetFolder(.SelectedItems(1))

            XML = “<menu xmlns=””http://schemas.microsoft.com/office/2006/01/customui””>” & vbNewLine
            XML = XML & XMLFiles(TemplateFolder, True)
            Content = XML & “</menu>”

            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”
            rxIRibbonUI.InvalidateControl (“rxdynInvalid”)

        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 file
        If Not Left(file.Name, 2) = “~$” Then

           Select Case True

                Case file.Type Like “*Microsoft*Excel*”

                    cntFiles = cntFiles + 1
                    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 Folder.subFolders

        cntDiv = cntDiv + 1
        thisXML = thisXML & “<menuSeparator id=””div” & cntDiv & “””/> ” & vbNewLine & _
                  “<dynamicMenu ” & vbNewLine & _
                      id=””rxdmnuSub” & ClearId(subFolder.Name) & “”” ” & vbNewLine & _
                      label=””” & subFolder.Name & “”” ” & vbNewLine & _
                      imageMso=””FileOpen”” ” & vbNewLine & _
                      getContent=””rxGetContent”” ” & 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 = CreateObject(“VBScript.RegExp”)
    RegEx.Pattern = “[\ \\/:\*\?""<>\|]“
    RegEx.Global = True
    ClearId = RegEx.Replace(Id, “”)
    Set RegEx = Nothing
End Function


 

Pride Dispensed, Pride Promised, And Pride To Go










Normal


0








false


false


false























MicrosoftInternetExplorer4




















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 eye-opener.

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

































Normal















0















































false















false















false































































































































MicrosoftInternetExplorer4


































































































 

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

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

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
Variable

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

-          Read
Property

=IF(ISNUMBER(SEARCH(“R”,$C2)),
“Public Property Get “&$D2&”() As ” &$B2&CHAR(10)&
    “&$D2&” = m_”&$D2&CHAR(10)&”End Property”,
“”)

-          Write Property


=IF(ISNUMBER(SEARCH(“W”,$C2)),
“Public Property Let “&$D2&”() As ” &$B2&CHAR(10)&
”   m_ “&$D2&” = “&$D2&CHAR(10)&”End Property”,
“”)


Correction 


=IF(ISNUMBER(SEARCH(“W”,$C2)),
“Public Property Let “&$D2&”(ByVal val As ” &$B2&”)”&CHAR(10)&
”    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.


 


For RSS subscribers, this code is attached.