Category Archives: 11519

Cracking The Code

Excel’s ability to run pivot tables against an OLAP cube in Analysis Services is a truly wondrous beast. The ease of use; volumes of data; the filtering; all of these provide a rich environment for users to easily inspect their data and get real information out. Add to this that Microsoft seem to believe that pivot tables are a winner and keep improving it, access to cubes and slicers being some of the latest significant changes, I can see no reason for all serious Excel users not to get to grips with this technology.

In addition to all of this goodness, it is possible to build a table query against the cube using the cube formulae. Sometimes this might be the appropriate way to present your information. But cube formulae are hard, the syntax is pure MDX, and long-winded. For example, using the MDX cube from the SQL Server MDX Step By step book that I used in my last post, a cube formula to get the Reseller Sales Amounts for Cleaner products in 2003 within the US is

=CUBEVALUE(“MDX SBS SBS”,”[Date].[Calendar].[Calendar Year].[CY 2003]”,”[Product].[Product Categories].[Subcategory].[Cleaners]”,”[Measures].[Reseller Sales Amount]”,”[Geography].[Geography].[Country].&[United States]”)

Phew! That is hard work. Imagine repeating that for say years by product category, maybe a 5×20 table of values.

I tend to create simple macros that add lists of available members to a dropdown in the ribbon, a user can pick a business oriented item from this list, and it updates the activecell formula to add the appropriate MDX. This way, a user has a simple way to build the cube formulae, and will see the MDX so they can learn if they are so inclined.

In addition, we can be smarter and simplify the formulae, because each row would use the same product sub-category, each column would use the same calendar year. We can use the CUBEMEMBER function to get the row and column heading values, such as

=CUBEMEMBER(“MDX SBS SBS”,”[Date].[Calendar].[Calendar Year].[CY 2003]”)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,”[Product].[Product Categories].[Subcategory].[Cleaners]”,”[Measures].[Reseller Sales Amount]”,”[Geography].[Geography].[Country].&[United States]”)

Similarly, we can use CUBEMEMBER to get the Cleaners member

=CUBEMEMBER(“MDX SBS SBS”,”[Product].[Product Categories].[Subcategory].[Cleaners]”)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,$H6,”[Measures].[Reseller Sales Amount]”,”[Geography].[Geography].[Country].&[United States]”)

Finally, the US member

=CUBEMEMBER(“MDX SBS SBS”,”[Geography].[Geography].[Country].&[United States]”)

and use that within the value formula,

=CUBEVALUE(“MDX SBS SBS”,I$5,$H6,”[Measures].[Reseller Sales Amount]”,$H$3)

So far, so good, but how are we supposed to know what those members are, and how to get the full MDX syntax of the member items? Of course, if we know the cube structure intimately and know MDX syntax thoroughly, we have no problems, but most users are not as informed.

Yet again, Excel comes to our rescue, in that it will tell us the cube formulae.

If we take the pivot table that we showed in the last post again, a table of product sub-categories over calendar years for a particular region, Figure 1, which just happens to match up to the example above, we can demonstrate how

Figure 1

If we go to the PivotTable Tools context ribbon, and on the Options tab, Tools group there is an OLAP Tools dropdown. The final item on this dropdown is ‘Convert to Formulas’, Figure 2, which will change the pivot table to a table of cube formulae.

Figure 2

When you select this option, you will get the dialog shown in Figure 3 which allows you to keep any report filters or convert them.

Figure 3

In this example, I retained the report filters, and the result was a table as shown in Figure 4.

Figure 4

If we examine this table, we can see the cube formulae that generate the table, Figure 5 highlighted shows a particular cube member, and the MDX that is required. This could be the end result, we might just use the table as presented, or it can be used as a learning tool, to familiarise with the cube and the MDX syntax.

Figure 5

Whilst this is a very handy trick for getting to understand the MDX required for generating the cube formulae, it is on an individual cell basis. The pivot table itself does not pull all of this data back on a cell by cell basis, it issues a far more complex MDX set query that pulls back all of the data specified in your pivot table. To see this code would be very useful in gaining better MDX understanding, so how can we do this?

There is nothing built into Excel to provide this, but there is a free addin at CodePlex called OLAP PivotTable Extensions which adds a pivot table context menu item. One of the extensions is to show the MDX code. If you right click within the pivot table, you should see the new context menu item, as in Figure 6.

Figure 6

This displays the following dialog, Figure 7, where you can see the MDX code on the MDX tab.

Figure 7

As you can see at first sight, this is quite scary, but most of it is a list of cells being returned, such as [Product].[Product Categories].[Product].[List Price]. Cut and paste it to a text editor and you should be able to format it and make it more readable, helping to understand what it is doing.

One thing to note in this query, as in most pivot table queries off a cube, is that is uses the NON EMPTY clause in the selects. What this effectively does is to outsort any of the member combinations along a particular axis that have nothing but empty cells. At first sight this seems like a good idea, don’t clog up the query with a whole bunch of rows where there is no data. But there is a potential downside to this. What if you want to see some of those empty rows? As in the case where some of your sales team might have targets and some don’t (for instance, customer salesmen as against store salesmen). In this case, you would want to see their empty rows because you would want to know that they are not hitting those targets, but you would not want to see the empty rows for the store sales staff. In an MDX query in SMS you would use the Exists function instead of NON EMPTY, I have yet to find out how to do the same in a pivot table.

Who Scratched My Table?

One of the better additions to Excel 2007 are, in my view, the functionality added to tables

I was recently working on a 2007 project and I was looking to add some highlighting using conditional formatting. That will be easy thought I, but as usual, I was way off the mark.

Formatting The Table

For the sake of clarity, I will not use the actual formatting that I was trying, but a far simpler case.

Figure 1 shows an example of a typical table in Excel 2007.

Figure 1

Let’s assume that I want to show those rows where the Ship Date has not yet been set, i.e. are showing TBA.

Using conditional formatting, I can easily do that by selecting B2:E8, and then adding a formula of


and adding an appropriate fill colour.

To demonstrate this, Figure 2 shows the formatted table.

Figure 2

Using Structured References

But hey, this data is in a table, and one of the great features of tables is structured references. So I decided that I would use structured references in my conditional formatting.

As a demonstration of  structured references, Figure 3 shows the table with my CF formula off-table.

Figure 3

As you can see, you reference the table, names tblShipping here, and the column, not each individual cell.

So clearly, I just have to replace the first CF formula with cell references with this new formula with structured references, Figure 4.

That is what I did, and to my utter surprise, I got the following error.

Figure 4

As an aside, I did click Help, but I won’t bore you with the details on the lack of help that provided, just suffice to say that it was to Excel 2007’s usual standard.

But what can the error be? There is nothing wrong with the formula as Figure 3 shows.

One thing did occur to me. My testing formula was not part of the table, it was outwith the table, whereas CF is part of the table (perhaps, who knows?). If you add that formula to the column next to the table, the table expands to encompass that column, Figure 5.

Figure 5

Although I used exactly the same formula as before, when the table incorporated the column with that formula, it dropped the table reference, clearly it is superfluous, or implicit.

Armed with all of this, I decided to try that version of the formula in my CF. I still got an error, but a different error

Figure 6


This suggests to me that and formatting added by conditional formatting is not ‘part of the table’. And because it is part of the table, it does not work, you cannot use structured references in CF.

I tried a few variations of the formula, mainly in desperation, but none of these worked either. One interesting aside, when I tried to use a formula with an explicit intersection within the formula embedded in the table, it didn’t strip off the table name, I had to use

tblShipping[[#This Row],[Ship Date]])=”TBA”

Yet another oddity.


I have written before about the implementation of conditional formatting in Excel 2007, in Conditional Formatting In Excel 2007 – Chav or Mini?, and  Open Office Is Looking Good. From these posts, you should be clear that I am far from impressed with conditional formatting in Excel 2007, and unfortunately this looks like another piece of poor implementation.

It seems that you can conditionally format a table in Excel 2007, but you cannot use structured references in that CF. Okay, so you can use normal CF, but big deal! Why can’t we use structured references when applying CF to a table? To my mind, not being able to is a total nonsense, another CF car crash piling up on the others.

I did also try and use a structured reference in CF on cells not in the table, still no joy.

If anyone knows how structured reference can be, should be, used, I would love to hear it.


Deploy Me Simple

Excel 2003 Addins In Excel 2007

Excel 2007 and 2010 is so different in concept to Excel 2003 and before that there are many new challenges in providing solutions that can be deployed in either version.

One challenge that I have been faced with is creating addins that may be used in Excel 2007 or earlier versions. My addins would usually have a menu and/or a toolbar to provide access to the functionality within the addin. In previous versions of Excel, this has been an elegant solution as most people are familiar with the menus and are easily able to find the new functions in the Excel 2003 UI.

As with many things, Excel 2007 changed all of that. Excel 2007 supports the old commandbar model, but in a particularly inelegant way. The menus and toolbars that an addin might create are dropped onto the Add-Ins
tab. Figure 1 shows my Excel 2007, with two addins installed, NameManager1 and Snagit2

Figure 1 – Addins Tab

Figure 2 – NameManager Menu

As I said, this is not an elegant solution, far better to adopt the Ribbon paradigm and either add a ribbon tab, or add a group to an existing tab.

The problem here is that whilst Excel 2007 can handle commandbars, Excel 2003 doesn’t understand the ribbon. The obvious solution is to have two versions of your addin, one that is Excel 203 compliant, one for Excel 2007.  This is far from ideal, having to maintain two versions where 90+% of the code will be the same is a maintenance nightmare. Another alternative is to put all of the common code into a DLL, which would still be multiple files and might entail a large amount of work re-writing the code to manage an Excel instance.

The solution that I am proposing here is to have two addins, but two very different addins.

Overall Design

I will be using one of my addins in the discussion that follows. This addin, called ‘SP Builder3, is a small addin with a simple menu that will usefully demonstrate this technique without confusing the matter with too much detail.  The 2003 menu for ‘SP Builder’ is shown in Figure 3


Figure 3 – SP Builder Menu

Figure 4 shows the ribbon version of ‘SP Builder’. As you can see, I have added a three button group to the Developer tab in the ribbon.

Figure 4 – SP Builder Ribbon

The functionality of each addin is as follows:-

  • Excel 2003 – functionally unchanged, apart from modifying the workbook open code to test the Excel version, if it is 2003 or lower the commandbars are loaded, if not the Excel 2007 workbook is opened. The workbook close code deletes the commandbars or closes the Excel 2007 addin
  • Excel 2007 – a basic addin that has ribbon XML. The callback code invokes the actual procedures in the Excel 2003 addin. When this addin loads, the ribbon is automatically built, and, because the Excel 2003 addin checks the version, the coomandbars are not loaded so we have nothing in the Add-Ins tab.

Excel 2003 Addin

As mentioned above, the only changes to the Excel 2003 addin are the open and close routines.

The workbook open checks the current Excel version and either creates the commandbars, or loads the Excel 2007 workbook, depending upon the version, as shown in Figure 5.

     If Val(Application.Version) < 12 Then

         If Not mhCommandBars.CreateMenu(False) Then Err.Raise appErrorGeneric

         Workbooks.Open ThisWorkbook.Path & Application.PathSeparator & WB_RIBBON
     End If


Figure 5 – Addin Initialisation

Similarly, the workbook close changes are shown in Figure 6.

     If Val(Application.Version)< 12 Then

         Call mhCommandBars.DeleteMenu

         Workbooks(WB_RIBBON).Close SaveChanges:=False
     End If

Figure 6 – Addin Removal

That is all that is required in the Excel 2003 addin, as you can see this adds no maintenance overhead.

Excel 2007 Addin

The Excel 2007 addin is completely new, but again there is nothing difficult or overly complex in this code. It is basically a wrapper to present the ribbon to the user, it has no real functional purpose in itself.

First we will look at the XML required to define the ribbon changes. Figure 7 shows the XML.

The XML here is very straight-forward, a single group with three buttons. It is likely that your addin will have a richer interface which will require more XML but that is not the objective of this article. As you can see, the group is added to the Developer tab. This is because I see the SP Builder addin as a development tool, but your addin could be in any tab, or even add another tab to the ribbon. One other thing to note is that all of the buttons call the same callback routine, this is explained later.

<customUI xmlns=


  <ribbon startFromScratch=false>


      <tab idMso=TabDeveloper>

        <group id=grpSPBuilder label=SP Builder>


          <button id=btnSPBuilder

                  label=Build SPs



                  onAction=rxspOnAction />


          <button id=btnSPList

                  label=List SPs



                  onAction=rxspOnAction />


          <button id=btnSPAbout




                  onAction=”rxspOnAction />






Figure 7 – Ribbon XML

In my Excel 2007 ribbon handling code, I always create a set of constants for the ids of each of the controls, as shown in Figure 8, which are then used in the callback code.

Option Explicit

Global Const CONTROL_ID_BUILD As String = “btnSPBuilder”

Global Const CONTROL_ID_LIST As String = “btnSPList”

Global Const CONTROL_ID_ABOUT As String = “btnSPAbout”

Figure 8 – Ribbon Constants

The final piece of this jigsaw is the ribbon callback code, Figure 9. As said earlier, I use a single callback and test the control id to determine the action to take. The key aspect of this code is that the action for each of the buttons is to run the ‘real’ procedure in the Excel 2003 addin, which is achieved with Application.Run4.

Option Explicit

Global rxspIRibbon As IRibbonUI

Public Sub rxspOnAction(control As IRibbonControl)

    Select Case control.ID

        Case CONTROL_ID_BUILD:  Application.Run “‘SP Builder.xla’!SPBuild”

        Case CONTROL_ID_LIST:   Application.Run “‘SP Builder.xla’!SPList”

        Case CONTROL_ID_ABOUT:  Application.Run “‘SP Builder.xla’!AboutSPBuilder”5

    End Select

End Sub

Public Sub rxspRibbonOnLoad(ribbon As IRibbonUI)

    Set rxspIRibbon = ribbon

End Sub

Figure 9 – Ribbon Callback Code


This provides a simple, and elegant, way to manage addins that are required to be deployed in Excel 2007 or earlier versions.

By using a ribbon wrapper to deliver the ribbon changes, we are keeping the code to a minimum, and it is not introducing a maintenance overhead.

1There is a 2007 version of NameManager which I believe has a ribbon, but I have not installed it

2 Interestingly, Snagit has not added to the ribbon although the product has adopted the Ribbon UI!

3 Although t is not material to this discussion, ‘SP Builder’ is a small addin that simplifies building stored procedures and loading them into a database

4 Although you cannot pass parameters directly in a callback, this technique also allows passing a paremeter to the 2003 procedure. This could be useful to let the ‘worker’ addin know that you are running from Excel 2007 if it wanted to do something differently in that instance

5 It would be a far better practice to create global constants with the called addin and procedure details rather that repeating this in hardcoded form, similar to how I manage the control Ids

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


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


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”

   <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;” />
       … more procedures
   <category name=”Delete”>
        <procedure name=”spDeleteSalesType”>
           <parameter name=”prmUpdatedBy”
                      type=”VarChar (50)” />
           <parameter name=”prmSalesTypeID”
           <SQL code=”UPDATE  refSalesTypes               ” />
           <SQL code=”SET     Deleted = TRUE,             ” />
           <SQL code=”        UpdatedBy = prmUpdatedBy,   ” />
           <SQL code=”        UpdatedOn = Now>             ” />
           <SQL code=”WHERE   SalesTypeID = prmSalesTypeID;” />
       … more procedures
       … more categories


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




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.




Cycling Through The Fog

Dynamic Analysis

Excel 2007 provides
the capability to have a pivot table connected to an OLAP cube. Furthermore,
detailed analysis can be built by using various CUBE functions. One of the
great things about Excel 2007 pivots and CUBE functions is that you can use a
value from the pivot as a member value within a CUBE function. For instance, a CUBEVALUE might be getting the net gas
sales for a particular company and period with


Company].[Company Drilldown].&[17]”,

                       “[Activity Date].[Date


If you also
have a pivot that is filtering the company drilldown, the current selection can
be substituted in that formula and achieve the same results,


                       ‘Pivot Sheet’!B10,

Date].[Date Drilldown].[Month].&[1]&[1]&[2009]”),


this gives us a dynamic capability, our analysis can reflect the currently
selected pivot value(s).

Next/Previous Function

I have a
spreadsheet that has built a comprehensive analysis using this technique.  This all works well, but I was looking for
another useful function. I needed to be able to provide Next/Previous buttons
that would enable cycling the analysis through each of the top-level members of
the Company Drilldown without the user having to go back to the pivot and
reselect the Next/Previous item. The Company Drilldown was a PageField in my

As far as I
am aware, there is no built-in way to get the next or previous item in a pivot
filter, this would have been the most obvious and simplest way, but nt to be.

My next
thought was that if I could somehow obtain the MDX for the each item, I could
create a CUBEMEMBER function for
that item, and point all of my CUBEVALUE
functions at that cube member. But how to get the MDX?

My first
thought was to query the cube directly using the drilldown dimension, but that
would get the values, but not the MDX.

must be somewhere within the pivot, otherwise I cannot see how a reference to
the pivot field works when embedded in a CUBE function, but with all of the
delving into the properties of the pivot object, I could not see where.

Retrieveing PageField members

I did
figure out how to get details of all members of the PageField by iterating
through the cube as in the example below.


Function IterateCubeToGetMembers()
Dim mpConn As ADODB.Connection
Dim mpCat As ADOMD.Catalog
Dim mpDim As ADOMD.Dimension
Dim mpHier As ADOMD.Hierarchy
Dim mpLevel As ADOMD.Level
Dim mpMember As ADOMD.Member
Dim mpData As Range
Dim mpCell As Range
Dim i As Long, j As Long

    Set mpData =
    Set mpConn = New Connection
“Provider=MSOLAP;” & _
Catalog=<;” OLAP_SERVER_NAME & _
Source”, OLAP_DB_NAME)
    Set mpCat = New ADOMD.Catalog

    With mpCat.CubeDefs(OLAP_CUBE_NAME)

        ReDim appVecHierarchies(1 To
        i = 0

        For Each mpCell In

            Set mpDim =
.Dimensions(“[Company Drilldown]”)
            Set mpHier =
mpDim.Hierarchies(mpCell.Offset(0, 1).Value)
            Set mpLevel =
mpHier.Levels(mpCell.Offset(0, 2).Value)
            ReDim appVecMembers(1 To
            j = 0

            For Each mpMember In mpLevel.Members

                j = j + 1
                appVecMembers(j) =
            Next mpMember

            i = i + 1
            appVecHierarchies(i) =
        Next mpCell
    End With

    Set mpDim = Nothing
    Set mpHier = Nothing
    Set mpLevel = Nothing
    Set mpMember = Nothing
    Set mpCat = Nothing
    Set mpConn = Nothing
    Set mpData = Nothing
End Function


Note, mpData.Columns(1).Cells
is a list of different dimensions that I load into the PageField depending upon
user selection.

This is a
nice technique, but still only gets me the member values, it does not get the
associated MDX.

Getting The MDX

Somewhere along
my investigations, I recalled seeing that the pivotitem was showing the MDX in
the SourceName property.

Getting the
PivotItems on a PageField was problematical, so I couldn’t use that directly.
But what if the drilldown dimension was not a PageField, but was in the rows
area for example?

I am not at liberty to change the original pivot, but I could create another
one, load the drilldown dimension into the rows area, then loop through the PivotItems
in PivotField(1) (we set it up with just the single pivot field). These PivotItems
have the MDX in the SourceName property.

Final Solution

following function builds arrays of the member and MDX values. It does it by

a sheet

up the pivot table

the dimensions one by one into the Rows area

all of the PivotItem values.


function LoadHierarchies()
CONST PIVOT_TEMP as string = “pvtTemp”
Dim appVecHierarchies As Variant
Dim appVecMembers As Variant
Dim mpSheet As Worksheet
Dim mpData As Range
Dim mpCell As Range
Dim mpPivotItem As PivotItem
Dim i As Long, j As Long

    Set mpSheet =
    ThisWorkbook.PivotCaches _
        .Create(SourceType:=xlExternal, _
SourceData:=ThisWorkbook.Connections(CONNECTION), _
Version:=xlPivotTableVersion12) _
TableDestination:=mpSheet.Name & “!R5C4″, _
TableName:=PIVOT_TEMP, _

    Set mpData =
    ReDim appVecHierarchies(1 To
    For Each mpCell In mpData.Cells
the next value in the pivot rows
                .Orientation = xlRowField
                .Position = 1
            End With

            With .PivotFields(1)

                j = 0
                ReDim appVecMembers(1 To
.PivotItems.Count, 1 To 2)
                For Each mpPivotItem In

                    j = j + 1
                    appVecMembers(j, 1) =
                    appVecMembers(j, 2) =
                Next mpPivotItem

‘we save the member details as an array within an array
                i = i + 1
                appVecHierarchies(i) =
            End With

‘remove the pivotfield ready for next
.CubeFields(mpCell.Value).Orientation = xlHidden
        End With
    Next mpCell


    Set mpPivotItem = Nothing
    Set mpSheet = Nothing
    Set mpCell = Nothing
    Set mpData = Nothing
End Function

appVecHierarchies and appVecMembers are global
arrays for storing the member values and MDX.

One Oddity

On a slight
tangent, I found one oddity in this technique.

When the
company drilldown was loaded into Page area, all of the companies in the cube showed
up. When loaded into the Rows area, only some of the companies showed. This was
due to the fact that some of the companies had no associated data.


I have a
solution, but it is slow to load, and I cannot say I am happy with it.

It seems to
me that the MDX must be held somewhere in the pivot so that the link from cube
functions work, but I cannot find it.

I would
welcome any thoughts on this, a better technique, or anything else.


OpenOffice Is Looking Good




/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
mso-pagination:widow-orphan lines-together;
mso-fareast-font-family:”Times New Roman”;
mso-bidi-font-family:”Times New Roman”;}
a:link, span.MsoHyperlink
a:visited, span.MsoHyperlinkFollowed
@page Section1
{size:595.3pt 841.9pt;
margin:2.0cm 2.0cm 2.0cm 2.0cm;

/* Style Definitions */
{mso-style-name:”Table Normal”;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
font-family:”Times New Roman”;

I have blogged previously
about what a rubbish job MS have done with Conditional Formatting in Excel
2007, but it seems it is worse than I feared.

In this same file I now had CF setup as I wanted, after much
trials and tribulations, but I needed to insert a new column, but the CF was
required to be different than the CF on either side.

First thing I tried was to simply insert the column. Of
course, this meant that it inherited the CF from the column to the left, which
was not what I wanted, so I opened the CF dialog and thought I would change the
range that the rule applies to from those two columns to just my newly inserted
column, and change that rule. This I did, but it wiped out the rule entirely
from the other column. And to compound the problem, it cleared the format fill
colour from the newly inserted column, and when I changed the rule and added
back the fill colour, every cell in my range showed the colour, even though
only a single cell matched the condition being tested for.

So I tried another approach. I created a column off the data
area, setup the CF exactly as I wanted for that column, then added an index
number in the first free row so that I could sort the columns into my required
order. For some unknown reason, this removed my CF, gave it the CF of the
column to its (new) left, and also managed to setup data validation as was
applied to its (new)O column to the right. Genius! I knew what I wanted to do,
but the designers at MS decide they know better, in two ways.

So how did I fix it? I first deleted the CF from the column
that is already present, then inserted my new column. I then re-instated the CF
that was in the previous column, and added the CF in my new column. Not a great
chore, but I SHOULDN’T HAVE TO FIGHT THE SOFTWARE to do what I want to
do. And you can bet that the next time I need to do it, I will have forgotten,
and I will go through all this pain again.

Whilst I am having a good rant, when are MS going to default CF (and Data
Validation) edit boxes to update, so as to avoid accidentally inserting rubbish
when you hit the arrow keys. I can’t recall ever thinking the default was

Ribbons – Gain Without The Pain







If you are in the habit of building or customising Excel 2007
Ribbons, there is an indispensable new tool that has just hit the market. Andy
has released his RibbonX Visual Designer, which can be found here.


I have blogged previously
about how I find cutting XML tedious, and the inadequate tools available, so
this is a more than useful addition to make ribbon design and coding simpler. It provides a
GUI to facilitate the Ribbon build, simple point and click rather than the tedious
line coding. The big advantage of this approach is that you can see the hierarchy
of your ribbon, and visualise what it will finally look like. To make it even
better, there is a preview option, which will build a workbook and inject the
XML so that you can ACTUALLY SEE what it will look like.


The tool adds a group to the Developer ribbon tab (so make
sure that you make this visible)

and here is a screenshot of the designer, with a few example controls added.


In addition, you can load an existing workbook, and amend
its ribbon. It has many other goodies which I won’t list here, check out the
link above to read about them all.


As I said, a great tool, and at a good price – free!


Conditional Formatting In Excel 2007 – Chav or Mini?

Yesterday I had an incredibly frustrating time.

I was updating my RibbonX Autogen routine to handle more types of controls. This is driven from a worksheet, some columns of which are generated, some of which depend upon user input. So being a helpful sort of guy, I have decided to shade the input cells as a visual aid. But in some circumstances, an input cell may not be required (for instance, a group does not require an image, so suggesting that it does by shading is a tad misleading). Being the helpful sort that I alluded to earlier, I decided that I would use conditional formatting, to test those conditions, and remove the shading where appropriate. Which is where I came up against the MS designers, who seem to me to live in a land far from reality, totally unaware of what people really do.

Okay, we all know that conditional formatting was limited, 3 conditions was never enough, and that the CF dialog was rubbish, but why didn’t they just address those issues? Why re-design the whole way it works? Don’t MS realise that evolution is just as valid an approach as revolution, change the things that really requirie changing first, see how it gets used, and then gradually build upon that improve. In other words, let the real users have some influence.

The first problem that I came across was the new behaviour whereby if you create a condition, and then paste the format of another cell over the top, another cell that also has CF, the target cell adds those format conditions. No options, you can’t say you don’t want to add, it just does it. Of course, unless you are aware of this, you may find your cells reacting to a differenet condition, and you then start wondering why the CF no longer works.

Next, I added some CF conditions to a number of rows over 2 columns. That was fine until I decided that I wanted one of the columns to have an extra test in the condition. So I selected the cells in that column, and edited the rule to include the new test. Lo and behold, the other column also adopted these tests. When you set conditional formatting on a range, Excel remembers which cells are using that rule, and so change the rule in any cell and they all get the change. Again, no choice that I can see, I can’t tell it what I want, I get what MS decide because MS knows best. In the end the only solution that I could find was by deleting the formatting on both columns and then recreating singly on each.

And of course, when you are in a cell that was part of a range that has been conditionally formatted, the formula is no longer adjusted to that cell as it was it previous versions of Excel, it shows the actual formula you applied. So I am in P15, and in the rule I see the formula =OR($A3=”break”,$B3,AND($A3=””,$M3<>””)). The first time I encountered this it really threw me. What possible purpose can this change serve? I cannot see it.

Add to all of this rubbish the new, glitzy, childish icon sets, the fact that the formula box still defaults to input (so you have to F2 to actually use it), the fact that the dialog is not resizeable in order to better view your formula; I can only conclude that this is another very bad user dialog.

Considering that Excel 2007 was supposed to be simpler to use, and remove some of the mysteries of Excel, I think the designers of Conditional Formatting in Excel 2007 have failed abysmally. As seems to be the way with MS and Excel, too much effort was spent on adding unnecessary and distracting glitz, and very little on real usability.

Autogen’ed Ribbon Code







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

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

  • 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
  • Group/Sep/Tag
    – concatenation of Group & Separator & Tag Upper


  • Control
    Id – a derived id for this control


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


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






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

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="" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

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

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

<button id="btnDemoButton1"
screentip="Button 1"
superTip="Format activecell as currency"
onAction="procButton1" />

<button id="btnDemoButton2"
screentip="Button 2"
supertip="Format activecell as percent"
onAction="procButton2" />

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

<button id="btnDemoButton3"
label="Text Wrap"
getScreentip="Button 3"
getSupertip="Format activecell with text wrap"
onAction=" procButton " />

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 


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="" onLoad="rxDemoRibbonOnLoad">

<ribbon startFromScratch="false">

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

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

<button id="btnDemoButton1"
onAction="rxDemoGetAction" />

<button id="btnDemoButton2"
onAction="rxDemoGetAction" />

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

<button id="btnDemoButton3"
onAction="rxDemoGetAction" />

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()
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()
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.