Beyond Excel: VBA and Database Manipulation

May 31 2010   4:04PM GMT

Check Entry – Initialize Globals

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

You are here (Click to enlarge)

You are here (Click to enlarge)

As mentioned in the last post, I have chosen to use globals (variables that can be used by any routine in the entire spreadsheet/project) to keep the routines fast by eliminating repeated lookups for the same information.  An alternative approach would be to pass lengthy parameter lists.  You may want to do that in your code.  I keep wrestling with that one and am tempted to rewrite the routines without globals.  But that is for another time.

“WorkSheet_Activate” assigns values to globals specificly for updating a speadsheet to a database.  “Initialize_Globals” ” assigns values to globals that can be used for simply loading a database or updating it.

It’s all pretty straight forward.  So without futher pontification – here is the code:

These following declarations should go at the top of modTableLoadmodTableLoad contains those routines and data items required by spreadsheets that load data for databases.  This includes database reporting/analytics spreadsheets and database update spreadsheets.

'Relative Column Positions for Detail Field Definitions
Global lColTbl As Long         'Table
Global lColAls As Long         'Alias
Global lColFld As Long         'Field
Global lColKey As Long         'Key
Global lColHdg As Long         'Heading
Global lColSOrd As Long        'Sort Order (1st, 2nd, 3rd, ...)
Global lColSSeq As Long        'Sort Sequence (Asc, Dsc)
Global lColFrz As Long         'Freeze Field
Global lColSQLF As Long        'SQL Function
Global lColHid As Long         'Hide
Global lColWid As Long         'Width
Global lColFmt As Long         'Format
Global lColXLF As Long         'XL Function
Global lColInp As Long         'Input
Global lColReq As Long         'Required Flag
Global lColVTyp As Long        'Validation Type
Global lColVTbl As Long        'Validation Table
Global lColUpd As Long         'SQL Update Statement Function
Global lColNote As Long        'Notes/Error Messages


The following routine is a handler for the activate event in the worksheet that displays records from and permits entries to the database.  It MUST go in the worksheet (it cannot be in a module). 

Public Sub Worksheet_Activate()
    Dim lRow As Long
    Dim s As String
    If lColTbl = 0 Or lColACD = 0 Then 'Do once to init globals (for speed)
        Initialize_Globals sFields
        If sFields > "" Then
            With Worksheets("Tables").Range(sFields)
                For lRow = 2 To .Rows.Count
                    If .Cells(lRow, lColKey) <> "" And lCol1stKey = 0 Then _
                        lCol1stKey = lRow - 1
                    If .Cells(lRow, lColReq) <> "" And lCol1stReq = 0 Then _
                        lCol1stReq = lRow - 1
                    If .Cells(lRow, lColHdg) = "ACD" Then _
                        lColACD = lRow - 1
                    If .Cells(lRow, lColHdg) = "ERRORS" Then _
                        lColERRORS = lRow - 1
                Next lRow
            End With
        End If
    End If
End Sub

The following routine should go in modTableLoad

Function Initialize_Globals(sFieldDefinitions As String) As Boolean
'   Description:Initialize all global fields
'   Parameters: sFieldDefinitions	range name holding field definitions
'   Example:    bResult = Initialize_Globals("Fields")
'     Date   Init Modification
'   01/01/01 CWH  Initial Programming
    On Error GoTo ErrHandler            '
    Initialize_Globals = Failure        'Assume the Worst

    lColTbl = FieldColumn("Table", sFieldDefinitions)
    lColAls = FieldColumn("Alias", sFieldDefinitions)
    lColFld = FieldColumn("Field", sFieldDefinitions)
    lColKey = FieldColumn("Key", sFieldDefinitions)
    lColHdg = FieldColumn("Heading", sFieldDefinitions)
    lColSOrd = FieldColumn("S.Ord", sFieldDefinitions)
    lColSSeq = FieldColumn("S.Seq", sFieldDefinitions)
    lColFrz = FieldColumn("Freeze", sFieldDefinitions)
    lColSQLF = FieldColumn("SQL Func.", sFieldDefinitions)
    lColHid = FieldColumn("Hide", sFieldDefinitions)
    lColWid = FieldColumn("Width", sFieldDefinitions)
    lColFmt = FieldColumn("Format", sFieldDefinitions)
    lColXLF = FieldColumn("XL Func.", sFieldDefinitions)
    lColInp = FieldColumn("Input", sFieldDefinitions)
    lColReq = FieldColumn("Required", sFieldDefinitions)
    lColVTyp = FieldColumn("V.Type", sFieldDefinitions)
    lColVTbl = FieldColumn("V.Tbl", sFieldDefinitions)
    lColUpd = FieldColumn("Upd.Func.", sFieldDefinitions)
    lColNote = FieldColumn("Notes", sFieldDefinitions)
    Initialize_Globals = Success        'Successful finish
    If Err.Number <> 0 Then MsgBox _
        "Initialize_Globals - Error#" & Err.Number & vbCrLf & _
            Err.Description, vbCritical, "Error", Err.HelpFile, _
    On Error GoTo 0
End Function

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

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:

Share this item with your network: