Category Archives: 11530

Who Says The Ribbon Is Hard?




Introduction


I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy.


The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the third can change the image when a certain cell is selected, from one image to another. This principle could easily be extended to have say different groups of buttons for each sheet in a workbook, hide/expose the group upon activation/deactivation of the sheet.


CustomUI Editor


If you want to look at the xml, you should download the CustomUI Editor. It is hardly what you would call a sophisticated tool, but it does enable you to create xml for the ribbon customisations.


Coding Techniques


A couple of things about my code. I do not hardcode any values in the xml, apart from the ids of the code. Instead I use callbacks for all of the properties. As an example, I could setup a button in the xml like so


<button id =”btnDynaRibbon1″
        label =”Button 1″
        onAction =”rxOnAction”
        image =”ImportExcel”
        size =”1″
        visible =TRUE
        screentip =”Button that toggles the image” />>


which has all of the properties, except onAction, hard-coded.


I could do it this way, but I don’t, instead my xml looks like this


<button id =”btnDynaRibbon1″
        getLabel =”rxGetLabel”
        onAction =”rxOnAction”
        getImage =”rxGetImage”
        getSize =”rxGetImageSize”
        getVisible =”getVisible”
        getScreentip =”rxGetScreentip” >


where all of the properties are using callbacks, which means that the value gets set within the VBA code. This is the code for the rxGetLabel callback


Ribbon Code


Public Function rxGetLabel(control As IRibbonControl, ByRef label)

   Select Case control.Id

       Case CONTROLID_TAB_DYNA:                 label = LABEL_TAB_DYNA
       Case CONTROLID_GRP_DYNA:                 label = LABEL_GRP_DYNA
       Case CONTROLID_BTN_DYNA_1:             label = LABEL_BTN_DYNA_1
       Case CONTROLID_BTN_DYNA_2:             label = LABEL_BTN_DYNA_2
       Case CONTROLID_BTN_DYNA_3:             label = LABEL_BTN_DYNA_3
   End Select
End Function


It simply tests for the control’s id, and returns the caption for the label appropriately. All of the values tested for and the values returned are stored as constants in the VBA (they could just as easily be stored in a table on a worksheet). The values of all of those constants in the above code snippet are


‘ CONTROLID_ constant values
Global Const CONTROLID_TAB_DYNA As String = “tabDynaRibbon”
Global Const CONTROLID_GRP_DYNA As String = “grpDynaRibbon”
Global Const CONTROLID_BTN_DYNA_1 As String = “btnDynaRibbon1″
Global Const CONTROLID_BTN_DYNA_2 As String = “btnDynaRibbon2″
Global Const CONTROLID_BTN_DYNA_3 As String = “btnDynaRibbon3″


‘ LABEL_ constant values
Global Const LABEL_TAB_DYNA As String = “Dyna-Ribbon”
Global Const LABEL_GRP_DYNA As String = “Dynamic Ribbon”
Global Const LABEL_BTN_DYNA_1 As String = “Button 1″
Global Const LABEL_BTN_DYNA_2 As String = “Button 2″
Global Const LABEL_BTN_DYNA_3 As String = “Button 3″


Managing Changes


Taking one of the dynamic elements, the first button that can be made non-visible, the visible value is not a constant, but a variable as it has to be manipulated by the code


‘ VISIBLE constant values


Global VISIBLE_BTN_DYNA_1 As Boolean


This variable is initialised as TRUE in the Workbook_Open event, and in the callback procedure, the control’s visibility property is returned as whatever is in this variable


Public Function rxGetVisible(control As IRibbonControl, ByRef Visible)


   Select Casecontrol.Id


       Case CONTROLID_BTN_DYNA_1:             Visible = VISIBLE_BTN_DYNA_1


etc.


The visibility variable gets changed in simple worksheet event code (remember, I am using a data validation cell to drive this code).


Private Sub Worksheet_Change(ByVal Target As Range)


    Select Case Target.Address


        Case “$C$3″


            VISIBLE_BTN_DYNA_1 = Target.Value = “Visible”


    End Select


    mgrxIRibbonUI.Invalidate
End Sub


As you can see, it sets the variable depending upon the cell value. Finally, it invalidates the ribbon variable so as to rebuild our ribbon customisations. The ribbon variable gets set in the ribbon onLoad callback


Public Function rxDMRibbonOnLoad(ribbon As IRibbonUI)


    Set mgrxIRibbonUI = ribbon
End Function


Invalidating the ribbon variable causes the callback routines to be re-entered; this is how you change things dynamically.


Summary


Hopefully you can see how simple it is to create dynamic ribbon customisations, and how to manage the changes within your VBA code. You should also be able to see how this can be extended to other areas of dynamic interaction.


As I mentioned, I keep the CustomUI very simple, and rely on the VBA code to set all of the properties of my ribbon customisations. This is not necessary, it is a style aspect that I prefer, I like to the XML simple, reducing the amount of changes that I need to make there, and instead drive it all from VBA. I use a host of global constants for my ribbon values, but again as mentioned earlier, it would be just as simple (and maybe pro vide more manageability of the values) if all of the control properties were added as a table on a hidden worksheet, and grabbed from there on ribbon load.


My example workbook is attached .

Thicker Than A Length of 4be2


Being a developer can be addictive fun. Sometimes you just get into the swing and before you know it is midnight and the house is eerily quiet. But it can also be hugely frustrating. Yesterday I had just such one of those days. I spent a whole day on a task that should have taken an half an hour maximum.


 


I have inherited the maintenance of a small application that that creates Word documents from a set of inputs in an Excel form and spreadsheets. Now being a good coder, the original developer created Word templates with a series of bookmarked fields that he updated with the information collected on the Excel sheets. I was recently asked to make a small amendment. This amendment affected a table of prices on one of the Word outputs which shows a product number, description and price. The client wanted the price to be appended with the text USD.


 


Originally I thought I would just update the template and add USD after the price bookmark fields. This I did, just needed to test it thenExcel Do, Dynamic Does


 


Unfortunately, getting the code to run was gave me great problems, the addin would just start and then immediately exit. To cut a long story short, after much rooting around (imagine trying to debug an app that immediately shuts down), I tracked the cause of this oddity. It was all down to the fact that the code stores 3 dates in the registry, and compares these dates at startup, shutting down if there is some issue. Unfortunately, the dates were being stored in the registry in US date style (mm/dd/yy), and so today’s date of 8th June (as I said, this was yesterday) was being stored as 06/08/10 because of VBA’s idiosyncratic handling of dates in a non-US date format, and when this was read back in the code saw it as 6th Aug, and immediately exited.


 


I finally got around this by changing the date in the registry, but will have to change the code to save the date in a format that is not US-centric for future changes. I can see the original developer’s logic, he is US based, the client is US based and there would be no possibility of anyone else using the app, so US style dates were not a problem. Not until I came along anyway.


 


On testing my simple solution, I hit a  problem because  the list of prices catered for 10 different items. An offer may be made with less than 10, so that when the app was run, and I only entered 3 items, the document had 7 lines with just USD on them. I couldn’t add the text to the template Word template.


 


Next thought was to dive into the code and when the price is taken from the Excel sheet and copied into the bookmarked fields, I would append USD at this point (there is no concern with other currencies, this is a custom app for a solely US-centric company).


 


Now, I had never dived into the code of this app before, all previous changes that I did had just been effected by changing the Word templates or the Excel sheets. Stepping through the code I found that it uses named ranges on the Excel sheets that correspond to the bookmark names in the Word template, and so just goes round in a loop and transfers the values from the named range to the equivalent Word bookmark. This was not good news, for me, because it meant that I would have to test these names and look for specific names, and add USD to those values. Not a great coding challenge, but it seemed a very poor solution to me, breaking what was a very clean and tidy original code model. Worse, I had some similar changes in other docs to do, and I could see it getting very messy.


 


Then after dinner, my brain finally engaged. The prices in the Excel spreadsheet were being displayed as dollar values, for instance as $1,234, and this was what was transferred to the Word bookmark field. It finally dawned on me that all I had to do was to change the format of the Excel field from “$”#,##0;;; to “$”#,##0 “USD”;;; and the existing code would take care of it all for me. A quick change of formats, a simple test run, and all was shown to be working.


 


So a 5 minute problem took me a couple of hours to resolve. At times, my denseness amazes even me. My only thoughts were that getting over the app dates problem has slowed my brain down, at least that is the excuse I am using.


 

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
     Else

         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
     Else

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

  <ribbon startFromScratch=false>

    <tabs>

      <tab idMso=TabDeveloper>

        <group id=grpSPBuilder label=SP Builder>

 
          <button id=btnSPBuilder
                  label=Build SPs
                  imageMso=ControlsGallery
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPList
                  label=List SPs
                  imageMso=ConditionalFormattingHighlightDuplicateValues
                  size=large
                  onAction=rxspOnAction />
 
          <button id=btnSPAbout
                  label=About…
                  imageMso=Info
                  size=”large”
                  onAction=”rxspOnAction />
        </group>
      </tab>
   </tabs>
  </ribbon>
</customUI>


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


Summary


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

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

CUBEVALUE(CubeName,

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

                       “[Activity Date].[Date Drilldown].[Month].&[1]&[1]&[2009]“),

                       “[Measures].[Gas_Amt_Net]”)

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,

CUBEVALUE(CubeName,

                       ‘Pivot Sheet’!B10,

                       “[Activity Date].[Date Drilldown].[Month].&[1]&[1]&[2009]“),

                       “[Measures].[Gas_Amt_Net]”)

Obviously, 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 pivot.

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.

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.

 

Private 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 = wsApp.Range(NAME_REF_LIST_HIERARCHY)
    Set mpConn = New Connection
    mpConn.Open “Provider=MSOLAP;” & _
                “Initial Catalog=<;” OLAP_SERVER_NAME & _
                “Data Source”, OLAP_DB_NAME)
    Set mpCat = New ADOMD.Catalog

    With mpCat.CubeDefs(OLAP_CUBE_NAME)

        ReDim appVecHierarchies(1 To mpData.Rows.Count)
        i = 0

        For Each mpCell In mpData.Columns(1).Cells

            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 mpLevel.Members.Count)
            j = 0

            For Each mpMember In mpLevel.Members

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

            i = i + 1
            appVecHierarchies(i) = appVecMembers
        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?

Obviously, 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

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

-          adding
a sheet

-          setting
up the pivot table

-          loading
the dimensions one by one into the Rows area

-          retrieving
all of the PivotItem values.


 


Private 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 = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    ThisWorkbook.PivotCaches _
        .Create(SourceType:=xlExternal, _
                SourceData:=ThisWorkbook.Connections(CONNECTION), _
                Version:=xlPivotTableVersion12) _
        .CreatePivotTable TableDestination:=mpSheet.Name & “!R5C4″, _
                          TableName:=PIVOT_TEMP, _
                          DefaultVersion:=xlPivotTableVersion12

    Set mpData = wsApp.Range(NAME_REF_LIST_HIERARCHY).Columns(1)
    ReDim appVecHierarchies(1 To mpData.Cells.Count)
    For Each mpCell In mpData.Cells
   
        With mpSheet.PivotTables(PIVOT_TEMP)
          
            ‘store the next value in the pivot rows
            With .CubeFields(mpCell.Value)
                .Orientation = xlRowField
                .Position = 1
            End With

            With .PivotFields(1)

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

                    j = j + 1
                    appVecMembers(j, 1) = mpPivotItem.Caption
                    appVecMembers(j, 2) = mpPivotItem.SourceName
                Next mpPivotItem

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

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

    mpsheet.Delete

    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.


Summary


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.


 

Conditionally Yours

VBA is a very simple language and programming environment, very easy to get something up and running very quickly, but it is showing its age these days, and with Microsoft making no improvements to the language it will not get better.

Which all means that we should utilise all of the functionality already available to us. I was using a particular technique the other day, and I recollected a discussion on one of the popular Excel forums that I had participated in, one in which I was surprised at the lack of knowledge of this technique.

The situation I found myself in was that I had developed some functionality in an application, and this functionality was quite sophisticated, but was not fully working as I wanted. I needed to deliver something, so I decided to do it in a simpler way, but I didn’t want to just lose my previous efforts, I wanted to continue to try and complete my initial attempts.

I could have just stripped out all of the old code and added in the new code and save it as a new workbook, I will still have the original code from the previous workbook. But this would mean that any other changes that I made to my new workbook, other than this revised function, would need to retro-fitted into the old workbook otherwise it would soon be useless. I think the chances of messing this up are obvious.

Another way would be to create a Public constant, and test this variable and execute the code depending upon this variable, something like this

Public Const NewVersion As Boolean = True

And then in the modules, code such

    If Not NewVersion Then

       ‘old code
    Else

        ‘new version code
    End If

This works okay, but of course all of the code will be compiled, and there are potential problems if you have variables associated specifically with the old or new versions, if they are being used incorrectly elsewhere the compiler will not throw them out as they will still be defined.

The solution is to use Conditional Compilation. This is similar to the technique above, but a conditional constant is used, and conditional statements. The code would look like this

#Public NewVersion = True

And then in the modules, code such

    #If Not NewVersion Then

       ‘old code
    #Else

        ‘new version code
    #End If

The advantages to this approach are:

·         depending upon
the value of the conditional constant, only that part of the code will be
compiled

·         unlike standard
If statements, public variables can be wrapped in #If … #End If statements

·         VBA contains a
few built-in conditional constants, such as Mac (test for Mac platform), Win
(Windows), VB6, which are available to use

·         the conditional
statements can help ensure variables are correctly used when compiling

·         identifying the
code to strip out if and when it is decided that one approach is the preferred
way is facilitated by the #If statements.


I tend to put conditional constants in each code module, although it is possible to set it in the project properties. I just find the module approach more intuitive, you can assign values such as True/False whereas in the project properties you have to set values of -1/0 (sic!). It also means I can have different values in each module when I am testing. Note that conditional constants will not accept an explicit Public declaration, nor will they allow strict data typing.


This is an extremely useful technique, but just remember to have those values properly set at the end.



 

What’s My Type?


I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I try to use it in anger, it breaks on me. As such, I tend to avoid it and create classes.


Earlier this week, it occurred to me that there was a case in which I could implement a Type, an elegant solution, with no chance of it breaking on me.


We all know that when your masterpiece sets application variables, such as calculation mode, in its processing, they should be reset at the end to whatever state the user originally had them. The normal way to do this is to save them in a bunch of variables at the start of the process, and restore them at the end.


My solution is very similar, but the benefits of Type add an elegance that I don’t feel the variables give it.


Here is that solution.


 


Public Type ApplicationValues


    ScreenUpdating As Boolean


    Calculation As XlCalculation


End Type


 


Private AppValues As ApplicationValues


 


Public Function ObfuscateData()


 


    With Application


   


        AppValues.ScreenUpdating = .ScreenUpdating


        AppValues.Calculation = .Calculation


       


        .ScreenUpdating = False


        .Calculation = xlCalculationManual


    End With


   


    On Error GoTo func_error


 


… main code


 


func_exit:


    With Application


       


        .ScreenUpdating = AppValues.ScreenUpdating


        .Calculation = AppValues.Calculation


    End With


 


func_error:


    ‘your error handler


    Resume func_exit


End Function


 


I think this is a neat little approach. Furthermore, it is simple and extendible, what more can we ask?


This may be obvious to you, it may be the sort of thing that you do all of the time, but it made me smile after I had implemented it. So much so that it is now a code snippet in my MZ-Tools, implementable at the drop (click) of a button.


 

The End Of VBA

The title was just to get your attention, it really should be entitled something along the lines of “What Made VBA The Success It Is Today?’, as this post is more about what VBA is and isn’t, albeit somewhat precipitated by the thought that VBA must be close to its end game.


I recently saw a thread in a forum where one of the responses was along the lines that “VBA was not designed for programmers – i.e. MS dumbed down requirements …”. I found this to be a very facile argument and said so in my response. I won’t bother retracing that thread here, but I thought it raises an interesting question.

When MicroSoft introduced VBA into Office, just what was their objective, how did they envisage its use, and what concessions did they make in their implementation? BTW, although it was introduced into Office, I will concentrate solely on Excel, for obvious reasons.

Although Excel already had a macro language, these were not the easiest to use, and so, for whatever reason that it was done, introducing VBA into Excel 97 was a masterstroke, it opened all sorts of possibilities to Excel users. Like all projects, I am sure that MS’ implementation of VBA had some key must-do objectives, some secondary should do objectives, and some nice to haves; some of those objectives would be customer focussed (such as giving users a simpler development tool), some would be MS focussed (for example, as it was a superset of VB, they would probably want to keep it as closely aligned to VB as is possible, controlling development and maintenance costs). And like all projects, the timeframes and the budgets would be balanced, large enough to suggest that a great product was achievable within the projects  goals, but not so large that everything was easy and achievable – that is just the way of the world.

I have no inside knowledge, but I would think that when MS decided to give the world VBA, they were not looking at IT shops, they were specifically looking to give real users extra capability. They probably anticipated macros that formatted worksheets, shunted data around a sheet/book, all of the standard, simple one-off macro solutions. I very much doubt that they anticipated quite how its usage would take-off, how Excel especially has morphed into a development platform capable of running highly complex applications.


The sheer creativity and inventiveness displayed in VBA is staggering. In Excel alone, there have been some amazing development, from the simple to the hugely complex. We have seen  a whole gamut of solutions, for example


  • simple, but highly effective, tools that make using Excel functions simpler, such as Rob Bovey’s Chart Labeller
  • tools that extend Excel’s native functionality and help spreadsheet users to use Excel more efficiently, such as Jan Karel Pieterse’s NameManager
  • true developer tools for spreadsheet users that help better spreadsheet development, such as Charles Williams FastExcel
  • some incredible tools that do things previously thought not possible, many such examples but a classic is Stephen Bullen’s PastePicture
  • giving us back functions that we used to have, such as Andy Pope’s Pattern Filler utility in Excel 2007
  • database applications where Excel is providing a presentation and data management layer, and a database, Access or SQL Server, maintains the data and provides the richness of a relational database
  • ETL applications where Excel harvests data from disparate sources to provide a composite picture, using tables, charts, dashboards etc.
  • end-to-end data capture, data management, and reporting applications using multiple technologies and multiple layers.


What is even more impressive is that these solution have often been produced by one man outfits, no big IT departments to support the developer here, and often by developers that are mainly business users, people who would never claim to be coders (one of the best Sparklines implementations I have seen was by a guy who made no claim to being a coder, indeed he said that he built this implementation to help develop his VBA skills).

Did MS envisage all of this when they were looking to add VBA? Maybe, but somehow I doubt it. As I said previously, users building macros to automate a few tasks, formatting, capturing data, and so on, is a far more realistically envisaged deployment, presumably they felt that just bringing VB into the Office apps would be a lot simpler for users to get to grips with. If they had envisaged all of this use, I would have hoped that they would have been far more ambitious in their objectives, but I don’t believe this so it is a sterile argument for me.

As I mentioned above, VBA would have been a project, and as such, it would have been subject to typical project constraints. This must have meant that, throughout the course of the project, implementation decisions would have had to be made. Some of these decisions probably resulted in cutting or constraining some of the functionality that was originally envisaged. I am sure this would have been done for all of the right reasons, and that the VBA developers and project leaders would have justified it in their minds, always taking into consideration what they knew or thought at that time. Sitting here in 2009, we might argue that some of those constraints were ill-advised, but that is hindsight, and it is certainly worlds away from saying that MS dumbed down VBA.

Sure there are some things that are better in VB than they are in VB6.


  • forms are better in VB, you have a built-in menu facility, and control arrays, but both of these can be emulated in VBA, with varying degrees of success and completeness
  • you can create stand-alone executables in VB6, but not in VBA – this is true but when VBA was introduced I am sure this was not even a consideration, without being able to foresee the types of addins deployed today and the security issues that have manifested, it seems impossible to think back then that this was necessary or even desirable
  • packaging and deploying – again, VBA is by default packaged within its host container application, targeted at individual users, how could MS have envisaged this need, and anyway, there are many good third party tools that provide this capability


Some things are simpler in VBA, working with Excel for example in Excel, by virtue of its implementation, just as some things are harder by virtue of its implementation.

Acknowledging some things are easier in VB is a completely different matter from saying that MS dumbed down VBA. I no more believe that MS deliberately dumbed down VBA than I believe that many of the techniques that have been developed over the years to make Excel such a versatile development platform were deliberately introduced by MS, it is just consequences of the original implementation.

To me, this is a relevant topic today as we stand on the brink of moving, either willingly or not so willingly, to the .Net world. It originally struck me that MS were not prepared to make any concessions to the world of VBA and the millions of users that have built solutions using VBA. My impression was that they were trying to dig their heels in and say that it is .Net in the future or nothing. I believe that attitude has changed somewhat, not enough yet, but at least they concede that these users have to be accommodated or else they just will not, or cannot, upgrade. The interesting thing will be to look back in 5 years time and reflect on what we think that MS have and have not done well in introducing .Net into Office. I for one feel that we are on the brink of a much better experience with .Net, and am cautiously looking forward to it.

In the meantime, let us all celebrate VBA, the way it has been stretched, bent and moulded over the years by the thousands of real users who just wanted to be able to do something, and do it today; by talented Excel developers like those mentioned above, and by all of the ‘novice users’ who have dared to try and do something in Excel and VBA that helped them to work faster, less, and/or better. Of course, there have been some horror stories along the way, I have seen a few myself, but there have been many successes. Excel has not become the de-facto application development platform that it is today (what other desktop application could have a book like PED written about it?) because MS dumbed down VBA, but because Excel has an incredible function set, and VBA has exposed that function set to all manner of users, and it is they that have shown the world just what is possible.

Too many Modules Spoil the VBA Project?

So many times you see someone asking about whether they should split their VBA code into many modules, if so, how should they structure it?

We probably know that there is a limit to the amount of code that a VBA code module can handle. The code module is essentially an in-file text file (if exported it can be viewed with a simple text eitor), and can accomodate a size of approx 64k bytes. As such, the answer to that previous question should always be an emphatic yes. But it also should be yes from a code maintainability persepctive, even if it never approaches the 64k limit. The code should be organised into functionally discrete modules, for instance a module that contains all of the file handling procedures, another that contains all of the graphing procedures and so on.

For my projects, I tend to have a global module, which contains all of my global variables (although I try to keep these to a minimum), and all of my application constants, such as messages and enumerated lists. This way, it is simple to track the source of these variables, rather than scattering them amongst many modules.

I would normally have an application class, specific to the particular application, which would hold any procedures common to that application, such as a common validation procedure, and application variables, such as the AppId, AppName, AppVersion and so on. It could be argued, and I have gone this route in some projects myself, that many of those global variables are application specific and should be in the application class. I think I subscribe to this in theory, but don’t always practise it.

Of course, I have a common error handling procedure, a utilities procedure (which would hold my generic procedures such as SheetExists), and a history module, which is pure comments.

If I need to trap application events, I would have such a class. I tend to hand off the actual processing of these events to a helper module, the class module simply traps the event, decides whether it applies to this application in these circumstances, and then calls the procedure in another module. Theoretically, I am not happy with this from a design perspective, but I do find it makes the code a lot more manageable, and convince myself it is good from a maintainability perspective.

I also always have a scratch code module, where I have procedures to easily setup data, reset things, test specific aspects of the application, and so on.

Finally, I apply a strict naming convention, and add two underscores to a few modules to force them to the top of the list, and a single underscore to some others to forec them into the next batch. I find this categorisation also helps with maintenance.

Here is an example of one of my projects.



As you can see, it is a good size with over 8,000 lines of code, the 363 procedures are nicely scattered over the modules, no module having more than 30 procedures except AppClass (many of which would be property Let/Gets). Apart from AppClass, no module has more than 700 lines of code, and each procedure is circa 30 lines line. Of these code lines, nearly 1,500 are comment lines (circa 15%). I think this is a high comment rate for me, not typical, as I do not over-comment my code.  In fact, without the history module, the comments reduce to 12%. 10-12% I would think is my typical comments percentage.