Drill Down – Part 2

Before we get to coding, let’s look at the final product.  When we click our ‘easy’ button, our spreadsheet prompts us for dates, customers, and products.  If we just take the defaults, everything will be loaded from the NorthWinds database – like this:

Drill Down Before

If we look in the ‘Orders’ column we see that some lines have more than one order supporting them.  If we double click anywhere in the second line (say cell A6 for example) our finished spreadsheet will display those two orders so we can know the detail behind our summary.  It will look like this:

Drill Down After

Now, as mentioned in the previous post, we need to restructure our code a bit.  We need to eliminate the Macro1() in our module, and create three routines in the sheet’s class (for how to get to the sheet’s class review prior post).  So here is the first bit of code – constants that will make modifying this template for use on other databases easier:

Const sConnect = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};" & _
                 "DBQ=C:\Users\chatmaker\Documents\Northwind 2007.accdb;"
Const sData = "Data"
                 

These constants go at the very top of our sheet’s class.   And, as before, you will need to modify the path to your copy of the Northwind database (highlighted in red).

The next bit of code is the sheet’s double click event handler.  When you double click on worksheet ‘Data’, this even automatically fires.  All we need to do is wire it up to do what we want.  In this case, we want to load the detail associated with the Customer ID and Product Code on the line that is double clicked.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   
'   Worksheet_BeforeDoubleClick:  Excel's double click event handler

'   Parameters: Target  Cell that was double clicked
'               Cancel

'   Example:    <Automatically called when user double clicks worksheet>

'     Date   Init Modification
'   02/23/10 CWH  Initial Programming

    On Error GoTo ErrHandler            '
   
    If NameExists(sData) Then
       
        Dim lColDta As Long
            lColDta = Range(sData).Column
        Dim lColCus As Long
            lColCus = lColDta + FieldColumn("Customer ID", sData) - 1
        Dim lColPrd As Long
            lColPrd = lColDta + FieldColumn("Product Code", sData) - 1
       
        Get_Data "Fields_Detail", _
                 Trim(Cells(Target.Row, lColCus)), _
                 Trim(Cells(Target.Row, lColPrd)), _
                 frmPrompt.pFrom, frmPrompt.pTo
    End If
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Worksheet_BeforeDoubleClick - Error#" & Err.Number & vbCrLf _
        & Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Sub

As you can see, there’s not much to it.  But it requires a new routine ‘Get_Data’.  ‘Get_Data’ is half of our Macro1() – the part that doesn’t ask for any information but instead builds the SQL and submits it.  It looks like this:

Private Function Get_Data(sFields As String, _
                  sID1 As String, sID2 As String, _
                  dFrom As Date, dTo As Date) As Boolean

'   Get_Data:   Processes request from Prompt or Worksheet_BeforeDoubleClick

'   Parameters: sFields Name of the Field Definition table
'               sID1    Customer ID(s)
'               sID2    Product code(s)
'               dFrom   Ordered date range start
'               dTo     Ordered date range stop

'   Example:    If .pOK Then Get_Data "Fields", "8", "'NWC-14', 'NWCA-21'", _
'                                      Int(now())-30, Int(Now())

'     Date   Init Modification
'   02/23/10 CWH  Initial Programming

    On Error GoTo ErrHandler            '
    Get_Data = Failure                  'Assume the Worst

    Dim sSQL As String
           
    sSQL = "SELECT  " & Build_SQL_Select_Fields(sFields) & vbCr & _
           "FROM    Customers C, Orders O, " & vbCr & _
           "        `Order Details` D, Products P " & vbCr & _
           "WHERE   O.`Customer ID` = C.ID " & vbCr & _
           "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
           "  AND   D.`Product ID`  = P.ID " & vbCr & _
           "  AND   O.`Order Date` Between #" & _
                    Format(dFrom, "mm/dd/yyyy") & "# And #" & _
                    Format(dTo, "mm/dd/yyyy") & "# " & vbCr & _
            Build_SQL_ID("O.`Customer ID`", Trim(sID1), False) & vbCr & _
            Build_SQL_ID("P.`Product Code`", Trim(sID2), True) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By(sFields, "*")
    
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"

    If NameExists(sData) Then
        If Range(sData).Rows.Count > 1 Then
            Add_XLFormula sData, sFields
            Freeze_Pane sData, sFields
            Sort_Data sData, sFields
            Format_Results sData, sFields
            If sFields = "Fields_Detail" Then Pivot_Template
        End If
    End If
   
    Get_Data = Success                  'Successful finish

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Get_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

That should look very familiar to you.  The reason we split this from the part of Macro1() that asks for input is because we want only one routine to load data for us – whether the input comes from frmPrompt or from Worksheet_BeforeDoubleClick.

So now all we need to do is add back the part that sets up and displays frmPrompt.  It looks like this:

Private Sub Prompt()

'   Prompt:     Ask user for report parameters

'   Parameters: None
'   Example:    Prompt

'     Date   Init Modification
'   02/23/10 CWH  Initial Programming

    On Error GoTo ErrHandler            '

    With frmPrompt
               
        .pDateLbl = "Ordered Dates"
        .pFromVisible = True
        .pFrom = "01/01/2006"
        .pToVisible = True
        .pTo = Format(Now(), "mm/dd/yyyy")
       
        .pID1Visible = True
        .pID1Lbl = "Customer ID(s)"
        .pTitle1 = "Select Customers"
        .pConnect1 = sConnect
        .pLblCode1 = "Code"
        .pSQLCode1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    ID like '%?%' " & vbCr & _
                     "Order By ID"
        .pLblDesc1 = "Company"
        .pSQLDesc1 = "Select   ID as Code, " & _
                              "Company as Name " & vbCr & _
                     "From     Customers " & vbCr & _
                     "Where    Company like '%?%' " & vbCr & _
                     "Order By Company"
       
        .pID2Visible = True
        .pID2Lbl = "Product Code(s)"
        .pConnect2 = sConnect
        .pLblCode2 = "Code"
        .pSQLCode2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Code` like '%?%' " & vbCr & _
                     "Order By `Product Code`"
        .pLblDesc2 = "Name"
        .pSQLDesc2 = "Select   `Product Code` as Code, " & _
                              "`Product Name` as Name " & vbCr & _
                     "From     Products " & vbCr & _
                     "Where    `Product Name` like '%?%' " & vbCr & _
                     "Order By `Product Name`"
                    
       .Show                                         'Display the Prompt
        Do While .Visible                            'Wait on user
            DoEvents
        Loop
        If .pOK Then Get_Data "Fields", _
                              Trim(UCase(.pID1)), Trim(UCase(.pID2)), _
                              .pFrom, .pTo
    End With

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Prompt - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Sub

When you’re finished make sure to get rid of Macro1(), and then right click on the ‘easy’ button and assign it to macro ‘Sheet1.Prompt’. 

There’s one more advantage to structuring our code this way – almost all of the code that needs to be modified to use this template on just about any other database – AS/400, SQL Server, Oracle, MySQL, etc – on just about any table or set of tables – is contained in this one sheet.  This makes adapting this very quick and easy.

Drill Down – Part 1

Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows.  When we summarized the data, in a sense, we duplicated the functionality of a simple ”pivot table.” 

Pivot Tables have one feature we didn’t duplicate.  It’s a feature that always astonishes new Pivot Tables users.  That feature is the automatic “drill down” that happens when a value is double clicked, revealing the underlying detail.  Users love this and it’s not tough to add it to the summarized sheet we created last post.  The secret is in Excel’s Worksheet_BeforeDoubleClick event handler.  Using this event handler, we can respond to the user double clicking on a data row and using information in that data row, pull up the detail supporting it. 

Now because we are wading into deeper VBA waters, it’s appropriate to introduce some more polished coding techniques.  We will be dumping Macro1() in Module1.  Instead we are going to break it up into three routines.

1. Worksheet_BeforeDoubleClick – This will handle the drill down request
2. Prompt – Presents the user with our prompt form when they press the “easy” button.
3. Get_Data – Gets and presents the data based on the parameters from Prompt or Worksheet_BeforeDoubleClick

Worksheet_BeforeDoubleClick cannot reside module1.  It must be placed in the worksheet class.

Code in worksheet classes has some special properties we need to be aware of.  Code in a module is shared across ALL worksheets.  Routines in a worksheet are “local” to that worksheet.   That means, variables declared in, and code written in a worksheet class are unique to that worksheet.  Thus, we can have the same name for a routine in each worksheet with each routine meeting the specific needs of its own worksheet. 

The Worksheet_BeforeDoubleClick is a fine example of this.  Every worksheet has a Worksheet_BeforeDoubleClick routine.  By default, they are empty – so by default, they do nothing.  The Worksheet_BeforeDoubleClick in our Sheet1 will be different.  We will add code so it will “drill down” into the data.  But we ONLY want this behavior in Sheet1.  We don’t want our “drill down” routine to respond to double clicks in our “Fields” table, and we certainly don’t want it to override our Pivot Table’s drill down functionality.  Fortunately for us, a worksheet’s local event handlers ONLY respond to it and no other worksheet.

We also want to take advantage of this “local” nature of routines placed in the worksheet class.  As mentioned earlier, we are going to break up the Macro1() into three separate routines.  These routines will be specific to processing this particular dataset.  However, in some future project, you may want to have several worksheets in a workbook with each worksheet analyzing one set of data that, with the other sets, provides a suite of analytical tools.  By making the routines local, each worksheet can have its own version of Prompt and Get_Data with each version modified as needed to support the unique requirements of its own worksheet.

VBE Drill Down Project

To add code to Worksheet_BeforeDoubleClick we must:

1. Select Sheet1 from Project Explorer
2. Select Worksheet from the Code Panel’s left drop down
3. Select BeforeDoubleClick from the Code Panel’s right drop down

Excel will then provide a subroutine shell for us to add our specific code.   And that will be the subject of our next post.

Group By and Aggregate SQL Functions

At this point, we have everything we need for about 95% of user reporting and analytical requests.  On rare occasions we need to summarize the data prior to putting it into our Excel spreadsheets.  This happens when the raw detail for an expected time frame exceeds Excel’s normal 65,536 row limit and is more detailed than required for proper analysis.  An example might be a request to review orders for an entire year by customer and product.  In moderately sized companies, such requests could easily exceed 65,536 records.

We can work around this limitation by aggregating records so that several database records are represented in Excel as one row.  We do this with SQL’s aggregate functions which include:

COUNT(*), SUM(field), AVG(field), MAX(field), MIN(field), FIRST(field), LAST(field)

If you use these functions you must add a GROUP BY clause.  I touched on this last month in the post Adding SQL Formulas to the Table.  The basic rule for the GROUP BY clause is:

All fields in the SELECT clause that are NOT one of the SQL Aggregate Functions, MUST be included in the GROUP BY clause. 

GROUP BY clauses are tedious and often a source of errors for the SQL novice.  If you omit or mis-transcribe any SELECT field, your SQL will fail.  That’s why SQL novices typically fail on their first attempts with GROUP BY clauses.  Since the GROUP BY clause is nearly a replica of the SELECT clause, creating them is tedious.  So of course we need to automate that to make our work easier, increase our productivity, and improve the quality of our products.  Here is a routine that does just that (with a Fields Table to use test your results).

Fields Table for GROUP BY example

Function Build_SQL_Group_By(sFields As String, sTable As String) As String

'   Build_SQL_Group_By: Build SQL "Group By" field list

'   NOTE: SQL Aggregate Functions include:
'         Avg(),Count(),First(),Last(),Max(),Min(),Sum()

'   Parameters:
'       sFields:    Field definition range name.  It must contain:
'
'           Field:     Field/Column names
'           Alias:     File/Table Alias (ex: L for the table LOADS)
'           Table:     Field's Table name
'           SQL Func.: SQL function/expression

'       sTable:     Table being grouped.  "*" indicates all fields from
'                   all tables will be grouped

'   Example:
'       sSQL = sSQL & "Group By " & _
'              Build_SQL_Group_By("Fields", "*")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    On Error GoTo ErrHandler
    Build_SQL_Group_By = "" 'Assume Something went wrong

    Dim lRow As Long
    Dim sSQL As String
    Dim sSQLFunc As String  'Value in the SQL Func.
    Dim sFld As String      'Field with Alias
   
    Dim lColTbl As Long
        lColTbl = FieldColumn("Table", sFields)
    Dim lColFld As Long
        lColFld = FieldColumn("Field", sFields)
    Dim lColSQL As Long
        lColSQL = FieldColumn("SQL Func.", sFields)
    Dim lColAls As Long
        lColAls = FieldColumn("Alias", sFields)
   
    If Trim(sFields) = "" Or Trim(sTable) = "" Then Exit Function
   
    sSQL = ""
    With Range(sFields)
        For lRow = 2 To .Rows.Count
           'Include fields for indicated table
            If (sTable = .Cells(lRow, lColFld) Or sTable = "*") Then
                sSQLFunc = Trim(UCase(.Cells(lRow, lColSQL)))
               'Include fields that are not an aggregate function
                If sSQLFunc = "" Or _
                   InStr(1, "AVG,COU,FIR,LAS,MAX,MIN,SUM", _
                         Left(sSQLFunc, 3)) = 0 Then
                   'Combine Field with Alias
                    sFld = IIf(Trim(.Cells(lRow, lColAls)) > "", _
                               Trim(.Cells(lRow, lColAls)) & ".", "") & _
                           Replace(.Cells(lRow, lColFld), """", "'")
                   'If SQL Function has a "?", replace with Field
                    If InStr(1, sSQLFunc, "?") > 0 Then _
                        sSQLFunc = _
                            Replace(sSQLFunc, "?", sFld)
                   'If SQL Function, use it instead of Field
                    If sSQLFunc > "" Then sFld = sSQLFunc
                    sSQL = sSQL & IIf(sSQL > "", ", ", "") & sFld
                End If
            End If
        Next lRow
    End With
   
    Build_SQL_Group_By = sSQL

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Group_By - Error#" & Err.Number & vbCrLf & _
	Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Below is the change to our Macro1() highlighted in red.  Pivot_Template has also been removed because this example has no “State” column.

If .pOK Then                                 'OK button used to exit
           
    sSQL = "SELECT  " & Build_SQL_Select_Fields("Fields") & vbCr & _
           "FROM    Customers C, Orders O, " & vbCr & _
           "        `Order Details` D, Products P " & vbCr & _
           "WHERE   O.`Customer ID` = C.ID " & vbCr & _
           "  AND   O.`Order ID`    = D.`Order ID` " & vbCr & _
           "  AND   D.`Product ID`  = P.ID " & vbCr & _
           "  AND   O.`Order Date` Between #" & _
                    Format(.pFrom, "mm/dd/yyyy") & "# And #" & _
                    Format(.pTo, "mm/dd/yyyy") & "# " & vbCr & _
            Build_SQL_ID("O.`Customer ID`", Trim(.pID1), False) & vbCr & _
            Build_SQL_ID("P.`Product Code`", Trim(.pID2), True) & vbCr & _
            "GROUP BY " & Build_SQL_Group_By("Fields", "*")
    SQLLoad sSQL, sConnect, "A4", "Data", "Data"
   
    If NameExists("Data") Then
        If Range("Data").Rows.Count > 1 Then
            Add_XLFormula "Data", "Fields"
            Freeze_Pane "Data", "Fields"
            Sort_Data "Data", "Fields"
            Format_Results "Data", "Fields"
        End If
    End If
         
End If

Adding Excel Formulas – Part II

Last post showed the Fields Table column holding Excel Formulas to embed into your database result sets.  We also talked about how to enter formulas that reference database fields.  This is truly powerful stuff that turns basic data extracts into analytical information.  So let’s start adding the code that makes this all happen.

NOTE: If you added Build_SQL_Select_Fields prior to February 08, 2010, you’ll need to change that version to what is now posted in: Adding Tables and Columns to the Fields Table originally posted on 01/09/2010.  It contained an error.  Sorry.

Today’s code includes two functions: Add_XLFormula and Parse_XLFormula.  The change to Macro1() is shown at bottom.  The line to call Add_XLFormula  needs to be before sorting and formatting because the results can be treated just like fields retrieved from the database.  The in code documentation explains how to use the functions well enough – as it should.

Function Add_XLFormula(sDataRange As String, sFieldRange As String) As Boolean
      
'   Add_XLFormula: Add XL formula fields to results ONLY after data is loaded
'   Parameters:    sDataRange  = Name of range containing data
'                  sFieldRange = Name of range containing field definitions
'   Example:       bResult = Add_Defaults("Data", "Fields_Data")

'     Date   Init Modification
'   01/12/06 CWH  Initial Programming
   
    Add_XLFormula = Failure       'Assume the Worst
    On Error GoTo ErrHandler

'   Dim Statements
    Dim lCol As Long              'Column in Data Range receiving formula
    Dim lRow As Long              'Current Row in Fields Table
    Dim lRows As Long             'Number of Rows in Fields Table
    Dim Formula As String         'Formula string
    Dim bIsArray As Boolean       'Is this an Array formula?
   
    Dim lColXLF As Long
        lColXLF = FieldColumn("XL Func.", sFieldRange)
   
    With Range(sFieldRange)
        For lRow = 2 To .Rows.Count
            sFormula = Trim(.Cells(lRow, lColXLF))
            If sFormula > "" Then
                'Check first if this is an Array Formula
                 bIsArray = Left(sFormula, 3) = """{=" Or _
                            Left(sFormula, 2) = "{="
                 If bIsArray Then
                     sFormula = Replace(sFormula, "{", "", 1, 1)
                     sFormula = Left(sFormula, Len(sFormula) - 1)
                 End If
                 
                 sFormula = Parse_XLFormula(lRow, sFormula, sFieldRange)
                 With Range(sDataRange)
                     lCol = lRow - 1
                     If Not bIsArray Then
                         Range(.Cells(2, lCol), _
                               .Cells(.Rows.Count, lCol)).FormulaR1C1 = _
                               sFormula
                     Else
                         .Cells(2, lCol).FormulaArray = sFormula
                         Range(.Cells(2, lCol), _
                               .Cells(.Rows.Count, lCol)).FillDown
                     End If
                 End With
            End If
        Next lRow
    End With
           
    Add_XLFormula = Success

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Add_XLFormula - Error#" & Err.Number & vbCrLf & Err.Description & vbCr _
            & sFormula, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
 
End Function

Function Parse_XLFormula(lFld As Long, sFormula As String, _
                         sFieldRange As String) As String
   
'   Parse_XLFormula:Create an XL formula using defintion in Fields Table
'   Parameters:     sFormula    A string containing a coded formula
'                   sFieldRange Name of Fields Table range

'   Example:        range("A4").FormulaR1C1 = _
'                       Parse_XLFormula(2, "=RC{MILES}/RC{GALLONS}", "Fields")

'   Notes:          sFormula can contain references to other Fields/Columns
'                   in the data range by placing {} around the Field/Column
'                   name.  This routine will replace these references with
'                   XL's R1C1 notation.  Examples:

'         Input     "=RC{MILES}/RC{GALLONS}"  Same as "={MILES}/{GALLONS}"
'         Result    "=R1C[3]/R1C[2]"          (Representative result)
'         Input     "=Sum(C{MILES})"          "C" w/no "R" gets entire column
'         Result    "=Sum(C[3])"              (Representative result)

'                   You may use the Field Name or the Heading.  I recommend
'                   using the Field Name because if you change the Heading
'                   the formula will break and you must change it as well

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   02/05/10 CWH  Allowed Field or Heading to be used in Formula string
   
    On Error GoTo ErrHandler
    Parse_XLFormula = ""
   
    Dim i As Integer
    Dim lRow As Long
    Dim lRows As Long
    Dim lBeg As Long    'Start of Field Reference
    Dim lEnd As Long    'End of Field Reference
    Dim s As String     'Field Reference string
    Dim sRC As String   'Prefix for Cell Reference
   
    Dim lColFld As Long
        lColFld = FieldColumn("Field", sFieldRange)
    Dim lColHdg As Long
        lColHdg = FieldColumn("Heading", sFieldRange)
       
   'If user put formula in double quotes, remove them
    If Left(sFormula, 1) = """" Then 
        sFormula = Right(sFormula, Len(sFormula) - 1)
        If Right(sFormula, 1) = """" Then _
            sFormula = Left(sFormula, Len(sFormula) - 1)
    End If
    i = 0
       
    With Range(sFieldRange)
        lRows = .Rows.Count
        Do
           'Search for left curly bracket - designating start of a field
            lBeg = InStr(1, sFormula, "{")
            If lBeg > 0 Then
                lEnd = InStr(1, sFormula, "}")
               's equals name of a field, or heading in the Fields Table
                s = Mid(sFormula, lBeg + 1, lEnd - 1 - lBeg)
               'Determine if an RC reference was included
                sRC = UCase(Mid(sFormula, lBeg - 1, 1))
                If sRC <> "R" And sRC <> "C" Then
                    sRC = "RC"  'User didn't specify. Add RC (single cell)
                Else
                    sRC = ""    'User specified so don't add anything
                End If
               'Search Fields Table to determine field # referenced
                For lRow = 2 To lRows
                    If Trim(.Cells(lRow, lColFld)) = s Or _
                       Trim(.Cells(lRow, lColHdg)) = s Then
                        sFormula = Left(sFormula, lBeg - 1) & _
                                   sRC & "[" & lRow - lFld & "]" & _
                                   Right(sFormula, Len(sFormula) - lEnd)
                        Exit For
                    End If
                Next lRow
            End If
            i = i + 1
        Loop Until lBeg = 0 Or i = 20 'Limit to 20 references
    End With
               
    Parse_XLFormula = sFormula

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Parse_XLFormula - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Macro1() new line in red:

 
If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Add_XLFormula "Data", "Fields"
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Format_Results "Data", "Fields"
        Pivot_Template
    End If
End If

 
			

Adding Excel Formulas

It’s great that we can download and display data in Excel where users can sort it, filter it, print it… whatever they want to it.  It’s great that we can summarize that data in interactive pivot tables.  It’s great that we can display those pivot tables in interactive charts.  Those of you that have been following along now have a spreadsheet you can use as a template to do all those things.  But there’s one thing that really makes these techniques a true replacement for traditional paper based, batch run reports – calculations.

Traditional programmers are accustomed to adding complex calculations to the reports they provide end users.  End users often want reports that calculate the age of an invoice, or the percent of sales overall or within a geographic region.  Without complex calculations, these techniques couldn’t replace traditional methods.  Well – get ready to leave traditional methods behind.  Stop killing trees and start replacing all of those paper based reports with Excel because we are adding all of Excel’s rich library of functions directly into our results simply by adding the formula to our Fields Table.  Below is our new Fields Table, complete with “XL Func.” column.

Fields Table with Excel Functions (click to enlarge)

Examine the examples here.  There are three major categories of Excel functions: cell to cell; entire column based; and array formulas. 

Entering Cell to Cell formulas:
Cell to cell is by far the most common formula type.  You’ve probably used them frequently already.  In this example we are calculating the age of the orders by subtracting the order date from today’s date.

'=Int(Now())-int({`Order Date`})

- or its R1C1 equivalent
'=Int(Now()-int(RC[-1])

Instructions:

• Reserve space in your result set by:
  • Entering *None in the Table column or leave it empty
  • Leaving the Alias column empty
  • Entering “” (two double quotes) in the Field column
  • Entering a suitable column heading in the Heading column 
• Enter formulas in this table with a leading single quote ‘ or surround them with double quotes “.  This prevents Excel from trying to calculate a result in this table.  We want Excel to calculate results only in our result set.
• To use result fields in your formulas, type the Field name or its Heading and surround it with curly brackets {}.  Field names don’t change (normally).  Column Headings might.  For that reason I recommend using Field names when possible so if the end user wants a column heading changed from ‘Ship State/Province’ to ‘State’, you don’t have to change the formula as well.  Field names don’t exist for calculated fields so you have to use Heading references for them.   
• If you don’t use result field references (which I strongly urge), formulas MUST be entered in the R1C1 notation and should use relative  positions.  Relative positions are prefered so formulas don’t have to be changed if fields are added to the result.
  • This is a relative position in R1C1 notation designating one column to the left of the cell receiving the formula: RC[-1]. 
  • This is a fixed position in R1C1 notation designating the third column: RC3.  

Entering Column based formulas:
Column based formula types aren’t so common but still have their uses.  In this example we are summing the total quantity and placing the result in each row.  This is a bit inefficient for the computer, but very efficient for you.  For reports that generate small result sets, this method will probably make more sense than coding an alternative.  To designate an entire column, place a “C” in front of the field reference. 

'=Sum(C{Quantity})

- or its R1C1 equivalent
'=Sum(C[-1])

Entering Array formulas:
Since the introduction of the SumIF command, I haven’t had much use for Array Formulas.  But for older versions of Excel, Array Formulas are the only equivalent.  In this example, we are getting the total for the row’s State.

'{=SUM(
  IF(C{State}={State},
     C{Quantity},0)
  )}

- or its R1C1 equivalent
'{=SUM(
  IF(C[-5]=RC[-5],
     C[-3],0)
  )}

Special Instructions:

• Surround Array Formulas with curly brackets {}.  These MUST be in the first and last positions of the formula (inside any quotes). 
• You do not need to format the formulas with line breaks like I do (Alt-Enter).  It’s just my personal preference.

Next Post
I spent a good deal of blog space to explaining how to enter formulas.  Next post will be the coding.  See you then.

Setting Column Widths or Hiding Them

Fields Table with Width and Hide Columns

Once again, all we need do is add the appropriate columns to our Fields Table (don’t forget to rename it with Shift-Ctrl-N) and sprinkle in a little VBA code.  This time, there are no changes required to Macro1().  We will be changing the Format_Results function only.

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
   
'   Format_Range:   Apply numeric formatting or alignment

'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   02/02/10 CWH  Added Column Width and Column Hide functionality
   
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
   
    Dim s As String
    Dim lColFmt As Long         '"Format" in Fields Table
    Dim lColWid As Long         '"Width"  in Fields Table
    Dim lColHid As Long         '"Hide"   in Fields Table
    Dim lRow As Long
    Dim lRows As Long
   
    lRows = Range(sDataRange).Rows.Count
       
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                'Formatting...
                s = Range(sFieldRange).Cells(lRow, lCol)
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select

            End With
            With Range(sDataRange).Columns(lRow -1)
               'Set Column Width ...
                s = Trim(Range(sFieldRange).Cells(lRow, lColWid))
                If Val(s) > 0 Then .ColumnWidth = Val(s)
               'Hide Column...
                s = UCase(Trim(Range(sFieldRange).Cells(lRow, lColHid)))
                If s = "H" Then .EntireColumn.Hidden = True
            End With


        Next
    End With
           
    Format_Results = Success    'Normal end, no errors

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Adding Formatting

Now it’s time to add basic formatting.  Formatting is important.  Accountants must see currency values formatted their special way.  Dates must be presented according to regional standards.  Fortunately, adding formatting to your results is as easy as adding one more column to our Fields table and one more routine to our library.   See below:

Fields Table w/Format

You can add any one of the following formatting to your results:

Below is the new routine to add to your library and the modifications to Macro1().

Function Format_Results(sDataRange As String, sFieldRange As String) As Boolean
   
'   Format_Range:   Apply numeric formatting or alignment

'   Parameters:     sDataRange  = Range containing data
'                   sFieldRange = Range containing data's field definitions
'   Example:        bResult = Format_Results("Data", "Fields")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Format_Results = Failure    'Assume the worst
    On Error GoTo ErrHandler
   
    Dim s As String
    Dim lCol As Long
    Dim lRow As Long
    Dim lRows As Long
   
    lRows = Range(sDataRange).Rows.Count
       
    With Range(sFieldRange)
        lCol = FieldColumn("Format", sFieldRange)
        For lRow = 2 To .Rows.Count 'Start after column header row
            s = Range(sFieldRange).Cells(lRow, lCol)
            With Range(Range(sDataRange).Cells(1, lRow - 1), _
                       Range(sDataRange).Cells(lRows, lRow - 1))
                Select Case UCase(s)
                    Case Is = "C"
                        .HorizontalAlignment = xlCenter
                    Case Is = "R"
                        .HorizontalAlignment = xlRight
                    Case Is = "L"
                        .HorizontalAlignment = xlLeft
                    Case Is = "W"
                        .WrapText = True
                    Case Is > ""
                        .NumberFormat = s
                End Select
            End With
        Next
    End With
           
    Format_Results = Success    'Normal end, no errors

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Format_Results - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function

Macro1() new line in red:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Format_Results "Data", "Fields"
        Pivot_Template
    End If
End If

Adding Sorting

Another common requirement for reports is sorting.  While this can be done in SQL with an ORDER BY clause, I like to use Excel to sort because the desktop PC usually has a much higer CPU-Power-to-Number-of-Users ratio than a database server.  Today, we add sort columns to the Fields table.

Fields Table w/Sort

 Once again, we need to add the columns to our Fields table, redefine it (using Ctrl-Shift-N), add a routine, and modify Macro1(). 

Function Sort_Data(sDataRange As String, sFieldRange As String) As Boolean

'   Sort_Data:          Sorts data based on settings in the field definition table
'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = range containing data's field definitions
'   Example:            bResult = Sort_Data("Data", "Fields")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming

    Sort_Data = Failure         'Assume the Worst
    On Error GoTo ErrHandler

    Settings "Save"             'Save current settings
    Settings "Disable"          'Disable events, screen updates, and calculation
          
    Dim i As Integer            'Generic Counter
    Dim lColOrd As Long         'Sort Order Column in FieldRange (1st, 2nd, etc.)
    Dim lColHdg As Long         'Column Headings Column in FieldRange
    Dim lColSeq As Long         'Sort Seq. Column in FieldRange (Asc, Desc)
    Dim lRow As Long            'Row in FieldRange
    Dim sField() As String      'Sort Field
    Dim sSequence() As String   'Sort Sequence
   
    With Range(sFieldRange)
        ReDim sField(.Columns.Count - 2)
        ReDim sSequence(.Columns.Count - 2)
        lColOrd = FieldColumn("S.Ord", sFieldRange)
        lColHdg = FieldColumn("Heading", sFieldRange)
        lColSeq = FieldColumn("S.Seq", sFieldRange)
        For lRow = 2 To .Rows.Count
            If Val(.Cells(lRow, lColOrd)) > 0 Then
                sField(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColHdg)
                sSequence(.Cells(lRow, lColOrd) - 1) = .Cells(lRow, lColSeq)
            End If
        Next
    End With
   
    With Range(sDataRange)
        Worksheets(.Worksheet.Name).Activate
        .Cells(2, .Column).Select
        For i = UBound(sField, 1) To 0 Step -1
            If sField(i) > "" Then
                Selection.Sort _
                    Key1:=sField(i), _
                    Order1:=IIf(UCase(Left(sSequence(i), 1)) = "A", _
                                xlAscending, xlDescending), _
                    DataOption1:=xlSortTextAsNumbers, _
                    Header:=xlYes, _
                    OrderCustom:=1, _
                    MatchCase:=False, _
                    Orientation:=xlTopToBottom
            End If
        Next i
    End With

    Sort_Data = Success

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Sort_Data - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error Resume Next
    Settings "Restore"     'Restore previous settings
    On Error GoTo 0

End Function

Add the Sort_Data routine (highlighed in red) to Macro1() as shown:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Sort_Data "Data", "Fields"
        Pivot_Template
    End If
End If

Preserving Column Headers and Key Values

Fields Table with Freeze Column

“Freeze” tells our routines which columns to keep in view no matter where the user scrolls to.  If you don’t want any, I recommend putting a “Y” in your first field.  This will preserve the column headings at minimum.  You can only indicate one field (or none) to freeze.

Add “Freeze” as shown to your fields table and remember to resize the named range “Fields” by placing your cursor on cell “A3″ and pressing Ctrl-Shift-N.

Below is the “Freeze Panes” routine and a minor modification to our Macro1().

Function Freeze_Pane(sDataRange As String, sFieldRange As String) As Boolean

'   Freeze_Pane:        Locks columns and rows from scrolling
'                       (Used to keep column headers and key data displayed)

'   Parameters:         sDataRange  = Range containing data
'                       sFieldRange = Range containing data's field definitions
'   Example:            bResult = Freeze_Pane("Data", "Fields_Data")

'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
   
    Freeze_Pane = Failure          'Assume the Worst
    On Error GoTo ErrHandler

    Settings "Save"     'Save current settings
    Settings "Disable"  'Disable events, screen updates, and calculation
       
    Dim lCol As Long
    Dim lRow As Long
   
    Worksheets(Range(sDataRange).Worksheet.Name).Activate
    ActiveWindow.FreezePanes = False
   
    With Range(sFieldRange)
        lCol = FieldColumn("Freeze", sFieldRange)
        For lRow = 2 To .Rows.Count
            If UCase(Trim(.Cells(lRow, lCol))) = "Y" Then
                Range(sDataRange).Cells(2, lRow).Select
                ActiveWindow.FreezePanes = True
            End If
        Next
    End With

    Settings "Restore"     'Restore previous settings

    Freeze_Pane = Success

ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Freeze_Pane - Error#" & Err.Number & vbCrLf & Err.Description, _
            vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0

End Function 

Modifications to Macro1().
Find the line beginning with If NameExists and replace it with this:

If NameExists("Data") Then
    If Range("Data").Rows.Count > 1 Then
        Freeze_Pane "Data", "Fields"
        Pivot_Template
    End If
End If

Adding SQL Formulas to the Table

In the last post we linked the Fields Table to some code that translated the table into an SQL Select clause.  Most often, that’s enough.  But sometimes we need to manipulate the data before loading it into our result spreadsheet.  We can do that by adding SQL functions like MID( ), ROUND( ), SUM( ), or in the case below, a simple concatenate. 

Fields Table w/SQL Func.

In this example, I have added a column “SQL Func.”, hit Ctrl-Shift-N (to resize the named range “Fields” to include the new column), and entered a simple formula to combine the customer’s last name with the first name.  The result will look like “Hatmaker, Craig”.

NOTE: Microsoft’s Access and SQL Server concatenate with the “+” (plus) sign.  DB2 and Oracle use “||”.  Oracle also uses CONCAT(a, b) as does MySQL.

NOTE: Some SQL functions aggregate multiple rows of data, like MAX( ), MIN( ), COUNT( ), etc.  These SQL functions require a GROUP BY clause.  If you use one of them, you must add the proper GROUP BY clause to your SQL Select Statement or it will fail. 

Now here’s the nice part.  There’s no coding this time.  This feature/functionality (without the aggregating functions) was included in the code from the last post.