Category Archives: 11521

If One Is Not Enough, …



It is generally accepted that it is a good programming practice to not use monolithic code, but to break functional units of code into separate subs or functions and call these from a control procedure. Functions are very useful for passing values back to the calling procedure, but functions return a single value so what if you want to get more than one value returned to you calling procedure?


I have often seen this topic raised in the Excel forums, a statement that a function cannot return multiple values, or a question on how to return multiple values. There are a number of ways to return multiple values, here I will show 3 such ways, with 2 variations on the last one.


The ways that I will cover are:


  • Classes
  • ByRefArguments
  • UDTs
    • Returning a UDT
    • UDT as an Argument



Some of the codeI includes basic error handling to illustrate the point made earlier.


 


Classes


The first example here is using classes. Classes have properties that can be read, write, or read-write, they can have as many as you need, and they have methods which are essentially functions within the class. So it should be clear that by using a class, our control procedure can call a procedure that returns multiple values; any of the properties or functions could set any number of other properties; a call to a class can then read those properties to get multiple return values. An example class and calling procedure is shown in Figure 1.


'---Control procedure
Public Sub MultiplesClass()
Dim cls As clsPerson

    On Error GoTo errhnd
    Set cls = New clsPerson
    cls.Name = "Jack"
    cls.DoB = DateSerial(1976, 12, 3)
    MsgBox cls.Name & " aged " & cls.Age & " years"

teardown:
    Set cls = Nothing
    Exit Sub
    
errhnd:
    MsgBox Err.Number & "," & Err.Description
    Resume teardown
End Sub

'---Class code
Private mAge As Long
Private mDoB As Date

Public Name As String

Public Property Let DoB(Value As Date)
    mDoB = Value
    mAge = Year(Date) - Year(mDoB)
    If DateSerial(Year(Date), Month(mDoB), Day(mDoB)) >= Date Then
    
        mAge = mAge - 1
    End If
End Property

Public Property Get Age() As Long
    Age = mAge
End Property

Figure 1 – Using a Class to Return Multiple Values


Byref Arguments


Perhaps the easiest way to return multiple values is to use arguments of the function to return amended or changed values. This method can use the return value to pass the function success status with the arguments returning the business variables. The one thing to note is that arguments that will be used to return values must be passed ByRef.


 


Figure 2 shows an example of returning multiple values using function arguments, also demonstrating the error handling.


 


Public Function MultiplesArgsCaller()
Dim myName As String
Dim myAge As Long
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Jane"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman
    
    myName = "Damien"
    If Not MultiplesArgsCalled(myName, isBlokeWoman, myAge) Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & myAge & " year old " & isBlokeWoman

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesArgsCalled( _
    ByVal Name As String, _
    ByRef GenderType As String, _
    ByRef Age As Long) As Boolean

    MultiplesArgsCalled = True
    Select Case Name
        Case "John"
            GenderType = "bloke"
            Age = 45
        Case "Jane"
            GenderType = "woman"
            Age = 36
        Case Else
            MultiplesArgsCalled = False
    End Select
End Function

Figure 2 – Using a Function Arguments to Return Multiple Values


Returning a UDT


The third and fourth methods use user defined types (UDTs).


UDTs can be seen as mini, very basic, classes. They are a set of data elements within an encompassing object, any instance of a UDT will inherit all of those data elements. The elements within a type can be strongly type with the uual data types, or default to Variant. Interestingly, an elament within a UDT can be defined as a previously defined UDT, types within types, but that is another topic.


As with function arguments, the elements within a UDT can be used as read, write or read-write data elements, the values that are passed to the function can be separated from the values returned, or a value could be passed to the function, amended ther, and the changed value returned in the same UDT element.


The first of these two methods uses a UDT for the function type. Figure 3 shows an example of returning multiple values using a UDT as a function type.


Public Type Person
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    myName = "John"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    myName = "Jane"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    myName = "Damien"
    thisPerson = MultiplesUDTCalled(myName)
    If Not thisPerson.Success Then
    
        Err.Raise 19999
    End If
    
    MsgBox myName & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = myName & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByVal Name As String) As Person

    MultiplesUDTCalled.Success = True
    Select Case Name
        Case "John"
            MultiplesUDTCalled.Gender = "bloke"
            MultiplesUDTCalled.Age = 45
        Case "Jane"
            MultiplesUDTCalled.Gender = "woman"
            MultiplesUDTCalled.Age = 36
        Case Else
            MultiplesUDTCalled.Success = False
    End Select
End Function

Figure 3 – Using a UDT as a Function Type to Return Multiple Values


Passing UDT as an Argument


The fourth and final method uses a UDT as a function argument. The same considerations apply as when using a UDT as a function type. Figure 4 shows an example of returning multiple values using a UDT as a function argument.


 


Public Type Person
    Name As String
    Gender As String
    Age As Long
    Success As Boolean
End Type

Public Function MultiplesUDTCaller()
Dim myName As String
Dim thisPerson As Person
Dim isBlokeWoman As String

    On Error GoTo errhandler:
    
    thisPerson.Name = "John"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender
    
    thisPerson.Name = "Jane"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

    thisPerson.Name = "Damien"
    If Not MultiplesUDTCalled(thisPerson) Then
    
        Err.Raise 19999
    End If
    
    MsgBox thisPerson.Name & " is a " & " is a " & thisPerson.Age & " year old " & thisPerson.Gender

teardown:
    Exit Function

errhandler:
    If Err.Number = 19999 Then Err.Description = thisPerson.Name & "is an unknown  name"
    MsgBox Err.Number & ", " & Err.Description
    Resume teardown
End Function

Private Function MultiplesUDTCalled( _
    ByRef Them As Person) As Boolean

    MultiplesUDTCalled = True
    Select Case Them.Name
        Case "John"
            Them.Gender = "bloke"
            Them.Age = 45
        Case "Jane"
            Them.Gender = "woman"
            Them.Age = 36
        Case Else
            MultiplesUDTCalled = False
    End Select
End Function

Figure 4 – Using a UDT as a Function Argument to Return Multiple Values


Summary


so there we have it, 4 ways to get multiple values from a procedure. ALl work, so it is a matter of preference or suitability as o which to use. Personally, I don’t use UDTs, my preference is for multiple arguments within a function. Of curse I use classes, but not for this being able to return multiple values, but in situations where a class suits my application design.


As an aside, I don’t use subs, I always use functions. All procedures should always return at least one value, a success or failure value to pass back to the error handler. Using the function return value to pass back a succes indicator does mean that where you do need that function to return some value determined within the function, just think of any type of calculator, the function return value has been used up, so you will necessarily need to pass back multiple values.

DAX, The Dark Knight



Excel Formulas Are easy, So Why Is DAX So Hard?


Recently, Rob Collie, aka PowerPivotPro, posted a blog entry entitled ‘The Ballad of Ken Puls, DAX Convert’. Whilst it was an interestiung post, this is not to specifically offer any comment on that blog, or to stroke Ken’s ego <g>, I was intrigued by the part where Rob mentioned that Ken had said, and I quote,


 


“I firmly believe that PowerPivot is the future of Excel.  No question in my mind.  It’s insanely easy to get some killer BI in your hands with very little learning, which is awesome.  But…  What I don’t get is DAX.  The light just isn’t going on for me.  If you want to mobilize the Excel Pro army to really make this take off, DAX needs to become accessible for us.”

 


This rang a bell with me, as about six weeks ago I taught a 2 day PowerPivot course with Chris Webb to a class of 15 technology savvy people. These are smart people, but it was clear when covering the more advanced aspects of DAX that they were straining to grasp some of the concepts. On my journey home I was pondering about this. I have stated before that I believe that Microsoft over-egg the simplicity of PowerPivot, suggesting that any user can pick up PowerPivot and create fantastic BI immediately. I think that, whilst easy to get started, to really get the true potential out of PowerPivot you have to have some understanding of Data Modelling.


Have Microsoft got it wrong with DAX as well when they took a deliberate decision to make DAX Excel-like in its syntax? In many ways, this is understandable, their experience of MDX, which really is hard, could weigh heavily on their outlook. But, it has been a double-edged sword in my view. Excel users people are quite ready to give DAX a go because it looks as easy as Excel formulas, but conversely, when they get to advanced DAX (CALCULATE, FILTER, ALL, etc.), they can easily get dis-heartened because it is not as easy as Excel formulas.


So I thought I would look at why DAX is so hard for an Excel user. I came up with 7 topics that I decided to look at, namely:


  • Data Typed
  • Integer Dates
  • Formula Formatting
  • Evaluation Context
  • Extendible
  • Debugging Aids
  • Ease of use

 


Data Typed


Integer Dates


Formula Formatting


Extendible


Debugging Aids


DAX is great if your formula works, but when it doesn’t you need good tools to help you debug it. Unfortunately. Microsoft haven’t provided any debugging tools whatsoever, so debugging is hard, incredibly hard. It should be part of our course, but with no tools to talk about there is nothing to suggest yet.


Just to complicate matters, the data usually consists of thousands, or even hundreds of thousands or millions, of rows, and reducing it to a reduced set is not staright-forward.


Marco Russo, Darren Gosbell, Angry Koala, and Paul te Braak have created a nice Excel addin called DAX Studio which is as good as it gets. it is a useful tool for building DAX formulae, as described by Javier Guillen in his post Using DAX studio to create PowerPivot measures, and this technique can be used to rebuild the formula from scratch checking as you go, but it is not reall a debugging tool, and was not meant as such.


Compare this to Excel. In Excel, the helpful debugging facilities are:


  • Evaluate Formula
  • Watch Window
  • F9

 


The Evaluate Formula facility lets you evaluate a formula in the activecell, stepping through it as each stage of the formula seeing what the result at that stage is, until it is fully resolved. It allows allows stepping in and out of the cell value if the expression being evaluated is a single cell.


The Watch Window allows you to add a cell or a range of cells to be watched, thw window showing the current value, reflecting any changes.


F9 allows you to select any expression within the formula bar and see what it resolves to. The expression can be a single cell, a function, or a set of functions, as long as it is an expression that can be resolved in its own right.


So, three facilties to assist in debugging the formula should make Excel formula debugging easy? In reality, I never use Evaluate Formula or the Watch Window as they do not provide enough value in my view. F9 is very useful, in that it is possible to evaluate any part of the formula. This is in contrast to Evaluate Formula, which evaluates the whole formula which means that the TRUE path is only evaluated, whereas with F9 you can select any part and have it evaluated regardless of whether the actual data would force the formula to follow that path or not.


Okay, Excel has a few tools, which is a few more than DAX, but they are hardly what you would call sophisticated. It still mainly relies on experience, intuition, and insight to be able to debug the more complex Excel formulae. Debugging DAX is diffocult, but debugging complex Excel formulae is not the easiest task either.


Evaluation Context


Evaluation context is probably the


Evaluation context is the aspect of DAX that people struggle with most when they start with PowerPivot. Understanding filter context and row context, when each comes into play, how you can override aspects of the filter context, all of this can seem mind boggling at first.


Ease of use

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 .

Keep It Simple? Don’t be Daft!




Keep It Simple? Don’t Be Daft!


I was recently working on a project where we were using SSRS to create XML reports which are then imported into Excel to facilitate further analysis.


Unfortunately, I could not see any option in PowerPivot to connect to an XML source. There is the option for text and RSS, but no XML that I can see. I know I could create an RDL and connect to that, but XML is far more than just SSRS.


I raised this with some colleagues, and one of them pointed me to this Microsoft article, Data Feed XML Syntax. I came away shell-shocked from reading this article. I cannot understand Microsoft’s reasoning here. I thought that RSS used XML as its transport protocol because it (XML) was a recognised data transport standard. RSS may use a particular dialect of XML, that is hardly surprising as it has particular needs, but why would anyone choose to use that dialect for its general XML import? We have a universal standard, and a particular implementation of that standard. So what do Microsoft use in PowerPivot but the RSS specific implementation of XML. Surely, it would have made more sense to build an XML import capability, then modify that for the particular implementation that is RSS.


To create a situation whereby we have write code to change the original XML to conform to some other implementation that is totally irrelevant to our problem is madhouse development. To quote that document … Programmers who use other tools or approaches (such as Microsoft generated XML – my words) will need to know how to structure a valid XML response that can be processed by the data feed reader that is built into the PowerPivot for Excel. This is a long way from my idea of self-service BI.


At times I wonder what planet Microsoft inhabits, it often seems to be a different world to mine. You can (easily) import XML into Excel, so they can do it, have done it. I knew we were in trouble as soon I saw SharePoint mentioned in the article, the bane of Office at the moment is that Microsoft seem intent on subsuming it within SharePoint.


Mr Richard (sorry, I cannot use the name he addresses himself by, this blog sees that as a profanity) Moffatt also makes a an interesting read on Office and SharePoint.

Self Service Is Cheap?




As I am sure that you are all aware, PowerPivot is the best thing to hit Excel since PED. Although I am not a big data cruncher, I have been playing with PowerPivot; to keep abreast of an important technology; it plays into one of my primary interests of Business Intelligence; and partly to seek out further opportunities.


Although I have been scouring the blogs and dedicated websites, I do like to have a book as a reference point, and so I was very pleased to get a review copy of PowerPivot for the Data Analyst: Microsoft Excel 2010, by Bill Jelen.


Bill Jelen is well known in the Excel community; he is the founder of MrExcel which is probably the most active Excel forum on the web; and is a prolific author and Excel presenter. It is good having an Excel PowerPivot book from someone whose primary focus is Excel, too many (including MS) see Excel as a presentation layer for SQL Server and SharePoint, and fail to acknowledge Excel’s capability and potential. Bill is unlikely to make that mistake.


Bill has a flowing writing style, and reading the book it is easy to imagine him at the front of a room giving one of his Excel presentations. Having access to Rob Collie, who has worked for Microsoft in this technology, was a lucky break for Bill, someone who could check his facts and feed him other ideas.


The book isn’t quite up to its subject in my view. It looks as though it was rushed to market to ride the launch hype, and suffers for that, with errors and what I see as less than relevant content; this feels like padding to me. There are basic typographical errors (the worst is when a trick to get month names to list in month name order is attributed to Colin Banfield, but his name is incorrectly spelt).


Too much of the content appears as if it has been lifted from some of Bill’s many other books. Much of the book is spent on ancillary topics in my view; there is more on standard pivot table usage than is required, such as the PivotTable trick that has impressed some. I have known the trick for some years, and whilst I acknowledge that it is a good trick, I have never found a use for it other than demonstrating it in conferences and training sessions, but most importantly, I cannot see the relevance to this book.


There is a section on basic DAX functions which covers 30 pages. Whilst these functions might be available within DAX, they are the same as the Excel functions and really did not need covering in depth here. I would have preferred more on DAX usage. There is a chapter on DAX measures, which is a good start, but after saying that a whole book could be written about Time Intelligence functions, we get a mere 10 pages. DAX is a real key feature that boosts PowerPivot, and can make them so much more powerful than standard PivotTables.


PowerPivot for the Data Analyst: Microsoft Excel 2010 is very easy to read, and whilst Bill is clearly a PowerPivot fan, he is not blind to some of its foibles and failures, and presents a good section on the pros and cons of PowerPivot.


Overall, as I mentioned, this book looks and feels rushed to me. Although it is quite a good start, I would not recommend it to anyone other than a novice Excel/PowerPivot user. It may be useful for someone that needs to gain a basic understanding of PowerPivot, but I feel there must be a better book out there, or on the production line. I notice that Marco Russo and Alberto Ferrari have recently released an Excel PowerPivot book, perhaps they would like to send me a copy to review?.


One other comment, not aimed specifically at this book, but at this market generally. In an age where we are all using GUI based software, and colour is used extensively within applications to help navigation and usage, publishers should not be printing the pictures in black and white, effectiveness is lost by the lack of colour.

VBA Has No Class




Recently, I was working on one of my apps, one that is database centric. Whilst making some changes, I came across this piece of code that inserts a new record into the database


    With RS

        .AddNew
        
        .Fields(FIELD_AUDIT_DATE) = sh.Cells(Rownum, COL_FU_AUDIT_DATE).Value
        .Fields(FIELD_CONSULTANT_ID) = GenerateID(FIELD_CONSULTANT_ID, Designer)
        .Fields(FIELD_SALES_TYPE_ID) = GenerateID(FIELD_SALES_TYPE_ID, sh.Cells(Rownum, COL_FU_SALES_TYPE).Value)
        .Fields(FIELD_CUSTOMER) = sh.Cells(Rownum, COL_FU_CUSTOMER).Value
        .Fields(FIELD_QUOTE_NUMBER) = QuoteNumber
        .Fields(FIELD_PRODUCT_ID) = GenerateID(FIELD_PRODUCT_ID, sh.Cells(Rownum, COL_FU_PRODUCT).Value)
        .Fields(FIELD_QUOTE_DATE) = sh.Cells(Rownum, COL_FU_QUOTE_DATE).Value
        .Fields(FIELD_QUOTE_AMOUNT) = sh.Cells(Rownum, COL_FU_QUOTE_AMOUNT).Value
        .Fields(FIELD_ESTIMATED_PROFIT) = sh.Cells(Rownum, COL_FU_PROFIT).Value
        .Fields(FIELD_REVISED_QUOTE) = sh.Cells(Rownum, COL_FU_REVISED_QUOTE).Value
        .Fields(FIELD_ACTUAL_PROFIT) = sh.Cells(Rownum, COL_FU_ACTUAL_PROFIT).Value
        .Fields(FIELD_STATUS_ID) = GenerateID(FIELD_STATUS_ID, sh.Cells(Rownum, COL_FU_QUOTE_STATUS).Value)
        .Fields(FIELD_DECLINED_ID) = GenerateID(FIELD_DECLINED_ID, sh.Cells(Rownum, COL_FU_DECLINED).Value)
        .Fields(FIELD_QUOTE_NOTES) = sh.Cells(Rownum, COL_FU_NOTES).Value
        
        .Fields(FIELD_UPDATED_BY) = ThisApp.LogonUser
        .Fields(FIELD_UPDATED_ON) = Format(Now, FORMAT_TIMESTAMPS_DB)
    
        .Update
    End With

Whilst looking at this code, for some reason my mind wandered to thinking about disconnected recordsets. Whilst most of my application involve database access, it is no longer on enterprise databases, so I don’t have the connection issues of high-end systems. As such, my use of disconnected recordsets is infrequent, my apps are safe in creating a user connection at logon, maintaining the connection throughout their session, and dropping at the end.


As an aside, I always use ADO in my applications. I found it easy to use and it performs fine for me. I have frequently been told that DAO performs better, usually by old Access’ers, but I have no issues with ADO, and will continue with it.


As often happens, my mind started wandering over this topic, forgetting what I was doing and thinking more about disconnected recordsets. I roamed on to thinking about collection classes. Collection classes are a very useful way of creating an in-memory dataset that can be manipulated by creating class methods, but they do require rather a lot of setup. In my musings, it occurred to me that I could use disconnected recordsets to achieve the same functionality, and use the builtin recordset functionality rather than creating my own methods.


Usually, a recordset would connect to a data source at some point, even a disconnected recordset, if only to get the data and/or write it back. It occurrs to me that this is not an absolute necessity, a recordset can be created and used without ever connecting to a data source, for instance where the data is maintained on a spreadsheet.


In this discussion, I will be working with a simple dataset as shown in Figure 1. 



Figure 1


In these examples, I am using late-binding, so I first declare some constants to emulate the ADO constants.


Enum ADOConstants
    adOpenStatic = 3
    adUseClient = 3
    adVarChar = 200
End Enum

Next, the data is extracted from the worksheet, and stored in an array.


Dim RSUsers As Object
Dim vecUsers As Variant
Dim Lastrow As Long
Dim i As Long, j As Long

    With ActiveSheet

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        vecUsers = .Range("A1").Resize(Lastrow, 5)
    End With

The recordset is created, and because we are not retrieving the data from a data source (using ADO), we need to initialise the recordset by creating the columns.


    Set RSUsers = CreateObject("ADODB.Recordset")
    With RSUsers

        .Fields.append "FirstName", adVarChar, 25
        .Fields.append "LastName", adVarChar, 25
        .Fields.append "Gender", adVarChar, 1
        .Fields.append "Role", adVarChar, 25
        .Fields.append "Location", adVarChar, 25

        .CursorLocation = adUseClient
        .CursorType = adOpenStatic

        .Open

Note that you have to use a client side cursor.


Now that we have a defined recordset, we can load it with data. Each row of the array is iterated and a new record is added to the recordset, the fields are populated, and the recordset is updated.


    For i = 2 To Lastrow

        .AddNew
        .Fields("FirstName") = vecUsers(i, 1)
        .Fields("LastName") = vecUsers(i, 2)
        .Fields("Gender") = vecUsers(i, 3)
        .Fields("Role") = vecUsers(i, 4)
        .Fields("Location") = vecUsers(i, 5)
    Next i

    .Update

The recorsdet is now fully populated, contains all of the data, and so it is ready to use. To demonstrate this, I created a simply display function that outputs the recordset contents.


Private Function DisplayDetails( _
    ByVal RS As Object, _
    ByVal Title As String)
Dim msg As String
Dim i As Long
    
    With RS

        .MoveFirst
        Do Until RS.EOF
    
            msg = msg & "Name: " & RS.Fields("FirstName").Value & " "
            msg = msg & RS.Fields("LastName").Value & vbNewLine
            msg = msg & vbTab & "Role: " & RS.Fields("Role").Value
            msg = msg & "(" & IIf(RS.Fields("Gender").Value = "M", "Male", "Female") & ")" & vbNewLine
            msg = msg & vbTab & "Location: "
            msg = msg & RS.Fields("Location").Value & vbNewLine
            .MoveNext
        Loop
    End With

    MsgBox msg, vbOKOnly + vbInformation, Title
End Function

The first demo shows all of the rows, all of the columns.


    Call DisplayDetails(RSUsers, "List All Users")

We can also use the recordset Find function to find the rows that match the specified criteria. The criteria is based upon a column name.


    RSUsers.Find = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Users")

Find also can be used to skip rows, specify a start point, or set the search direction.


The recordset can be sorted,


    RSUsers.Sort = "Location"
    Call DisplayDetails(RSUsers, "List All Users Sorted By Location")

… or filter it.


    RSUsers.Filter = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Male Users")

You might wonder what is the difference between Find and Filter. Filter allows for multiple criteria, unlike Find.


    RSUsers.Filter = "Gender = 'M' AND Location Like 'P*'"
    Call DisplayDetails(RSUsers, "List All Male Users in P*")

As you can see, the disconnected recordset can do everything a collection class can do, but without having to hand-roll any of the methods, recordset has them builtin.


I think there is a killer usage for disconnected recordsets in Excel VBA apps, I just haven’t thought of it yet.

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.

Formulas Made Easy




I have been playing with PowerPivot recently, and one of the sites I have visited is Rob Collie’s PowerPivotPro blog, http://powerpivotpro.com/. One particular post that caught my eye was a guest post by Colin Banfield, on building complex DAX formulas in NotePad++. As Colin noted, the PowerPivot formula editor is severely limited, a better option is required when working with more complex DAX formulae. As Colin suggested, NotePad++ can provide this better option; with code folding, keyword highlighting, and Intellisense. In his post, Colin showed how to extend NotePad++ to provide this functionality for DAX formulas.


Being primarily an Excel guy, I couldn’t help but empathise, I feel exactly the same about creating complex Excel formulas. Sure, it is possible to break formulas over several lines by inserting Alt-Enter into the string, and Excel 2007 and 2010 has improved matters somewhat with the resizable formula bar, but these are tweaks around the edges, the core problem remains (the resizable formula bar is a good idea in concept, but in practice it takes up far too much real-estate, which when added to the space gobbler that is the ribbon, makes it far from ideal).


So, inspired by Colin’s post, I set about to extend NotePad++ to support Excel formulas. Believe me, with the huge number of functions, creating tooltips and argument lists, this was no trivial task. I was amazed at the number of functions I hadn’t come across before, besides those that I never use.


Assuming you have NotePad++ to hand, this is how to extend NotePad++ for Excel. This duplicates much of what Colin has written, but it is worthwhile repeating for clarity.


Notepad++ saves language definitions in an XML User Defined Language file (UDL), you can start the XML User Defined Language editor from the menu (View -> User Define Dialog…), or from the toolbar button.


The first thing to do is to give your UDL an identifier, I call mine XLF here, and to specify the extension of any files to use this UDL, again mine is xlf.


The first tab allows setting up the folder options. An Excel formula has no obvious fold points, so I have invented two keywords, BBEGIN and BEND, that  I embed in the formula simply to provide the fold points. Clearly, these need to be removed before copy-pasting the formula back to Excel. Figure 1 shows my folder tab settings.



Figure 1.


The second tab is used to define the keywords, and the highlight colour. Figure 2 shows my Keyword settings. You can see that even though I have used all four keyword sets, they have the same attributes. This is because I used an Excel spreadsheet to generate this data (surprise!), and I grouped them in standard functions, 2007 new functions, 2010 new functions, and cube functions.  



Figure 2.


Next, we have in Figure 3 the tab for defining comments. Unfortunately, an Excel formula doesn’t support comments, so I use this just to add some preliminary comments if I want to save the formula in a file.



Figure 3.


Finally, figure 4 shows the operator settings.



Figure 4.


So far we have defined the UDL for our particular edit type, and when XLF is selected from the Language menu, any input will be formatted according to these settings. Figure 5. Shows a ‘typical’ Excel formula created in Notepad++.



Figure 5.


Where it gets really good is by adding intellisense, description and argument tooltips. To do this, you have to create an XML file with the same name as your chosen language id, that is XLF.xml in this case. This file is stored in the NotePad++ file directory, in Notepad++\plugins\APIs folder. The format of a typical function is shown in Figure 6.


<KeyWord name="AVERAGEIFS" func="yes">
  <Overload retVal="number" descr="Returns the average (arithmetic mean) for the cells specified by a given set of conditions or criteria. Excel 2007 and later">
    <Param name="average_range"/>
    <Param name="criteria_range1"/>
    <Param name="criteria1"/>
    <Param name="[criteria_range2]"/>
    <Param name="[criteria2]"/>
    <Param name="…"/>
  </Overload>
</KeyWord> 

Figure 6.


After creating all of the xml code (auto-generated in Excel for me), and building the XLF.XML file, when inserting a function in NotePad++ you get the following assistance, as shown in figures 7 and 8.


 



Figure 7.



Figure 8.


Update


Notepad++ has to be enabled to allow auto-complete and function tips. To do this, go to menu Settings>Preferences>Backup/Auto-Completion and check the Enable auto-completion and Function Parameters hint boxes, as shown in Figure 9. 



Figure 9.


To save you the chore of recreating all of this data, I have provided a zip file containing the XLF.xml file, and the exported UDL data. The XML file has to be stored in the Notepad++\plugins\APIs folder, and the UDL file can be imported within the UDL.

The US Isn’t The Whole World?




Microsoft sneaked a couple of new functions into Excel 2010. These functions are WORKDAY.INTL and NETWORKDAYS.INTL.


They appear to be addressing the age-old question of handling different working day patterns around the globe. Whilst we have Saturday and Sunday off, some countries may have Friday and Saturday. And of course, when planning work, some poor souls only get a single day of the week off. These new functions provide a new optional argument, weekend, to specify which days are to be treated as weekend days.


This should remove those complex formula solutions to handle this particular problem.


Unfortunately, MS as ever seem to have over-engineered it.


The help describes the weekend argument thus

Weekend  Optional. Indicates the days of the week that are weekend days and are not considered working days. Weekend is a weekend number or string that specifies when weekends occur

which suggests that we specify what days are in our weekend. But how do we define them?

Later the help provides a table of weekend values,

weekend-number Weekend days
1 or omitted Saturday, Sunday
2 Sunday, Monday
3 Monday, Tuesday 
4 Tuesday, Wednesday 
5 Wednesday, Thursday
6 Thursday, Friday
7 Friday, Saturday 
11 Sunday only
12 Monday only
13 Tuesday only
12 Wednesday only
15 Thursday only
16 Friday only
17 Saturday only

and later its says …

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string.

They present a table of decimal values then tell me that Weekend is a string of day indicators. Okay, I admit, it did say earlier that weekend is a weekend number or string that specifies when weekends occur, but why the need for two ways of telling it which days? And where is Monday, Tuesday and Wednesday as a combination in the table of values?

So I started looking at this, and my first thought was that weekend was a binary string, and the table above was the decimal numbers associated with the binary string.

First, in the string they have 1 representing a non-working day and 0 or a working day. I appreciate they call it weekends, but that does strike me as very odd.

Then, the decimal table presented doesn’t map to the binary values. Even allowing for their upside down logic, Saturday and Sunday weekends, or working days of Mon, Tue, Wed, Thu and Fri would be 0000011 which is 3, not 1.

The table of values shown doesn’t show all possibilities, such as weekend days of Saturday, Sunday and Monday, but the weekend string suggests that it will support this.

I can’t figure out that table of values, so even if it does support more than 2 weekend days, it doesn’t seem possible to use a decimal value to use in such circumstances. If I enter =WORKDAY.INTL(–“2010-09-06″,5,8), it returns #NUM.

All in all, I cannot help but conclude that having two ways of representing the weekends is pointless, the decimal values are limited, and the weekend string representation is so much simpler to envisage.

I have played with it, and the weekend string seems to work. =WORKDAY.INTL(–“2010-09-06″,5,”0010011″) gave me Tue 14-Sep, which is Tuesday, Thursday, Friday, Monday and Tuesday past the Monday of 6th Sep. It caters nicely for holidays as well.

Who Scratched My Table?

One of the better additions to Excel 2007 are, in my view, the functionality added to tables http://www.jkp-ads.com/articles/Excel2007tables.asp.


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


=$F2=”TBA”


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


Conclusion


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.


Summary


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.