Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

If you develop a nontrivial application in Excel, you might want to consider building in some sort of help for end users. Doing so makes the users feel more comfortable with the application and could eliminate many of those time-wasting phone calls from users with basic questions. Another advantage is that help is always available: That is, the instructions can't be misplaced or buried under a pile of books.

You can provide help for your Excel applications in a number of ways, ranging from simple to complex. The method that you choose depends on your application's scope and complexity and how much effort you're willing to put into this phase of development. Some applications might require only a brief set of instructions on how to start them. Others could benefit from a full-blown, searchable Help system. Most often, applications need something in between.

This chapter classifies user help into two categories:

• Unofficial Help system: This method of displaying help uses standard Excel components (such as a UserForm).

• Official Help system: This Help system uses a compiled CHM file produced by Microsoft's HTML Help Workshop.

Creating a compiled help file is not a trivial task, but it is worth the effort if your application is complex or if it will be used by a large number of people.

In the past, I've referred to Excel's onscreen assistance as online help . In fact, that's the common name for this type of assistance. But in recent years , the term online has come to refer to information available via the Internet. Some people were confused by the expression online help because the help information is actually stored on their local drives .

Therefore, I now use the expression Help system to refer to assistance provided by an application. But, beginning with Excel 2003, things have come full circle. The Help system for Excel 2003 and later is ( optionally ) truly online. You can view locally stored help information or (with an Internet connection) search for more up-to-date information at the Microsoft Web site.

Many of the examples in this chapter use a simple workbook application to demonstrate various ways of providing help. The application uses data stored in a worksheet to generate and print form letters .

As you can see in the following figure, cells display the total number of records in the database (C2, calculated by a formula), the current record number (C3), the first record to print (C4), and the last record to print (C5). To display a particular record, the user enters a value into cell C3. To print a series of form letters, the user specifies the first and last record numbers in cells C4 and C5.

The application is very simple, but it does consist of several discrete components. I use this example to demonstrate various ways of displaying context-sensitive help.

The form letter workbook consists of the following components:

• Form : A worksheet that contains the text of the form letter.

• Data : A worksheet that contains a seven-field database table.

• HelpSheet : A worksheet that's present only in the examples that store help text on a worksheet.

• PrintMod : A VBA module that contains macros to print the form letters.

• HelpMod : A VBA module that contains macros that control the help display. The content of this module varies depending on the type of help being demonstrated.

• UserForm1 : A UserForm present only if the help technique involves a UserForm.