Beyond Excel: VBA and Database Manipulation


February 9, 2010  9:23 PM

Adding Excel Formulas – Part II

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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

 

February 6, 2010  5:37 PM

Adding Excel Formulas

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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)

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.


February 2, 2010  5:57 PM

Setting Column Widths or Hiding Them

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Sometimes we want to include large fields in our results but don’t want them hogging screen realestate.  We can limit those results using the “Width” colum in our Fields Table (See below).  If you couple this with the Format “W” (Wrap), you can display comments and notes without pushing all other data off the screen.
 
Sometimes we want results that are only occasionally used.  We can elect to hide those columns with the Format Table’s Hide column.  If the user wishes to display those rarely used columns, they can use Excel’s Column Unhide or AutoFit commands. 
 
Sometimes we include data in our results required for some calculation, such as aging an invoice, but we don’t care to see the underlying data.  We can hide them as well – and in our next post we will be adding Excel formulas to the Fields Table.
Fields Table with Width and Hide Columns

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


January 25, 2010  6:06 PM

Adding Formatting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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

Fields Table w/Format

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

Value Result
L Left align – the default for character results
R Right align – the default for numeric results
W Wrap text to fit
mm/dd/yyyy This is a typical date format. You can also use any data format string allowed by Excel
#,###/00 This is a typical number format. You can also use any number format string allowed by Excel
$#,##0_);[Red]($#,##0) This is a typical currency format in the US. You can also use any currency format string allowed by Excel
You get the idea – anything allowed by Excel as a format works

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


January 21, 2010  5:58 PM

Adding Sorting

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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

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


January 18, 2010  6:15 PM

Preserving Column Headers and Key Values

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

When retrieving large amounts of data, we need to preserve displaying column headers and key values.  Key values should be on the left of your extract.  With that in mind, today we add column “Freeze” to our Fields Table. 

Fields Table with Freeze Column

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


January 14, 2010  6:02 PM

Adding SQL Formulas to the Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

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.

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.


January 9, 2010  12:04 PM

Adding Tables and Columns to the Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In my last post I provided some simple tools to manage Excel tables.  This was in preparation for creating and incorporating a “Fields Table” into our reporting system.  The advantages of the Fields table are enumerated in the prior posts.  So lets start using it.

The first portion of our Fields table are the basic elements of any database function:

  • Database tables and their aliases
  • Table columns and their column headers. 
Basic Fields Table

Basic Fields Table

By putting them in a table, we no longer have to write that tedious portion of our SQL statement AND it facilitates collaborative development with our users who can sit down with us and readily understand how changing the row order can rearrange the column order and changing the heading value changes the column headings in their final product.  They feel involved.  They feel empowered.  They gain a sense of ownership.  You gain their trust.

NOTES:

  • If your Table or Field has spaces, you must enclose it in brackets or “`”.   
  • If you change the Heading, you must also change the field names in your Pivot Tables macro to match.
  • Aliases are optional, but if you use one in your “From” clause, you MUST use the same one here. 

Instructions:

  1. Create a new tab on your spreadsheet and label it “Tables”
  2. Populate the cells as you see in the picture above (formatting is optional).
  3. If you didn’t do it last time, attach the macro “Create_Named_Range” to Ctrl-Shift-N using the “Macro” option from the “View” or “Developer” menus (Office 2007) then . . .
  4. Put the cursor in cell A3 and press Ctrl-Shift-N to name the range “Fields”.  Click OK.
  5. Go to the VBE (Alt-F11) and copy the code below and add it to your module.

Below you will find:

  • Build_SQL_Select_Fields - reads the fields table and creates a portion of our SQL Select statement
  • FieldColumn – used by Build_SQL_Select_Fields to find the column of a field in an Excel table  
  • Macro1() – Modified to use Build_SQL_Select_Fields .

Function Build_SQL_Select_Fields(sFields As String) As String

'   Build_SQL_Select_Fields: Build from an XL table a string to
'                            use as an SQL "Select" list of fields
'   Parameters:
'       sFields     Name of range containing field definitions
'   The "sFields" table range MUST contain:
'       Table:      Database File/Table name
'       Field:      Database Field/Column name
'   The "sFields" table range MAY contain:
'       Alias:      Database File/Table Alias (ex: L for table LOADS)
'                   WARNING:  Do not omit Alias if alias used in FROM clause
'       Heading:    Column heading for field in Excel report
'       SQL Func.:  It may contain a valid SQL function such as:
'                   SUM(?)          Totals the values in this field.
'                   TRIM(?)         Removes spaces
'                   SUBSTR(?,#,##)  Takes a substring from the field's value
'                   "?" is required.  It identifies where Field/Column name goes
'   Example:        sSQL = "Select  " & Build_SQL_Select_Fields(sFieldRange) & vbCr
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
'   01/21/10 CWH  Changed "As" section to remove requirement for
'                 brackets and quotes in Heading column for headings with spaces
    On Error GoTo ErrHandler
    Build_SQL_Select_Fields = ""    'Assume Something went wrong
   
    Dim lRow As Long
    Dim lCol As Long
    Dim lColA As Long
    Dim sSQL As String
    Dim sCRTB As String
       
    sCRTB = vbCr & vbTab & vbTab
   
    With Range(sFields)
        For lRow = 2 To .Rows.Count
            'Add a comma if this not the first field
            sSQL = sSQL & IIf(sSQL > "", ", " & sCRTB, "")
           
           'Add Alias
            lCol = FieldColumn("Table", sFields)
            If lCol > 0 Then sSQLField = Trim(.Cells(lRow, lCol))
           'Use File/Table name if Alias didn't exist
            If sSQLField = "" Then
                lCol = FieldColumn("Table", sFields)
                If lCol > 0 Then sSQLField = .Cells(lRow, lCol)
                If UCase(Trim(sSQLField)) = "*NONE" Then sSQLField = ""
            End If
           
            'Add the Field/Column to the string
            sSQLField = sSQLField & _
                IIf(sSQLField > "", ".", "") & _
                .Cells(lRow, FieldColumn("Field", sFields))
           
            'Replace double quotes with single quotes
            sSQLField = Replace(sSQLField, """", "'")
           
            'Embed result in "Select Function" if specified
            lCol = FieldColumn("SQL Func.", sFields)
            If lCol > 0 Then _
                If InStr(1, .Cells(lRow, lCol), "?") > 0 Then _
                    sSQLField = _
                        Replace(.Cells(lRow, lCol), "?", sSQLField)
           
            'Add "As" if a column heading is specified
            lCol = FieldColumn("Heading", sFields)
            If lCol > 0 Then
                s = Trim(.Cells(lRow, lCol))
                If s > "" Then
                    If InStr(1, s, " ") > 0 Then s = "[" & s & "]"
                    sSQLField = sSQLField & " as " & s
                End If
            End If           
            sSQL = sSQL & sSQLField
        Next lRow
    End With
    Build_SQL_Select_Fields = sSQL
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Build_SQL_Select_Fields - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function
 
Public Function FieldColumn(sField As String, sRange As String) As Integer
      
'   FieldColumn:    Determine a field's relative column number within a named range
'   Parameters:     sField - The Column Heading we're looking for
'                   sRange - The name of the range where it should be
'   Example:        lCol = FieldColumn("Name", "Customers")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    FieldColumn = 0     'Assume not found
   
    Dim c As Range
   
    If Trim(sField) > "" Then
        Set c = _
            Worksheets(Range(sRange).Worksheet.Name).Range(sRange).Rows(1).Find( _
            sField, , , xlWhole, xlByColumns, xlNext, False)
        If Not c Is Nothing Then FieldColumn = c.Column - Range(sRange).Column + 1
    End If
   
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "FieldColumn - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

Find the SQL= line in Macro1() and change it to this (red portion is to highlight change only):

           
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)


December 31, 2009  9:08 AM

Creating the Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

I hope you had a Merry Christmas and will have a great new year.  My heart goes out to the drivers and office workers of Arrow Trucking.  ABC News reported on Christmas day that Arrow Trucking went bankrupt.  Management cancelled driver’s fuel cards leaving them stranded miles from home.  Management handled it poorly.  And while the economy is a huge factor in this tragedy, we, as employees need to remember that our company’s success or failure depends greatly on how efficiently we do our job.  That’s what this blog is all about.  It’s about delivering real value at the lowest cost.  I hope you will continue to follow this blog and employee these methods.  If you have better methods, please share through comments.  Thanks.

Last post I introduced an Excel table that holds the definitions for our reports.  Using a table instead of code simplifies and speeds adapting our report to other needs, facilitates collaboration with our customers, and concentrates the bulk of changes into an easy to modify form.  Its value is tremendous.  The cost to setup initially is significant.  Fortunately we don’t need to add everything all at once.  We can cut the table into chunks and derive value from those chunks fairly quickly.

The first chunk will concentrate on four of the first few columns:

Column Description
Table Name of the database table that contains the field (see “Field” column)
Alias An abbreviation of the table name. Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS. This shortens the select statement (improving readability) while identifying which field comes from which table.
Field Name of the database field/column in the table (see “Table” column)
Heading Column heading for the field displayed in the worksheet.

We will use these columns to build a portion of our SQL Select clause for us.

Before we get started, I want to introduce a little utility that will help us as we adjust the table because we’re going to be adjusting it a lot.  The utility is a macro that helps create a named range from data entered on a spreadsheet.  It’s called: Create_Named_Range. 

Create_Named_Range is roughly the equivalent of highlighting an area on the spreadsheet and using Excel’s “Define Name” menu option.  It adds two niceties.  It assumes the table’s name is in the cell two rows above the table and finds the area for you.  I tie the macro to Ctrl-Shift-N so all I have to do is enter the table into my spreadsheet, label it, put the cursor in the upper left cell of the table, and press Ctrl-Shift-N.  The macro discovers my table’s area and names it with the table’s label.

Create_Named_Range requires a function called Fix_Names.  Fix_Names replaces or removes any illegal characters from a string so Excel can accept it as a name.  I’ve included both macros below:

Public Sub Create_Named_Range()
'   Create_Named_Range: Create a named range
'   Parameters:     None
'   Example:        This is meant to be attached to Shift-Ctrl-N. 
'                   The user positions the cursor in the upper
'                   left corner of a table, presses Shift-Ctrl-N,
'                   a prompt asks for the range's name
'                   (it will default to any entry in the cell 2 rows up),
'                   then this routine finds the bottom and right
'                   coordinates for the table.  The left most column
'                   MUST contain data in every row and the top must row
'                   MUST contain data in every column.
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Dim lCol As Long
    Dim lCols As Long

    Dim lRow As Long

    Dim lRows As Long

    Dim sName As String
   
    sName = InputBox("Enter Range Name:", _
                     "Create Named Range", _
                     IIf(Selection.Row > 2, _
                         Selection.Cells(-1, 1), _
                         "Data"))
    sName = Fix_Name(sName)
    With Selection
       'Find last column
        lRow = 1
        lCol = 1
        Do While .Cells(lRow, lCol) <> ""
            lCol = lCol + 1
        Loop
        lCols = lCol - 1
       'Find last Row
        lRow = 1
        lCol = 1
        Do While .Cells(lRow, lCol) <> ""
            lRow = lRow + 1
        Loop
        lRows = lRow - 1
        Names.Add sName, Range(.Cells(1, 1), .Cells(lRows, lCols))
    End With
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Create_Named_Range - Error#" & Err.Number & vbCrLf & _
        Err.Description, vbCritical, "Error", Err.HelpFile, _
        Err.HelpContext
    On Error GoTo 0
End Sub
 
Public Function Fix_Name(sName As String) As String
'   Fix_Name:       Conforms a string so it can be used as a
'                   field/column name for database functions
'   Parameters:     sName - The string to be conformed
'   Example:        sColumnName = Fix_Name("1st deposit %")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler
    Fix_Name = sName
   
    Dim l As Long
           
    sName = Replace(sName, "#", "_NUM")
    sName = Replace(sName, "$", "_AMT")
    sName = Replace(sName, "%", "_PCT")
    sName = Replace(sName, "=", "")
    sName = Replace(sName, "-", "_")
    sName = Replace(sName, ",", "")
    sName = Replace(sName, ".", "_")
    sName = Replace(sName, ":", "")
    sName = Replace(sName, "/", "")
    sName = Replace(sName, "\", "")
    sName = Replace(sName, "'", "")
    sName = Replace(sName, " ", "_")
    Select Case Left(sName, 1)
        Case Is = 1
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "ST", 3, 1)
            sName = "FIRST" & Right(sName, l)
        Case Is = 2
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "ND", 3, 1)
            sName = "SECOND" & Right(sName, l)
        Case Is = 3
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "RD", 3, 1)
            sName = "THIRD" & Right(sName, l)
        Case Is = 4
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "FOURTH" & Right(sName, l)
        Case Is = 5
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "FIFTH" & Right(sName, l)
        Case Is = 6
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "SIXTH" & Right(sName, l)
        Case Is = 7
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "SEVENTH" & Right(sName, l)
        Case Is = 8
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "EIGHTH" & Right(sName, l)
        Case Is = 9
            l = Len(sName) - IIf(UCase(Mid(sName, 2, 2)) = "TH", 3, 1)
            sName = "NINETH" & Right(sName, l)
    End Select
    Fix_Name = sName
ErrHandler:
   
    If Err.Number <> 0 Then MsgBox _
        "Fix_Name - Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

In the next post we will start using the table.


December 19, 2009  2:23 PM

The Fields Table

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker
Fields Table

Fields Table

The day before yesterday, one of my internal customers asked for a listing of an external customer’s orders.  Orders had to be selected based on dates, various subsidiary customer accounts, order statuses, etc.  and totalled along specific lines.  Our external customer wanted us to provide this to them because their own systems professionals couldn’t, or wouldn’t.  My internal customer said he needed it quick and that meant he really, really needed it the next day.  We handed it to him within a few minutes.  My customer beamed how happy he was that we could promise these reports and deliver.  It’s a competitive advantage.  When our external customers evaluate who to procure services from, this level of customer service may be the convincing factor that tips their business our way.

Last post I talked about how to take our reporting to the next level using Excel Tables.  Today, I’m introducing that table – the “Fields” table.  At right is a thumbnail of the table.  If you click it, you can see it in its entirety (It’s too wide for this blog’s format).  It contains most of the parameters necessary to describe data elements for a data extracts.  Here are descriptions of the various columns:

Column Description
Table Name of the database table that contains the field (see “Field” column) 
Alias An abbreviation of the table name.  Usually this is a one character tag like O for ORDERS, or C for CUSTOMERS.  This shortens the select statement (improving readability) while identifying which field comes from which table. 
Field Name of the database field/column in the table (see “Table” column)
Key Identifies fields/columns as part of the table’s unique key 
Heading Column heading for the field displayed in the worksheet. 
S.Ord. (Sort Order) Identifies which fields are sorted on first, second, third, etc.  There is no limit to the number of sort fields. 
S.Seq. (Sort Sequence) Identifies “Ascending” or “Descending” sort sequence for the column.
Freeze Identifies which field to freeze Excel’s window pane on.  This is usefull for keeping column heading and key values displayed at all times, no matter which cell you’re in. 
Sel.Func. (Selection Clause Function) Identifies an SQL formula to use on the field.  Use a “?” to identify where the Field appears in the formula. 
Hide Use “H” to hide the column completely.  Use “C” to “conditionally” grey out repetitive values.  This is useful for extracts that contain many records for the same key value.  Duplicate key values can be greyed out leaving the first value in the list to stand out. 
Width Restricts a column to a specific width.  This is useful for improving readibility when long text fields are used. 
Format For numeric fields you can specify any numeric format understood by Excel.  For character fields you can specify alignment: L = Left, R=Right, C=Centered, W=Wrap Text.
Default For reports, this would be better labeled as “Excel Formula”.  Use this to embed calculated columns into your result. 
Input Not used for reports.  For update spreadsheets this identifies the conditions under which this field may be changed: A=Adding records, C=Changing existing records or adding records, P=By program control only (users are locked from making any changes)
Required Not used for reports.  Identifies fields that cannot be left blank.  A common synonym is “mandatory.” 
V.Type (Validation Type) Not used for reports.  Identifies rules to apply to insure values entered are valid.  
V.Tbl (Validation Table) Not used for reports.  Identifies an Excel or database table to use to validate values. 
Upd. Func. (Update Function) Not used for reports.  Identifies an SQL function to use when updating values in tables
Notes Use this the same way you would use program comments. 

I put this table in a worksheet called “Tables”.   This table is a powerful RAD (Rapid Application Development) type tool.  Once I get the basics of the report up, I can then bring in my customer who will almost always want to change the output based on either something they didn’t communicate, or based on a realization they had when they saw the results of what they asked for.  I no longer dread those moments.  I actually look forward to them.  Because while we’re talking, I see the excitement on their faces as within seconds their desires are realized – simply by adding or adjusting a row in this table.  I can easily add columns to their results by adding rows to this table – without changing my code.  I can change the initial sort to anything they want – without changing my code.   I can change column headings, add formats, and even add calculated columns – all without changing any of my code – and all within a matter of seconds.  My customers are thrilled.

In the next post we will start adding code to take advantage of this table in order to generate much of our SQL Select statement for us.


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: