Creating the Fields Table

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:

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.