Check Entry – Initializing Globals

You are here (Click to enlarge)

As I have written about this method for creating updates with Excel, I have confessed that I really don’t think some of my choices were the best way.  These questionable choices came about as a result of plowing new ground and attempts to improve speed.  I haven’t changed them solely because I tend to not fix what’s working.  That doesn’t mean you need to stick with what I’ve done.  You have the code.  You have the skills.  Make changes as you see fit.  One of these questionable tactics is today’s topic, initializing globals. 

Originally I wrote totally self contained routines that would pluck what they needed from the Field Definitions file or properties of the Datarange.  This meant these routines had to calculate values already calculated by parent routines or repeatedly as the routine was called within a loop.  To speed execution, I decided to ‘pre-calculate’ these values and make them available to any routine that needed them.  Globals values (variables that can be accessed by any routine in any module) were the first thing that came to mind and they did indeed dramatically improve execution.  So why are they questionable?

As mentioned, the global’s advantage is it can be accessed by any routine in any module.  The global’s disadvantage is it can be changed by any routine in any module.  That opens the door for some unrelated routine to inadvertently, and negatively impact other routines; unexpected things can happen; debugging can be a nightmare. 

Similar to globals are class properties.  Like forms, individual worksheets can have custom properties.  They are setup the same way as forms.  I’ve tried this approach.  My implementation of properties had key values placed in seperate properties which meant my code changes were spread over a significant area of code (I like concentrating where changes are made in a tight section of code).  Another implementation of properties that I tried involved something similar to the ‘Settings’ routine, where I passed a value to the property that indicated which value I wanted returned.  This limited my changes to one place (a good thing) but added speed sapping processing.

Another alternative to the global is to pass all values as parameters down the stack of function calls.  This can result in huge parameter lists that contain some values not related to the routine accepting them.  The keyword is, ‘can’.  I don’t know if that would be the case here because I haven’t explored it.  It may very well be managable.  In addition, passing everything as a parameter down the stack does not prevent parent routines from changing values inappropriately, but it does limit the damage to downstream processes, particularly if you pass parameters by value instead of by reference. 

So now you know the debate that went through my head as I coded this process.  Maybe you know why one method is best.  Maybe you know of an alternative I haven’t explored.  If you do, please share by posting a comment.  But until I figure out which method is best, I’ll stick with one that I know works, and works well.  Below is the code that initializes project wide globals, and worksheet scoped globals.

Worksheet_Activate handles globals limited in scope to the worksheet.  It MUST be placed in the worksheet code area.

Initialize_Globals handles project wide globals.  It CANNOT be placed in the worksheet code area.  It MUST be placed in a module.  I place it in modTableLoad.

Public Sub Worksheet_Activate()

    Dim lRow As Long

    Dim s As String

   ‘Do just once to initialize these globals (for speed)

    If lColTbl = 0 Or lColACD = 0 Then

        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

Function Initialize_Globals(sFieldDefinitions As String) As Boolean

‘   Description:Initialize all global fields

‘   Parameters: sFieldDefinitions Name of range holding any field definitions

‘   Example:    bResult = Initialize_Globals(“Fields_Data”)

‘     Date   Init Modification

‘   01/01/01 CWH  Initial Programming

    Initialize_Globals = Failure        ‘Assume the Worst

    On Error GoTo ErrHandler            ‘

    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

ErrHandler:

    If Err.Number <> 0 Then MsgBox _

        “Initialize_Globals – Error#” & Err.Number & vbCrLf & Err.Description, _

            vbCritical, “Error”, Err.HelpFile, Err.HelpContext

    On Error GoTo 0

End Function