Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Four Stages of an Application

Every application has four distinct stages, regardless of the type of add-in used to implement it. These stages are development/maintenance, startup, runtime and shutdown. This section briefly discusses all four stages and identifies 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 high-level 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 are 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 code to help build and maintain the application you are writing. There are two major categories of code that helps to build code:

Code templates
These can be as simple as basic subroutine and function frameworks manually copied from a stored module, or as complex as third-party code-generation tools. The Excel Visual Basic Editor (VBE) provides a very 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 application, 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 during the startup process.

Check the environment
Check any environmental conditions that must be satisfied in order for your application to run. This might include verifying that the appropriate version 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 gracefully 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 application modifies the user's Excel environment, it must save the original settings so they can be restored prior to exiting. This topic is covered extensively in Chapter 6 Dictator Applications.

Build any dynamic user interface elements
These include application-specific command bars, Excel Application-level settings, workbook templates and so forth.

Register any user-defined functions
If your add-in contains user-defined functions (UDFs) 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 settings made will depend on the type of add-in and the conditions discovered at startup. For example, if there were an application workbook open that belonged to your application when the add-in was opened, you would enable all of your application's menu bars 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 is performing the operations 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 application 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 Using Class Modules to Create Objects.

Handle runtime errors
Although we would like our applications to run flawlessly all the time, every application eventually encounters a runtime error. These errors cannot be allowed to stop your application dead in its tracks. Instead, they must be handled gracefully and in such a way that the user has some idea of what went wrong. Error handling is covered extensively in Chapter 12 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 standard modules in the referenced add-in directly by name. Without references, you can accomplish the same thing by using the Application.Run function.

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

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.

Unregister any user-defined functions
If your add-in registered any UDFs with the Excel Function Wizard on startup, it should unregister these functions on shutdown.

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 forth).

Restore the original environment
If your application made any persistent changes to the Excel environment, it must save the original 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 Dictator Applications.