Every Excel application passes through four distinct stages, regardless of the type of add-in used to implement it. These stages are development/maintenance, startup, runtime, and shutdown. In this section we briefly discuss all four stages and identify the activities and services that must be provided by the application during each stage. Some of the topics covered do not apply to all types of applications, but we cover them all to give you a complete overview of what an application must accomplish. We do not go into great detail on the topics covered in this section. Some will be obvious to readers of this book and others will be covered extensively either later in this chapter or in later chapters of the book.
Development/Maintenance
During this stage you are either writing the application’s code for the first time or updating existing code in the application. Either way, the purpose of this stage is to build or fix the application rather than to run it. You can make your life easier during this stage by using VBA to help automate the tasks required to build and maintain the application you are writing. There are two major categories of code designed to help build code:
■ Code templates—These can be as simple as basic subroutine and function frameworks manually copied from an ad hoc storage mod-ule, or as complex as third-party code generation tools. The Excel Visual Basic Editor (VBE) provides a rudimentary tool for creating template subroutines, functions, and property procedures through the Insert > Procedure menu.
■ Development utilities—You should strive to automate as many routine development processes as possible. Your application should contain a dedicated code module, or even a separate utility applica-tion, for VBA utilities that assist you in creating and maintaining the application. In the “A Table-Driven Approach to UI Worksheet Management” section later in the chapter, we demonstrate a utility for automatically managing the settings on your user interface worksheets.
Startup
When your application is starting up it must perform a number of tasks depending on what type of application it is and the conditions it finds dur-ing the startup process.
■ Check the environment—Check any conditions that must be sat-isfied for your application to run. This might include verifying that the appropriate versions of Windows and Excel are installed as well as verifying the existence of any additional programs and files your application depends on. If the startup check fails, you can exit grace-fully with a clear error message to the user rather than allowing your application to continue until it encounters a runtime error.
■ Save all settings that must be restored on exit—If your appli-cation modifies the user’s Excel environment, it must save the orig-inal settings so they can be restored prior to exiting. This topic is covered extensively in Chapter 6, “Dictator Applications.”
■ Build or open any dynamic user interface elements—These include application-specific command bars, Excel application set-tings, workbook templates, and so on.
■ Register any user-defined functions—If your add-in contains user-defined functions (UDFs) that you want to expose to the user, you need to add some basic information about them to the Excel Function Wizard. We cover this topic in the “Function Library Add-ins” section later in the chapter.
■ Set the initial user interface configuration—The specific set-tings made will depend on the type of add-in and the conditions dis-covered at startup. For example, if an application workbook was open that belonged to your application when the add-in was opened, you would enable your application’s menus and toolbars.
Otherwise you would probably disable most of them. This type of dynamic command bar modification is covered in the “Practical Example” section of Chapter 7, “Using Class Modules to Create Objects.”
Runtime
Runtime is the stage during which your application performs the opera-tions that constitute its primary purpose.
■ Handle requests from the user—These include calls generated by command bar controls, Forms controls on worksheets, ActiveX controls on UserForms and worksheets and any keyboard shortcuts your application has provided for the user.
■ Handle Excel application events—During runtime your applica-tion must also be prepared to respond to (and in some cases suppress) events generated by Excel itself. Excel application event handling is covered extensively in Chapter 7.
■ Handle runtime errors—Although we would like our applications to run flawlessly all the time, every application eventually encoun-ters a runtime error. These errors cannot be allowed to stop your application dead in its tracks. Rather they must be handled grace-fully and in such a way that the user has some idea of what went wrong. Error handling is covered extensively in Chapter 15, “VBA Error Handling.”
■ Call code located in other add-ins—If you have set a reference to another add-in using the Tools > References menu in the VBE during development you can call public procedures located in stan-dard modules in the referenced add-in directly by name. Without references you can accomplish the same thing by using the
Application.Runfunction.
■ Provide other services—Add-ins also provide other services at runtime, the most common being UDFs. We cover UDFs in detail in the “Function Library Add-ins” section later in the chapter.
5.FUNCTION, GENERAL, ANDAPPLICATION-SPECIFICADD-INS
Shutdown
The shutdown stage is when your application is exiting, either normally at the request of the user or abnormally as the result of an error condition.
Either way there are activities that must be performed at this stage.
■ Remove all application-specific user interface components—
This means removing all the application-specific items created during the startup phase (command bars, application-specific workbooks, and so on).
■ Unregister any user-defined functions—If your add-in regis-tered any UDFs with the Excel Function Wizard on startup then it should unregister these functions on shutdown.
■ Restore the original environment—If your application made any persistent changes to the Excel environment, it must save the origi-nal settings on startup and restore them on shutdown. This process is generically known as saving and restoring the user’s workspace.
This topic is covered extensively in Chapter 6.