Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA®

Practical Example

Features

The add-in for our PETRAS timesheet application will perform the following operations:

Open and initialize the application

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

Open and initialize the time-entry 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

Enable the user to close the PETRAS application

Add a custom property that will allow the consolidation application to locate all instances of our time-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 to running the add-in.

Open and Initialize the Application

The first operation the add-in performs when it is opened is to initialize the application and then open and initialize the user interface workbook. This is accomplished by 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 Error GoTo 0
' Initialize global variables.
InitGlobals
' Make sure we can locate our time entry workbook before
' we do anything else.
If Len(Dir$(gsAppDir & gsFILE_TIME_ENTRY)) > 0 Then
Application.ScreenUpdating = False
Application.StatusBar = gsSTATUS_LOADING_APP
' Build the command bars.
BuildCommandBars
' Determine if the time entry workbook is already
' open. If not, open it. If so, activate it.
On Error Resume Next
Set wkbBook = Application.Workbooks(gsFILE_TIME_ENTRY)
On Error GoTo 0
If wkbBook Is Nothing Then
Set wkbBook = Application.Workbooks.Open( _
gsAppDir & gsFILE_TIME_ENTRY)
Else
wkbBook.Activate
End If
' Make the worksheet settings for the time entry
' workbook
MakeWorksheetSettings wkbBook
' Reset critical application properties.
ResetAppProperties
Else
MsgBox gsERR_FILE_NOT_FOUND, vbCritical, gsAPP_NAME
ShutdownApplication
End If
End 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.Chapter 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 5-4. Each of these features is discussed in the sections that follow.

Figure 5-4. The PETRAS Application Toolbar

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 this procedure.

Listing 5-7. The MakeWorksheetSettings Procedure

Public Sub MakeWorksheetSettings(ByRef wkbBook As Workbook)
Dim rngCell As Range
Dim rngSettingList As Range
Dim rngHideCols As Range
Dim sTabName As String
Dim vSetting As Variant
Dim wksSheet 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 automatically by the settings code if it
' needs to be protected and/or hidden.
wksSheet.Unprotect
wksSheet.Visible = xlSheetVisible
' Hide any non-standard columns that need hiding.
Set rngHideCols = Nothing
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 If
For Each rngCell In rngSettingList
' Determine if the current worksheet requires the
' current setting.
vSetting = Empty
On Error Resume Next
If rngCell.Value = "setScrollArea" Then
' The scroll area setting must be treated
' differently because it's a range object.
Set vSetting = Application.Evaluate( _
"'" & wksSheet.Name & "'!" & rngCell.Value)
Else
vSetting = Application.Evaluate( _
"'" & wksSheet.Name & "'!" & rngCell.Value)
End If
On Error GoTo 0
If Not IsEmpty(vSetting) Then
If rngCell.Value = "setProgRows" Then
If vSetting > 0 Then
wksSheet.Range("A1").Resize(vSetting) _
.EntireRow.Hidden = True
End If
ElseIf 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
ElseIf rngCell.Value = "setEnableSelect" Then
wksSheet.EnableSelection = vSetting
ElseIf rngCell.Value = "setRowColHeaders" Then
wksSheet.Activate
Application.ActiveWindow _
.DisplayHeadings = vSetting
ElseIf rngCell.Value = "setVisible" Then
wksSheet.Visible = vSetting
ElseIf rngCell.Value = "setProtect" Then
If vSetting Then
wksSheet.Protect , True, True, True
End 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 procedure loops through all the worksheets in the specified workbook and applies all the settings that we have defined for each worksheet. We have designed this procedure to accept a reference to a specific workbook object as an argument rather than having it assuming it needs to operate on the user interface workbook because this design will allow us to generalize the application to handle multiple user interface workbooks if we need to at some later time. The settings table on which this procedure is based can be seen on the wksUISettings worksheet of the PetrasAddin.xla workbook.

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()
Application.StatusBar = False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.EnableCancelKey = xlInterrupt
Application.Cursor = xlDefault
End Sub

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 a Copy of the Time-Entry Workbook to a Predefined Consolidation 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

Public Sub PostTimeEntriesToNetwork()
Dim sSheetTab As String
Dim sWeekEndDate As String
Dim sEmployee As String
Dim sSaveName As String
Dim sSavePath As String
Dim wksSheet As Worksheet
Dim wkbBook As Workbook
Dim vFullName As Variant
' Don't do anything unless our time entry workbook is active
' wkbBook will return a reference to it if it is.
If bIsTimeEntryBookActive(wkbBook) Then
' Make sure the TimeEntry worksheet does not have any
' data entry errors.
sSheetTab = sSheetTabName(wkbBook, gsSHEET_TIME_ENTRY)
Set wksSheet = wkbBook.Worksheets(sSheetTab)
If wksSheet.Range(gsRNG_HAS_ERRORS).Value Then
MsgBox gsERR_DATA_ENTRY, vbCritical, gsAPP_NAME
Exit Sub
End If
' Create a unique name for the time entry workbook.
sWeekEndDate = Format$( _
wksSheet.Range(gsRNG_WEEK_END_DATE).Value, _
"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
' entry workbook to that location. If not, prompt the
' user to identify a consolidation location, save that
' location to the registry and save the time entry
' workbook to that location.
sSavePath = GetSetting(gsREG_APP, gsREG_SECTION, _
gsREG_KEY, ")
If Len(sSavePath) = 0 Then
' No path was stored in the registry. Prompt the
' user for one.
vFullName = Application.GetOpenFilename( _
Title:=gsCAPTION_SELECT_FOLDER)
If vFullName <> False Then
' NOTE: The InStrRev function was not available
' in Excel 97.
sSavePath = Left$(vFullName, _
InStrRev(vFullName, "\"))
SaveSetting gsREG_APP, gsREG_SECTION, _
gsREG_KEY, sSavePath
Else
' The user cancelled the dialog.
MsgBox gsMSG_POST_FAIL, vbCritical, gsAPP_NAME
Exit Sub
End If
End If
wkbBook.SaveCopyAs sSavePath & sSaveName
MsgBox gsMSG_POST_SUCCESS, vbInformation, gsAPP_NAME
Else
MsgBox gsMSG_BOOK_NOT_ACTIVE, vbExclamation, gsAPP_NAME
End 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 function returns an object reference to the time-entry workbook via its ByRef Workbook argument if the time-entry workbook is active. If the time-entry workbook is not active, we display an error message to the user 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 More Data-Entry Rows to the Time-Entry Worksheet

In the version of the time-entry workbook demonstrated in Chapter 4 Worksheet Design, 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. Listing 5-10 shows the procedure that implements this feature.

Listing 5-10. The AddMoreRows Procedure

Public Sub AddMoreRows()
Const lOFFSET_COLS As Long = 5
Const lINPUT_COLS As Long = 6
Dim rngInsert As Range
Dim wkbBook As Workbook
Dim wksSheet As Worksheet
' Don't do anything unless our time entry workbook is active
If bIsTimeEntryBookActive(wkbBook) Then
' Get a reference to the TimeEntry worksheet and the
' insert row range on it. All new rows will be inserted
' above this range.
Set wksSheet = wkbBook.Worksheets(sSheetTabName( _
wkbBook, gsSHEET_TIME_ENTRY))
Set rngInsert = wksSheet.Range(gsRNG_INSERT_ROW)
' Add a new row to the time entry table.
wksSheet.Unprotect
rngInsert.EntireRow.Insert
rngInsert.Offset(-2, 0).EntireRow.Copy _
Destination:=rngInsert.Offset(-1, 0)
rngInsert.Offset(-1, lOFFSET_COLS) _
.Resize(1, lINPUT_COLS).ClearContents
wksSheet.Protect , True, True, True
Else
MsgBox gsMSG_BOOK_NOT_ACTIVE, vbExclamation, gsAPP_NAME
End If
End Sub

In the AddMoreRows procedure, we use the same method to determine whether a time-entry workbook is active as we used in the PostTimeEntriesToNetwork procedure. After we've determined we have a valid workbook active, inserting a new row is 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.

Allow the User to Close the 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 Listing 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

Public Sub ShutdownApplication()
' Blow past any errors on application shutdown.
On Error Resume Next
' This flag prevents this procedure from being called a
' second time by Auto_Close if it has already been called
' by the ExitApplication procedure.
gbShutdownInProgress = True
' Delete command bar.
Application.CommandBars(gsBAR_TOOLBAR).Delete
' Close the time entry workbook, allowing the 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
Else
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 statement. Chapter 12 VBA Error Handling covers this statement in detail.

The first thing the ShutdownApplication procedure does is set a global flag variable that will prevent it from being called twice if the user initiated shutdown by clicking the Exit PETRAS toolbar button. The process of closing the add-in workbook will cause the Auto_Close procedure to fire. The Auto_Close procedure also calls ShutdownApplication, but it checks the value of the gbShutdownInProgress variable first and simply 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 workbooks distinction is an important one because many users have a hidden Personal.xls workbook or other utility workbook always open. We want to ignore these hidden workbooks when trying to determine whether we should close Excel or leave Excel open on exit. Listing 5-13 shows the procedure that counts the number of visible workbooks.

Listing 5-13. The lCountVisibleWorkbooks Procedure

Public Function lCountVisibleWorkbooks() As Long
Dim lCount As Long
Dim wkbBook As Workbook
For Each wkbBook In Application.Workbooks
If wkbBook.Windows(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 document property, activate the PetrasTemplate.xls workbook and choose File > Properties 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:

MEntryPoints This module contains the procedures called from the toolbar buttons of our toolbar. These procedures are entry points in the sense that they are the only way for the user to execute code in the application. The ClearDataEntryAreas 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 are all located in this module.

MGlobals This module contains the definitions of all public constants and variables used by our application as well as an InitGlobals procedure used to ensure our global variables are always properly initialized.

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

MStandardCode This module contains standard code library procedures that are reused without modification in many different projects. The ResetAppProperties procedure shown in Listing 5-8 is one example.

MSystemCode 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 Option Private Module directive. The WriteSettings procedure shown in Listing 5-4 is located in this module.