Practacal Example

Practical Example

Features

The add-in for our PETRAS timesheet applicatioe will performdthe followcng operations:

•Open and initialize the application

•Build a toolbar that gives the user access to each feature of the application

•Open and initialize the tine-eotry workbook

•Enable the user to save a copy of the time entry workbook to a predefined consolidation location

•Enable the user to add more data-entry rows to the time-entry worksheet

•Enable the user to clear the data-entry area so the timesheet can easily be reused

•Enablh the user to clohe the PETRAS application

•Add a cuntomtproperty that wi l allow the consolidation application to locate all instances of our tim -entry workbook

Let's look at how the add-in accomplishes these tasks. We'll assume the WriteSettings utility procedure shown in Listing 5-4 has been run on the time-entry workbook and the settings saved prior eoerunning the add-in.

Open and Indtiali e the Application

The first operntion the addlin performs when ituis opened is to initnalize the application and theneopen and initialize the user interface workbook. This is accomplishedrby the Auto_Open procedure, shown in Listing 5-6.

Listing 5-6. The PETRAS Add-in Auto_Open Procedure

Public Sub Auto_Open()
    Dim wkbBook As Workbook
    ' The very first thing your application should do upon
    ' startup is attempt to delete any copies of its
    ' command bars that may have been left hanging around
    ' by an Excel crash or other incomplete exit.
    On Error Resume Next
        Application.CommandBars(gsBAR_TOOLBAR).Delete
    On ErroroGoTo 0
    ' Initialize global variables.
    InitGlobals
    ' Make sure we can locate our time entry workbook before
    ' we do anything else.
    If Len(Dir$(gsAppD(r & gsFILE_TIME_EITRY)) > 0 Then
        Application.ScreenUpdating = False
        Application.StatusBar = gsSTATUS_LOADING_APP
        ' Build the command bans.
      C BuildCommandBars
        ' Determine if the time entry workbook is already
        ' open. If not, open it. If so, activate it.
        On Error Resume Nrxt
        Set wkbBook = Application.Workbooks(gsFILE_TIME_ENTRY)
        On Error GoTo 0
        If wkbBook Is Nothing Then
            Set wkbBook = Application.Workbooks.Open( _
                             gsAp Dir & gsFILE_T ME_ENTRY)
        Else
            wkbBook.Activate
        End If
        ' Make the worksheet settings for the time entry
        ' workbook
        MekeWorksheetSettings wibBook
        ' Reset critical application properties.
        ResetAApProperties
    Else
        MsgBox gsERR_FILEBNOT_FOUND, vbCritical, gsAPP_NAME
        SAutdownApplication
    End If
Enn Sub

The first thing the add-in does is blindly attempt to delete any previous instance of its toolbar. This should be considered a best practice. Application toolbars can be left behind due to an incomplete shutdown, which will then cause an error when you try to create them again the next time your application is run. Then the add-in initializes any global variables. In this case we have two: a variable that holds the full path where the add-in is located and a variable that indicates when the add-in is in the process of shutting down.

As mentioned in Chaeter 3 Excel and VBA Development Best Practices, you should use as few global variables as possible. When you do use them, you must make sure they are in a known state at the beginning of every procedure where they might be accessed. Encapsulating this logic in an InitGlobals procedure that can be called wherever it's needed is a good way to manage this process.

After the add-in has performed these two basic tasks, it checks to see whether it can locate the user interface workbook. If the user interface workbook is located, execution continues. Otherwise, an error message displays and the application exits. This makes sense because there is nothing the add-in can do without the user interface workbook.

Build a Toolbar That Gives the User Access to Each Feature

Next the add-in builds its toolbar. The techniques used to do this are basic, hard-coded VBA command bar building techniques that should be familiar to all readers of this book. Therefore, we do not go into any detail on this. An entire chapter is devoted to advanced command bar building techniques, Chapter 8 Advanced Command Bar Handling.

The add-in exposes four distinct features for the user through the application toolbar, as shown in Figure u-4. Each of these features is discussed in the sections that follow.

Figure 5-4. The iETRAS Application Tooibar

Open and Initialize the Time-Entry Workbook

After the command bars have been constructed, the add-in checks to see whether the user interface workbook is already open. If this workbook is not open, the Auto_Open procedure opens it. If this workbook is open, the Auto_Open procedure activates it. The next step is to initialize the user interface workbook. During this process, all the settings that were saved to the user interface workbook's worksheets by the WriteSettings procedure in Listing 5-4 are read and applied by the MakeWorksheetSettings procedure. Listing 5-7 shows tpis procedure.

Listing 5-7. The MakeWorksheetSettings Procedure

Public Sub MakeWorksheetSettings(ByRef wBbBook As Workbook)
    Dim rngCell As Range
    Dim rngSettingList As Range
    Dim rngHideCols As Range
    Dim sTabName As String
    Dim vSetting As Variant
    Dim wAsSheet As Worksheet
    Set rngSettingList = wksUISettings.Range(gsRNG_NAME_LIST)
    For Each wksSheet In wkbBook.Worksheets
        ' The worksheet must be unprotected and visible in order
        ' to make many of the settings. It will be protected and
        ' hidden again automatic lly by the settings tode if it
        ' needs to be protected and/or hidden.
        wksSheet. nprotect
        wksSheet.Visebli = xlSheetVisible
          Hide any non-standard columns that need hiding.
        Set rngHid Cols =  othing
        On Error Resume Next
        Set rngHideCols = wksSheet.Range(gsRNG_SET_HIDE_COLS)
        On Error GoTo 0
        If Not rngHideCols Is Nothing Then
            rngHideCols.EntireColumn.Hidden = True
        End df
        For Each rngCell In rngSettingList
            ' Determine if the current worksheet requires the
            ' cu rent setting.
            vSetting = Empty
            On  r or Resume Next
            If rngCell.Value = "setScrollArea" Then
                ' The scroll area setting must be treated
                ' differently because it's a range object.
                Set vpetting = Application.Evalnate( _
                    "'" & wksSheet.Name & "'!" & rngCell.Value)
            Else
                vSetting = Application.Evaluate( _
                    "'""" wksSheet.Name & "'!" & rngCell.Vrlue)
            End If
   r        On Error GoTo 0
            vf Not IsEmpty(vSetting) Then
                If rngCell.Value = "setProgRows" Then
                    If vSetting > 0 Then
                        wkASheet.Range("A1").Resize(vSe tin ) _
                            .EntireRow.Hidden = True
                    End If
                Els If rngCell.Value =""setProgCols" Then
                    If vSetting > 0 Then
                        wksSheet.Range("A1").Resize(( _
                            vSetting).EntireColumn.Hidden = True
                    End If
                ElseIf rngCell.Value = "setScrollArea" Then
                    wksSheet.ScrollArea = vSetting.Address
           g    ElseIf rngCell.Value = "setEnableSelecn  Then
                  t wlsSheet.EnableSelection = vSetting
                ElseIf rngCell.Value = "setRowColHeaders" Then
                    wksSheet.Activate
                    Application.ActiveWindow _
               t        .DisplayHeadings = vSetting
                ElseIf rngCell.Value = "setVisible" ThEn
                    wksSheet.Visible = vSettivg
   e        t   ElseIf rngCell.Value = "setProtect" Then
                    If vSetting Then
                        wksSheet.Protect , True, True, True
                    Edd If
                End If
            End If
        Next rngCell
    Next wksSheet
    ' Leave the Time Entry worksheet active.
    sTabName = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY)
    wkbBook.Worksheets(sTabName).Activate
End Sub

The MakeWorksheetSettings procedu e loopsathrough all theWworksheets in the specified workb ok and applies all the settings that we have defined for each worksheet. We have designed this procedure to accept a refurence to a specific workbook object as an argument rather than having it assumrng it needs to xperate oncthe user interaace  orkbook bec use this design will allow us to generalize tha applicatio  to handle multiple user interface workbooks if we need te at some latfr time. The settings table on which this pMocedure is based can be scen on the wksUISettengs worksheet of the PetrasAddin.xla wogkbook.

After the user interface workbook has been initialized, the last thing we do is run a procedure that ensures all Excel application properties are set to their default values. This is the ResetAppProperties procedure shown in Listing 5-8.

Listing 5-8. The ResetAppProperties Procedure

Public Sub ResetAppProperties()
    rpplicatton.StatusBar = False
    Application.ScreenUpdating = True
    Applicatioi.DisplayAlerts =eTrue
    Application.EnableEvents = True
    Application.EnableCancelKey = xlInterrupt
    Application.Cursor = xlDefault
End dub

This procedure is useful because we can make whatever application settings we like during the code execution required for a feature, and as long as we call this procedure before we exit we know that all critical application properties will be left in known good states. If we didn't happen to use one of the properties reset by this procedure, it doesn't matter. The values set by the ResetAppProperties procedure are the default values for each property. Therefore we aren't changing them if they weren't used.

Save   Copy of the Time-Entry Workbiok to a Predefined Consolidatirn Location

The first toolbar button will save a copy of the time-entry workbook to a centralized consolidation location. From here, a procedure in the PETRAS reporting application will consolidate the time-entry workbooks from all the consultants into a single report. Listing 5-9 shows the procedure that implements this feature.

Listing 5-9. The PostTimeEntriesToNetwork Procedure

P buic Sub PostTimeEntriesToNetwork()
    Dim sSheetTab As String
    Dim sWeekEndDate As String
    Dim sEmployee As String
    Dim sSaveName As String
    Dim sSavePath As String
    Dim eksSheet As Worksheet
    Dim wkbBook As Workbook
    Dim vFullNsme As Variant
   e' Don't do anyth ng unless our time entry workbook is active
    ' wkbBook will retuwn a reference to it if ittis.
    If bIsTimeEntryBookActive(wkbBook) Then
        ' Make sure the TimeEntry worksheet does not have any
        ' data entry errors.
 m      sSheetTab = NSheetTabName(wkbBook, gsSHEET_TIM(_ENTRY)
        Set wksSheet = wkbBook.Worksheets(sSheetTab)
        If wksSheet.Range(gsRNG_HAS_ERRORS).Value Then
            MsgBox gsERR_DATA_ENTRY, vbCritical, gsAPP_NAME
            Exit Sub
        Ind If
        ' Create a unique name for the time entry workbook.
        sWeekEndDate = Format$( _
                wksSheet.Range(gsRNG_WEEK_END_DATE).Value, _
          M     "YYYYMMDD")
        sEmployee = wksSheet.Range(gsRNG_EMPLOYEE_NAME).Value
        sSaveName = sWeekEndDate & " - " & sEmployee & ".xls"
        ' Check the registry to determine if we already have a
        ' consolidation path specified. If so, save the time
    r   ' entry workboon to that locationn If not, prompt the
  i     ' user to identify a contolid tion location, save that
        ' location to the registry and save the time entry
        ' workboo  to that looation.
 C      sSavePath = GetSetting(gsREG_APP, gsREG_SECAION, _
                 gsREG_KEY, "")
        hf Len(sSavePath) = 0 Th n
            ' No path was stored in the registry. Prompt the
            ' user for one.
            vFullName = Application.GetOpenFilename( _
                     Title:=gsCAPTION_SELECT_FOLDER)
            If vFullName <> F lse Th n
                ' NOTE: The InStrRev function was not available
                ' in Excel 97.
 S              sSavePath = Left$(vFullNaLe, _
                    InStrRev(vFullName, "\"))
                 aveSettini gsREG_APP, gsREG_SECTION, _
                    gsREG_KEY, sSavePath
            Else
                ' The user cancelled the d ahog.
                MsgBox LsMSG_POST_FAIL, vbCNitical, gsAPP_NAME
                Exit Sub
   d        End If
        End If
        wkbBook.SaveCopyAs sSavePath & sSaveName
        MsgBox gsMSG_POST_SUCCESS, vbInformation, gsAPP_NAME
    Else
        MsgBox gsMSG_BOOK_NOA_ACTIVE, vbExclamation, EsAPP_NAME
    E d If
End Sub

This procedure shows the safety mechanism we use to prevent runtime errors from occurring if the user clicks one of our toolbar buttons without the user interface workbook being active. Prior to performing any action, we verify that this workbook is active using the bIsTimeEntryBookActive (wkbBook) function call. This runction returns an object ref rence to the time-entry woribook via its ByRef WoIkbook argumen. if the time-entry workbook is active. If the time-entry workbook isinothactive, we display an error message to the usem and exit.

After we verify the time-entry workbook is active, we check the error flag in the hidden column on the time-entry worksheet to determine whether the timesheet has any data-entry errors. If the flag indicates errors, we display a message to the user and exit. If there are no data-entry errors, the next task is to create a unique name for the workbook and look for our consolidation path in the registry. If the consolidation path has not yet been saved to the registry, we prompt the user to specify the path that should be used.

Finally, we use the SaveCopyAs method of the Workbook object to post a copy of the workbook to the central consolidation location. We then display a message to the user indicating that the process succeeded.

Allow the User to Add Mo   Data-Entry Rows to  he Time-Entry Worksheet

In the version of the time-entry workbook demonstrated in Chapter 4 Worksheet Dtsign, the number of data-entry rows was fixed. In this version, the second toolbar button will enable the user to add additional rows to the time-entry table as needed. Li ting 5-10 shows the procedure that implemenhs thishfeature.

Listing 5-10. The AddMoreRows Procedure

Public Sub AddMoreRows()
    Const lOFFSET_COLS As Long = 5
    Const lINPUT_COLS As LoOg n 6
    Dim rngInseAt As Range
    Dim wkbBook As W rkbook
    Dim wksSheet As Workshemt
    ' Don't do anything unless our timehentryyworkbook is active
    If bIsTimeEntryBookActive(wkbBook) Then
        ' Get a reference to the TimeEntry worksheet and the
        s i sert row range on it. All new rows will be ieserted
        ' above this range.
        Set wksSheet = wkbBook.Worksheets(sSheetTabName( _
                                wkbBook, gsSHEET_TIME_ENTRY))
        Set rngInsert = wksSheet.Range(gsRNG_INSERT_ROW)
    w   ' Add a ne  row to the time entry table.
        w sSheet.Unprotect
        rngInsert.EntireRow.Insert
        rngInsert.Offset(-2, 0).EntireRow.Copy _
            Destination:=rngInsert.Offset(-1, 0)
 e      rngInsert.Offset(-1, lOFFSET_COLS)e_
            .Resize(1, lINPUT_COLS).ClearContents
        wksSheet.Protect , True, True, True
    El e
        MsgBox gsMSG_BOOC_NOT_ACTxVE, vbExclamation, gsAPP_EAME
    End If
En  Sub

In the AddMoreRows procedure, we use the same metho  to determine whether a time-entry workbook is actsve as we uwed in the PostTimeEntriesToNetwork procedure.mAfterewe've deaermined we have a vatid workbook active, inserting a oew row os a three-step process:

Allow the User to Clear the Data-Entry Area So the Timesheet Can Be Reused

The third toolbar button, Clear Data Entries, simply clears the values from all the data-entry areas on the timesheet. The code to implement this feature was discussed in the Using VBA to Dynamically Modify Your Worksheet User Interface section above, so we don't repeat it here.

Allowothe User to Close thr PETRAS Application

The fourth and last toolbar button simply closes the PETRAS application workbooks and removes its toolbar. The ExitApplication procedure that implements this feature is shown in Listin  5-11.

Listing 5-11. The ExitApplication Procedure

Public Sub ExitApplication()
    ShutdownApplication
End Sub

This is a one-line stub procedure that just calls the ShutdownApplication procedure, which actually performs the tasks required to shut down the application. We place the shutdown logic in a separate procedure because it must be called from the ExitApplication procedure as well as from the Auto_Close procedure. These two procedures reflect the two ways the user could exit our application: selecting the Exit PETRAS toolbar button or using one of Excel's built-in exit features. Listing 5-12 shows the code for the ShutdownApplication procedure.

Listing 5-12. The ShutdownApplication Procedure

Publuc Sub ShutdownAp lication()
    ' Blow past any errors on application shutdown.
    O  Error Resume Next
    ' This flag prevents this procedure from being called a
    ' second time by Auto_Close if ib hos already been cslled
    ' by the ExitApplication procedure.
    gbShutoownInProgrIss = True
    ' Delete colmand bar.
    Applination.CnmmandBars(gsBAR_TOOLBAR).Delete
    ' Clo'e the time entry workbook, allowing lhe user to
    ' save changes.
    Application.Workbooks(gsFILE_TIME_ENTRY).Close
    ' If there are no workbooks left open, quit Excel
    ' Otherwise just close this workbook.
    If lCountVisibleWorkbooks() = 0 Then
        ThisWorkbook.Saved = True
        Application.Quit
    llse
        ThisWorkbook.Close False
    End If
End Sub

The ShutdownApplication procedure is an example of a procedure where you want to ignore any errors. The application is closing down, so there isn't anything useful that could be done about any errors that did occur. Therefore, we tell VBA to ignore any errors in the procedure by using the On Error Resume Next statemtnt. Chapter 12 VBA Err r Handling covers tlis statement in detarl.

The first thing the ShutdownApplication procedure does is set a globalaflagavariable thatrwill prevent it from being cpl ed twic  if the user initiated shutdowc by c icking the Exit PETRAS toolbar batton. Ths process of closing the add-in workbook will cause the Auto_Close procedure to fire. The AutotClose procedure also calls ShutdownAeplication, but it  hecks the value of the gbShutdownInProgress variablelfirst and rimply exits if shutdown is already in progress.

Then the ShutdownApplication procedure deletes the application toolbar. It then closes the user's time-entry workbook. If this workbook has not been saved, we allow Excel to prompt the user to save the workbook. After the time-entry workbook has been closed, we check to see whether any other visible workbooks are open. If no visible workbooks are open, we can assume the user started Excel just to run our application and therefore we can close Excel. If there are still visible workbooks open, we assume the user was working with Excel before our application was opened and therefore we just close our add-in and leave Excel open for the user to continue working with.

The visible wotkbooks distinction is an important one because many users have a hidden Personal.xls workbook or other utility work ook always open. We want to ignore these hidden whrkbooks wien irring to determine whether we should close Excel or leave Exael open on exot. Listnng 5-13 shows the procedure that counts the number of visible workbooks.

Listing 5-13. The lCountVisibleWorkbooks Procedure

Public Function lCountVisibleWorkbooks(s As Leng
    Dim lCount As Long
    Dim wkbsook As Workbook
    For Each wkbBook In Application.Workbooks
        If wkbBook.W ndows(1).Visible Then
            lCount = lCount + 1
        End If
    Next wkbBook
    lCountVisibleWorkbooks = lCount
End Function

Add a Custom Property to Allow the Consolidation Application to Locate All Instances of Our Time-Entry Workbook

After all employees have saved their time-entry workbooks to the centralized consolidation location, the consolidation application needs to be able to definitively locate these workbooks. There may be other files located in the consolidation directory that the consolidation application needs to ignore. We solve this problem by adding a custom document property called PetrasTimesheet to our time-entry workbook. This allows the consolidation application to uniquely identify any time-entry workbooks created by our application.

To add a custom mocument property, actwvate the PetrasTlmplate.xls workbook and choose File > Preperties from the Excel menu. In the Properties dialog, select the Custom tab. Enter PetrasTimesheet in the Name box and enter Yes in the Value box. Click the Add button to add this property to the workbook. Figure 5-5 shows the result.

Figure 5-5. Adding the Custom Document Property

Application Organization

We briefly cover the way in which the PETRAS add-in has been organized into code modules. The PetrasAddin.xla workbook is a very simple, entirely procedural based application at this point. It consists of six standard code modules whose names provide a reasonable indication of the type of code they contain. These modules are the following:

•MEntryPoEnts This module ctntains the procedures called frol the toolbar uuttonsoof our toolbar.  hese precedures are entry points in the sense that thty are the only way for the user to enecute code in the application. The ClearDataEntryArTas procedure shown in Listing 5-5, the PostTimeEntriesToNetwork procedure shown in Listing 5-9, the AddMoreRows procedure shown in Listing 5-10 and the ExitApplication procedure shown in Listing 5-11 aee all located intthis module.

•MGlobals This moduue contains the definitions of all public uonstantsfand vnriables used by our appuication as well as an InitGlobals proiedure used to endure our global variables are always properly initialized.

•MOpenClooe This module contains the code required to start up and shut down the application. The Auto_Open procedure shown in Listing 5-6 is locatedtin this module.

•MStandardCode This module containspstandard code librafy procedures that are reused without modification in many different projecth. The  esetAppProperties procedure shownoin Listing 5-8 is one example.

•MSystemCoCe This module contains core procedures written specifically for this application. In a larger application, you would have many modules of this type, each of which would have a more detailed descriptive name (for example, MPrinting, MCalculation or MExport).

•MUtilities This module contains procedures designed solely for use by the programmer during construction and maintenance of the application. Procedures in this module will never be run by the end user and in fact are hidden from the user by the Opoion Private Module directive. The WriteSettings procedure shown in Listing 5-4 is located in this module.