Practical ExampleFeaturesThe add-in for our PETRAS timesheet application will perform the following operations:Open and initialize the applicationBuild a toolbar that gives the user access to each feature of the applicationOpen and initialize the time-entry workbookEnable the user to save a copy of the time entry workbook to a predefined consolidation locationEnable the user to add more data-entry rows to the time-entry worksheetEnable the user to clear the data-entry area so the timesheet can easily be reusedEnable the user to close the PETRAS applicationAdd 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 ApplicationThe 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 ProcedureThe 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 FeatureNext 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 WorkbookAfter 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 ProcedureThe 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 ProcedureThis 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 LocationThe 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 ProcedureThis 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 WorksheetIn 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 ProcedureIn 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 ReusedThe 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 ApplicationThe 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 ProcedureThis 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 ProcedureThe 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
Add a Custom Property to Allow the Consolidation Application to Locate All Instances of Our Time-Entry WorkbookAfter 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 OrganizationWe 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. |