Category Archives: 11525

The XML Is On The Kid















I recently wrote about a database tool that I have built to facilitate adding stored procedures to databases, SP Builder.


 


XL-Dennis made a couple of comments, one of which was with regards to the medium used for the script file. He talked about XML files, and whilst I had used an INI file when I originally built it I had thought about using XML but dismissed it at the time as my XML skills and knowledge were minimal to put it mildly (oddly, I have been aware of and using XML for over 12 years now, mainly as a consumer of such, but I still feel it promises far more than it delivers as a technology).


 


XL-Dennis’ comment gave me the prod I needed to actually do something about this, so I set about converting my tool to using XML files.


 


This is the structure of the XML I designed, using the same data as my previous posts.


 


<?xml version=”1.0″ encoding=”utf-8″?>
<database type=”Access”
         path=”C:\MyDatabases\”
         name=”MyDB.mdb”>

   <category name=”Get”>
 
       <procedure name=”spGetCompanyGoals”>
       
           <SQL code=”SELECT SUM(RD.SalesGoal) AS ‘Company Sales Goal’, ” />
           <SQL code=”      SUM(RD.BonusGoal) AS ‘Company Bonus Goal’ ” />
           <SQL code=”FROM refUsers AS RU ” />
           <SQL code=”WHERE LoginID = prmLoginId;” />
       </procedure>
 
       … more procedures
   </category>
 
   <category name=”Delete”>
 
        <procedure name=”spDeleteSalesType”>
 
           <parameter name=”prmUpdatedBy”
                      type=”VarChar (50)” />
 
           <parameter name=”prmSalesTypeID”
                      type=”Integer” />
           
           <SQL code=”UPDATE  refSalesTypes               ” />
           <SQL code=”SET     Deleted = TRUE,             ” />
           <SQL code=”        UpdatedBy = prmUpdatedBy,   ” />
           <SQL code=”        UpdatedOn = Now>             ” />
           <SQL code=”WHERE   SalesTypeID = prmSalesTypeID;” />
       </procedure>
 
       … more procedures
 
       … more categories
   </category>
</database>


 


There are some distinct advantages to the XML to my mind. These are:


  • it is more readable
  • there is less meta-information, such as the various counts, because the XML parser will provide all of that to the code
  • the XML parser provides a level of validatio
  • tools such as Altova XMLSpy provide a much better IDE for creating and updating these files than a text editor, as well as validation

 


There are some disadvantages of course, but the only one that really irks me is having to use &amp; for & and so on. I understand why I have to, but it still rankles a tad.


 


In the XML design, there were a couple of decisions to be made, around the elements and attributes. Before I embarked upon the design I read the chapter on XML in the second edition of the indispensable Professional Excel Development, and whilst it suggested encapsulating the data in elements rather than attributes I chose to use attributes as I felt it was more readable that way, attributes still need to be within a parent element which loses clarity. In my mind, this


 


           <parameter name=”prmSalesTypeID”
                      type=”Integer” />


 


is far simpler than this


 


           <parameter>
               <name>prmSalesTypeID</name>
               <type>Integer</type>
           </parameter>


 


Changing the addin was trivial once I had worked out the code for reading the XML file, updating the INI file to my XML format took far more time. The XML version ofthe addin can de downloaded here.


 

 


SP Builder is supplied as an Excel 2003 addin, or XLA file, and adds a menu option to the Tools menu with three options, Build SPs, SP Builder List and About.





 


As you many know, when Excel 2003 workbooks that create commandbars are opened in Excel 2007 or 2010 the menus are accessed via the Addins tab. This is not a satisfactory solution, so the addin tests the Excel version, and if it is 2003 or earlier it builds the commandbars, it is 2007 or 2010 it loads a ‘light’ 2007/2010 addin that adds a group to the Developer tab.



 





This way, we have a single addin that runs in any version of Excel.


 


 


One thing that I found was that I could not insert comments in my file, the parser failed when I had comments. I used what I believe is the correct format for comments, that is


<!—
‘———————–
‘ Check Stored Procedures
‘———————–
–>


but it only worked when I completely stripped out the comments.


 


I also created an XSD file for the XML, which I used to play with reading it into Excel, but I see no real use for Excel in this process other than hosting the code, so that is going nowhere. Is there any other use I can use the XSD for? This also points at the next step, take Excel out of the process completely and create a standalone VB application; that would make a nice candidate for me to develop some more .Net skills.


 


 


 

The Kid Grows Up

In a previous post, I talked about building stored procedures (SPs)in an Access database, and calling the same from Excel using ADO.


As I mentioned in that post, I am not a fan of the Access GUI. Whilst GUIs can be okay for doing some simple testing, checking whether something works or, I find it far easier to build a script when I need to do similar things over and over (such as building all of the SPs for an application). I am an inveterate scripter (see Autogen’ed Ribbon Code and XML Is Such A Pain); rather than build the stored procedures using the Access GUI, I much prefer to build a script file that can be rerun at any time. This is very much in line with my preferences to autogen as much as possible, and also with me development methodology, where I prefer to allocate design time before ploughing into the functional code.


In the post mentioned above, I said that … you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. This post will cover such a creator app.


In this app, I have a script file that defines all of the SPs, and the application just reads that file and builds the SPs defined therein. I have used an INI file as my SP definition file; I like the flexibility of INI files, the format does not have to be too rigid, and they are easily segmented, and easily read (via code).


The format of my file is as follows


 1[spb_App]
 2
 3   [spb_DB]
 4
 5       DBType=Access ;could be SQL Server or any other DB
 6       DBPath=C:\MyDatabases\
 7       DBName=MyDB.mdb
 8
 9   [spb_Stored_Procs]
10
11       TypeCount=7
12
13       I001=Get
14       I002=Check
15       I003=Insert
16       I004=List
17       I005=Query
18       I006=Table
19       I007=Update
20       I008=Delete
21
22;———————–
23; Get Stored Procedures
24;———————–
25       [spb_SP_Get]
26
27           SPCount=7
28
29;spGetCompanyGoals
30           [spb_SP_Get_001]
31
32               SPName=spGetCompanyGoals
33
34               [spb_SP_Get_001_Parameters]
35
36                   ParameterCount=1
37
38                  [spb_SP_Get_003_Parameters_001]
39
40                      ParamName=prmLoginID
41                      ParamDataType=VarChar (50)
42
43               [spb_SP_Get_001_SQL]
44
45                   SQLLineCount=4
46
47                   Line001=SELECT SUM(SalesGoal) AS ‘Company Sales Goal’,
48                   Line002=      SUM(BonusGoal) AS ‘Company Bonus Goal’
49                   Line003=FROM refUsers
50                   Line004=WHERE LoginID = prmLoginId;
51etc.


 


Line 1 isn’t actually used, it is just for completeness.


Lines 3-7 define the database, the type and location. Note that the type is to allow for building SPs in different databases, although we will just discuss Access.


Lines 9-20 define the SP categories, I do this so as to break up the SPs and keep them grouped, for easier maintenance. The Type (Get, Check, etc.) is used as part of the section id for the SP details, as in lines 20, 30, 34, and 38.


Line 27 defines how many SPs are in that group, used as a loop index in the code.


Line 32 is the SP name, used in the code to Drop the SP then Create it anew.


Lines 34-41 defines the parameters. As you can see, there is a ParameterCount specifying how many parameters the SP uses. A definition for each parameter, if applicable, follows, with an incrementing suffix index so that the app can extract each in turn.


Lines 43-50 define the SP code, with a SQLLineCount defining how many lines of SQL are within the SP. In the example above, the SP is very simple, but of course SPs of any complexity can be built.


Lines 29-50 are repeated for each SP within that category.


Lines 22-50 are repeated for each category of SPs.


The category names are not relevant, it can be any name and any number, as long as the sections match up.


 


Care has to be taken that the definitions are consistent, the category id is correct, the SP index is carried through, the parameter name in the parameter definition is the same as the parameter in the SQL code., and so on.


 


The SP Builder addin can be downloaded from here. It is unprotected, so the code can be examined, updated as you see fit.


 


I use this technique for all of my databases, so I have a script file for each, and can easily recreate the database code. As I mentioned, by creating a script file it helps in better design, thinking about the code required rather than diving into the GUI and building as required. This technique could be extended to creating the database, building the tables etc. I have a separate app for this, but have not combined them as I find myself creating the SPs far more often than the database, I find it more convenient to keep as separate applications.

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.

Autogen’ed Ribbon Code





Normal

0





false

false

false















MicrosoftInternetExplorer4
















In a previous post, XML Is Such A Pain, I showed the technique that I use to
simplify the creation of XML code for custom ribbons.


This is a handy technique, as it means that creating the XML
is very simple, it is more or less a cut-and-paste job, and I spend very little
time thereafter  with the XML, which I
like as the tools for XML in Excel 2007 are not great.


As much as I like this technique, it still requires a lot of
VBA code. Whilst VBA is a great deal easier as a development tool than anything
we have with XML, there are many areas where errors can creep in, both
compilation and runtime errors.


In the old world of commandbars, I used a table driven
method of generating the commandbars. It is possible to use a similar technique
with Excel 2007, many such have been demonstrated.


I prefer to generate the code, from a similar setup table. This
way I can generate all of the code components; it only needs to be run when
anything changes; and I can view the code that is generated. I like code
generation.


In the technique that I will demonstrate here, I generate
all of the required code from the setup table


  • the ribbon XML
  • the VBA callback procedures
  • VBA constants used in the callback procedures.

In addition, the VBA code is automatically inserted into the
target workbook. As of yet, the XML is not automatically added as the workbook
is open at the time, but it should be possible to extend the technique to write
the XML file.


As in the previous post, all of the code shown here relates
to the following custom ribbon tab.




In that earlier post, you can view all of the XML and VBA
code, so it will not be repeated here, but rather this post will cover the
generation of that code. The autogeneration code here only caters for button
controls, but it can easily be extended for other controls such as menus,
toggles, and so on.


Autogen Setup Table


The table used to drive the autogeneration is structured as
follows:


  • Type – a simple Data Validation list of tab, group, separator, or button
  • Tag – an id for the control that is used to generate unique control ids
  • Tag Upper – an upper case copy of Tag
  • Group – the Tag Upper value of the current group
  • Separator – the Tag Upper of the previous separator within this group (if applicable)
  • Group/Sep/Tag – concatenation of Group & Separator & Tag Upper

=SUBSTITUTE(D6&”_”&E6&”_”&C6,”__”,”_”)


  • Control Id – a derived id for this control

=IF(A3=”group”,”grp”,IF(A3=”button”,”btn”,IF(A3=”separator”,”sep”,A3)))&$B$2&IF(A3=”button”,PROPER(E3),””)&B3


  • Caption – a value to assign as the control label
  • Procedure – the OnAction procedure name
  • Image File – the idMso of the required image
  • Image Size – a simple Data Validation list of small or large
  • Screentip – a value to assign as the control screentip
  • Supertip – a value to assign as the control supertip
  • Keytip – a value to assign as the control Keyttip

The example shown above consists of


  • a tab control
  • a group control
  • two button controls within that group
  • a second group control
  • a third button control within the second group

The table for this example is shown below. I have transposed
it (columns to rows) in order to show it on this page, but that aside, you
should get the principle.



Autogen Overview


The autogeneration works in the following way:


  • read the setup table and create a staging array of details
  • initialise the constants array, setting up on oversized array
  • initialise the output XML array with the namespace etc.
  • initialise the VBA code output arrays with function signature (a public ribbon variable and an onLoad callback procedure)
  • read the staging array and create items for each line of XML code
  • create output arrays for
    • procedure VBA code lines
    • label VBA code lines
    • image VBA code lines
    • image size VBA code lines
    • screentip VBA code lines
    • supertip VBA code lines
    • keytip VBA code lines
  • create constants arrays for
    • procedure ids
    • label VBA ids
    • image ids
    • image size ids
    • screentip ids
    • supertip ids
    • keytip ids
  • finalise the output XML array with the closing tags, and write the array to a text file (this is an area to be improved)
  • finalise the global constants output arrays by resizing the arrays to the appropriate size and write the arrays to a code module
  • finalise the VBA code output arrays by adding closing function statements, resizing the arrays, and write the arrays to a code module
  • write a procedure to the code module to invalidate the ribbon

In its present release, the XML code is written to a text
file. It needs to be copied to the custom UI file, either by unzipping the
file, then opening the customUI.xml file and replacing the XML within there, or by
using the CustomUI editor to replace the XML.


There is nothing particularly illuminating in this code, it
is a very straight-forward loop, depending entirely upon the setup table being
correctly formatted. It should be noted that some control types do not create
all of the array data, for instance a separator does not create constants or
VBA code, tabs do not create image code and so on.


Considerations


As this technique writes VBA code on the fly, trusted access
to VBA has to be granted, but this is only required in the development
environment, if a workbook that this technique is applied to is given to a
colleague, that colleague does not to grant trusted access.


This was a relatively simple project to build, there were
not many problems encountered. The oddest involved the image size. In the XML,
the image size is normal or large, so you would naturally assume that the value
to be assigned in the callback procedure was normal or large. My original
attempt at the code assigned these values, but the images always showed as
normal size, I couldn’t get large images. Finally, I contacted Ken Puls a co-author on the RibbonX
book, which was and is my main reference in working with the Ribbon. Ken’s book
seemed to suggest that normal and large was the correct values to use in the
callback procedures, but he also found that this wasn’t working. Luckily he
found the values to use, which are 0 and 1. Ken has blogged this bug here.


Code Writing Code


The code that writes the VBA code on the fly is given below.
There is not much code as there is no decision logic required in this code, the
setup code creates complete output lines, apart from a test as to whether the
code is to be written new or appended.


Option Explicit
Option Private Module

Public Enum ModuleTypes
vbext_ct_StdModule = 1
vbext_ct_ClassModule = 2
vbext_ct_MSForm = 3
End Enum

Public Function WriteCode( _
ByVal CodeArray As Variant, _
ByVal Module As String, _
Optional ByVal Append As Boolean)
Dim mpModule As Object
Dim mpInsertLine As Long
Dim i As Long

On Error Resume Next
Set mpModule = ActiveWorkbook.VBProject.VBComponents(Module)
On Error GoTo 0

If Not Append Then

If Not mpModule Is Nothing Then ActiveWorkbook.VBProject.VBComponents.Remove mpModule
Set mpModule = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
mpModule.Name = Module

mpModule.CodeModule.InsertLines 1, _
"'== Generated Cube Formula Module " & _
Format(Date, "dd mmm yyyy") & " " & Format(Time(), "hh:mm:ss") & _
" By " & Environ("UserName") & _
" on " & Environ("ComputerName") & vbCrLf & _
"'== " & vbCrLf & _
" "
End If

With mpModule.CodeModule

mpInsertLine = .CountOfLines + 2
For i = LBound(CodeArray) To UBound(CodeArray)

.InsertLines mpInsertLine, CodeArray(i)
mpInsertLine = mpInsertLine + 1
Next i
End With

End Function

Example File


For RSS subscribers, you can download a zip file containing the Autogen addin, and the example file shown above.


 

XML Is Such A Pain

































































Normal































0































































































false































false































false













































































































































































































































Creating a custom ribbon tab in Excel 2007 is a well known technique by now, and relatively easy to achieve.  I will not go into the details here, there are many examples scattered across the web, I would suggest Ron de Bruin’s pages at http://www.rondebruin.nl/ribbon.htm.


The biggest pain for me with custom ribbon code is the XML. As an example, to create a custom ribbon tab such as this



requires the following XML code


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

<tabs>
<tab id="tabDemo" getLabel="Ribbon Demo" insertAfterMso="TabHome">

<group id="grpDemoGroup1" getLabel="Formatting Group">

<button id="btnDemoButton1"
label="Currency"
image="DollarSign"
imageSize="Large"
screentip="Button 1"
superTip="Format activecell as currency"
keytip=""
onAction="procButton1" />

<button id="btnDemoButton2"
label="Percentage"
image="PercentSign"
imageSize="Large"
screentip="Button 2"
supertip="Format activecell as percent"
keytip=""
onAction="procButton2" />
</group>

<group id="Text Group" getLabel="rxDemoGetLabel">

<button id="btnDemoButton3"
label="Text Wrap"
getImage="WrapText"
getSize="Large"
getScreentip="Button 3"
getSupertip="Format activecell with text wrap"
getKeytip=""
onAction=" procButton " />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

I have used all of the attributes in this example for the buttons, as I prefer to be explicit, not leave to defaults. 


So far, this piece of XML is not too bad. But I find the maintenance of this XML extremely tedious, adding more controls and/or changing any of the attributes of these controls can be long winded and extremely error prone. The tools for creating and maintaining the XML are very limited, the best is the CustomUI Editor, but this suffers from having no recent used file list, and has an irritating habit of moving the changed line to the bottom of the window (whoever thought that was a good idea?). Maintaining the XML involves switching between the CustomUI Editor and Excel 2007, and you have to close down the Excel file in between. 


Because of this, I have adopted the practice of not putting any of the values within the XML, but rather I use callback code to set the values. As an example, instead a line such as 


label="Text Wrap"

I would setup the XML as follows 


getLabel="rxDemoGetLabel"

and within my Excel file I would have a procedure that loads that label, such as


Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)

Select Case control.Id
Case "tabDemo": returnedVal = "Ribbon Demo"
End Select
End Function

I do this for all labels, and the other attributes Image, ImageSize and so on (onAction has to be a callback of course). Using this technique, the XML code now looks like


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

<tabs>
<tab id="tabDemo" getLabel="rxDemoGetLabel" insertAfterMso="TabHome">

<group id="grpDemoGroup1" getLabel=" rxDemoGetLabel ">

<button id="btnDemoButton1"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />

<button id="btnDemoButton2"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />
</group>

<group id="grpDemoGroup2" getLabel="rxDemoGetLabel">

<button id="btnDemoButton3"
getLabel="rxDemoGetLabel"
getImage="rxDemoGetImage"
getSize="rxDemoGetImageSize"
getScreentip="rxDemoGetScreenTip"
getKeytip="rxDemoGetKeyTip"
onAction="rxDemoGetAction" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>

This means that I need to change the XML code far less frequently, if I wish to change a label it is all done in code, and so on. Adding a new button or group requires adding to the XML, but the additional code would be very similar to the existing code, just the control type and its id need specific values, so the maintenance is very simple.


This does mean that we need code to set the attribute values. In the rxDemoGetLabel example above, the tab’s label is assigned. As we have more controls with more labels assigned, we need more code to manage them, but this is simple extra case statements. For example


Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = "Ribbon Demo"
Case "grpDemoGroup1": returnedVal = "Formatting Group"
Case "btnDemoButton1": returnedVal = "Currency"
Case "btnDemoButton2": returnedVal = "Percentage"
Case "grpDemoGroup2": returnedVal = "Text Group"
Case "btnDemoButton3": returnedVal = "Text Wrap"
End Select
End Function

As we can see, the code is very simple, very maintainable. The other callbacks would be very similar.


One other thing that I do is to create a set of global constants for all of the values, again to make maintenance simpler. For the labels I would have the following set of constants


 


Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

And the callback code would be


Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = LABEL_DEMO
Case "grpDemoGroup1": returnedVal = LABEL_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = LABEL_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = LABEL_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = LABEL_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = LABEL_GROUP2_BUTTON3
End Select
End Function

I find this a lot easier to setup, and much simpler to maintain. It does mean that I have to invalidate the ribbon so as to refresh the attributes, but this is done on opening the file, and should not be noticeable. For info, this is the code I add to invalidate the ribbon


Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
Set mgrxDemoIRibbonUI = ribbon
Call RibbonSetup
End Function

Public Function RibbonSetup()
mgrxDemoIRibbonUI.Invalidate
End Function

The full code for the constants is shown below


Option Explicit

Global Const PROC_GROUP1_BUTTON1 As String = "procButton1"
Global Const PROC_GROUP1_BUTTON2 As String = "procButton2"
Global Const PROC_GROUP2_BUTTON3 As String = "procButton3"

Global Const LABEL_DEMO As String = "Ribbon Demo"
Global Const LABEL_GROUP1_GROUP1 As String = "Formatting Group"
Global Const LABEL_GROUP1_BUTTON1 As String = "Currency"
Global Const LABEL_GROUP1_BUTTON2 As String = "Percentage"
Global Const LABEL_GROUP2_GROUP2 As String = "Text Group"
Global Const LABEL_GROUP2_BUTTON3 As String = "Text Wrap"

Global Const IMAGE_GROUP1_GROUP1 As String = ""
Global Const IMAGE_GROUP1_BUTTON1 As String = "DollarSign"
Global Const IMAGE_GROUP1_BUTTON2 As String = "PercentSign"
Global Const IMAGE_GROUP2_GROUP2 As String = ""
Global Const IMAGE_GROUP2_BUTTON3 As String = "WrapText"

Global Const IMAGESIZE_GROUP1_GROUP1 As String = "0"
Global Const IMAGESIZE_GROUP1_BUTTON1 As String = "1"
Global Const IMAGESIZE_GROUP1_BUTTON2 As String = "1"
Global Const IMAGESIZE_GROUP2_GROUP2 As String = "0"
Global Const IMAGESIZE_GROUP2_BUTTON3 As String = "1"

Global Const SCREENTIP_GROUP1_GROUP1 As String = ""
Global Const SCREENTIP_GROUP1_BUTTON1 As String = "Button1 "
Global Const SCREENTIP_GROUP1_BUTTON2 As String = "Button 2"
Global Const SCREENTIP_GROUP2_GROUP2 As String = ""
Global Const SCREENTIP_GROUP2_BUTTON3 As String = "Button 3"

Global Const SUPERTIP_GROUP1_GROUP1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON1 As String = ""
Global Const SUPERTIP_GROUP1_BUTTON2 As String = ""
Global Const SUPERTIP_GROUP2_GROUP2 As String = ""
Global Const SUPERTIP_GROUP2_BUTTON3 As String = ""

Global Const KEYTIP_GROUP1_GROUP1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON1 As String = ""
Global Const KEYTIP_GROUP1_BUTTON2 As String = ""
Global Const KEYTIP_GROUP2_GROUP2 As String = ""
Global Const KEYTIP_GROUP2_BUTTON3 As String = ""

and for the callback code


Option Explicit
Option Private Module

'-----------------------------------------------------------------
' Publicly exposed callbacks
'-----------------------------------------------------------------
' No values are set in the XML apart from ids, all labels, images,
' tooltips and actions are assigned in the callback code
'-----------------------------------------------------------------

Public mgrxDemoIRibbonUI As IRibbonUI

Public Function rxDemoRibbonOnLoad(ribbon As IRibbonUI)
Set mgrxDemoIRibbonUI = ribbon
Call RibbonSetup
End Function

Public Function rxDemoGetAction(control As IRibbonControl)
Select Case control.Id
Case "btnDemoButton1": Application.Run PROC_GROUP1_BUTTON1
Case "btnDemoButton2": Application.Run PROC_GROUP1_BUTTON2
Case "btnDemoButton3": Application.Run PROC_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetLabel(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "tabDemo": returnedVal = LABEL_DEMO
Case "grpDemoGroup1": returnedVal = LABEL_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = LABEL_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = LABEL_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = LABEL_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = LABEL_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetImage(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "btnDemoButton1": returnedVal = IMAGE_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = IMAGE_GROUP1_BUTTON2
Case "btnDemoButton3": returnedVal = IMAGE_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetImageSize(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "btnDemoButton1": returnedVal = IMAGESIZE_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = IMAGESIZE_GROUP1_BUTTON2
Case "btnDemoButton3": returnedVal = IMAGESIZE_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetScreentip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = SCREENTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = SCREENTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = SCREENTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = SCREENTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = SCREENTIP_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetSupertip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = SUPERTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = SUPERTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = SUPERTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = SUPERTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = SUPERTIP_GROUP2_BUTTON3
End Select
End Function

Public Function rxDemoGetKeytip(control As IRibbonControl, ByRef returnedVal)
Select Case control.Id
Case "grpDemoGroup1": returnedVal = KEYTIP_GROUP1_GROUP1
Case "btnDemoButton1": returnedVal = KEYTIP_GROUP1_BUTTON1
Case "btnDemoButton2": returnedVal = KEYTIP_GROUP1_BUTTON2
Case "grpDemoGroup2": returnedVal = KEYTIP_GROUP2_GROUP2
Case "btnDemoButton3": returnedVal = KEYTIP_GROUP2_BUTTON3
End Select
End Function

Public Function RibbonSetup()
mgrxDemoIRibbonUI.Invalidate
End Function

I think you can see this is a simpler method than holding the attribute values in the XML, all of the working code is in one place, and it makes for easier setup and maintenance.


This overall approach requires the setting of all of the values for the global constants and the code. In a future post I will describe how I have automated this.