Excel 2016 Power Programming with VBA (2016)

Part IV. Developing Excel Applications

Chapter 19. Providing Help for Your Applications

In This Chapter

· Providing user help for your applications

· Using only the components supplied with Excel to provide help

· Displaying help files created with the HTML Help system

· Associating a help file with your application

· Displaying HTML Help in other ways

Help for Your Excel Applications

If you develop a nontrivial application in Excel, you may 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 for using your application 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 may 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). Or you can simply display the support information in a text file, a Word document, or a PDF file.

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

Creating a compiled help file isn’t 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.

Note

Beginning with Microsoft Office 2007, Microsoft abandoned CHM help files in their Office product and used a different (and more complicated) Help system called MS Help 2. This Help system isn’t covered in this book.

About the examples in this chapter

Many of the examples in this chapter use a common 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 in 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 simple but 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 table of customer information.

· 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.

· UHelp: Present only if the help technique involves a UserForm.

On the Web

All the examples in this chapter are available on the book’s website. Because most examples consist of multiple files, each example is in a separate directory.

Help Systems That Use Excel Components

Perhaps the most straightforward method of providing help to your users is to use the features in Excel itself. The primary advantage of this method is that you don’t need to learn how to create HTML help files — which can be a major undertaking and might take longer to develop than your application.

In this section, I provide an overview of some help techniques that use the following built-in Excel components:

· Cell comments: Using comments is about as simple as it gets.

· A text box control: A short macro is all it takes to toggle the display of a text box that shows help information.

· A worksheet: An easy way to add help is to insert a worksheet, enter your help information, and name its tab Help. When the user clicks the tab, the worksheet is activated.

· A custom UserForm: A number of techniques involve displaying help text in a UserForm.

Using cell comments for help

Perhaps the simplest way to provide user help is to use cell comments. This technique is most appropriate for describing the type of input that’s expected in a cell. When the user moves the mouse pointer over a cell that contains a comment, the comment appears in a small window, like a tooltip (see Figure 19.1). Another advantage is that this technique doesn’t require macros.

Figure 19.1 Using cell comments to display help.

The automatic display of cell comments is an option. The following VBA instruction, which can be placed in a Workbook_Open procedure, ensures that cell comment indicators are displayed for cells that contain comments:

Application.DisplayCommentIndicator = xlCommentIndicatorOnly

On the Web

A workbook that demonstrates the use of cell comments is available on the book’s website in the cell comments\formletter.xlsm file.

Tip

Most users don’t realize it, but a comment can also display an image. Right-click the comment’s border and choose Format Comment from the shortcut menu. In the Format Comment dialog box, select the Colors and Lines tab. Click the Color drop-down list and select Fill Effects. In the Fill Effects dialog box, click the Picture tab and then click the Select Picture button to choose the image file.

As an alternative to cell comments, you can use Excel’s Data ➜ Data Tools ➜ Data Validation command, which displays a dialog box that lets you specify validation criteria for a cell or range. You can just ignore the data validation aspect and use the Input Message tab of the Data Validation dialog box to specify a message that’s displayed when the cell is activated. This text is limited to 255 characters.

Using a text box for help

Using a text box to display help information is also easy to implement. Simply create a text box by choosing Insert ➜ Text ➜ Text Box, enter the help text, and format it to your liking.

Tip

In lieu of a text box, you can use a different shape and add text to it. Choose Insert ➜ Illustrations ➜ Shapes and choose a shape. Then just starting typing the text.

Figure 19.2 shows an example of a shape set up to display help information. I added a shadow effect to make the object appear to float above the worksheet.

Figure 19.2 Using a shape object with text to display help for the user.

Most of the time, you won’t want the text box to be visible. Therefore, you can add a button to your application to execute a macro that toggles the Visible property of the text box. An example of such a macro follows. In this case, the TextBox is named HelpText.

Sub ToggleHelp()

ActiveSheet.TextBoxes("HelpText").Visible = _

Not ActiveSheet.TextBoxes("HelpText").Visible

End Sub

On the Web

A workbook that demonstrates using a text box for help is available on the book’s website in the textbox\formletter.xlsm file.

Using a worksheet to display help text

Another easy way to add help to your application is to create a macro that activates a separate worksheet that holds the help information. Just attach the macro to a button control and — voilà! — quick-and-dirty help.

Figure 19.3 shows a sample help worksheet. I designed the range that contains the help text to simulate a page from a yellow notebook pad — a touch that you may or may not like.

Figure 19.3 An easy method is to put user help in a separate worksheet.

To keep the user from scrolling around the HelpSheet worksheet, the macro sets the ScrollArea property of the worksheet. Because this property isn’t stored with the workbook, it must be set when the worksheet is activated.

Sub ShowHelp()

' Activate help sheet

Worksheets("HelpSheet").Activate

ActiveSheet.ScrollArea ="A1:C35"

Range("A1").Select

End Sub

I also protected the worksheet to prevent the user from changing the text and selecting cells, and I froze the first row so that the Return to the Form button is always visible, regardless of how far down the sheet the user scrolls.

The main disadvantage of using this technique is that the help text isn’t visible along with the main work area. One possible solution is to write a macro that opens a new window to display the sheet.

On the Web

This book’s website contains a workbook named worksheet\formletter.xlsm that demonstrates using a worksheet for help.

Displaying help in a UserForm

Another way to provide help to the user is to display the text in a UserForm. In this section, I describe several techniques that involve UserForms.

Using Label controls to display help text

Figure 19.4 shows a UserForm that contains two Label controls: one for the title and one for the help text. A SpinButton control enables the user to navigate among the topics. The text itself is stored in a worksheet, with topics in column A and text in column B. A macro transfers the text from the worksheet to the Label controls.

Figure 19.4 Clicking one of the arrows on the SpinButton changes the text displayed in the Labels.

Clicking the SpinButton control executes the following procedure. This procedure sets the Caption property of the two Label controls to the text in the appropriate row of the worksheet (named HelpSheet).

Private Sub sbTopics_Change()

HelpTopic = Me.sbTopics.Value

Me.lblTitle.Caption = _

Sheets("HelpSheet").Cells(HelpTopic, 1).Value

Me.lblTopic.Caption = _

Sheets("HelpSheet").Cells(HelpTopic, 2).Value

Me.Caption = APPNAME &" (Help Topic" & HelpTopic &" of" _

& Me.sbTopics.Max &")"

End Sub

Here, APPNAME is a global constant that contains the application’s name.

On the Web

A workbook that demonstrates this technique is available on the book’s website in the userform1\formletter.xlsm file.

Using Control tips in a UserForm

Every UserForm control has a ControlTipText property, which can store brief descriptive text. When the user moves the mouse pointer over a control, the Control tip (if any) is displayed in a pop-up window. See the accompanying figure.

Using a scrolling Label to display help text

The next technique displays help text in a single Label control. Because a Label control can’t contain a vertical scroll bar, the Label is placed inside a Frame control, which can contain a scroll bar. Figure 19.5 shows an example of a UserForm set up in this manner. The user can scroll through the text by using the Frame’s scroll bar.

Figure 19.5 Inserting a Label control inside a Frame control adds scrolling to the Label.

The text displayed in the Label is read from a worksheet named HelpSheet when the UserForm is initialized. Here’s the UserForm_Initialize procedure for this worksheet:

Private Sub UserForm_Initialize()

Dim LastRow As Long

Dim r As Long

Dim txt As String

Me.Caption = APPNAME &" Help"

LastRow = Sheets("HelpSheet").Cells(Rows.Count, 1).End(xlUp).Row

txt =""

For r = 1 To LastRow

txt = txt & Sheets("HelpSheet").Cells(r, 1).Text & vbCrLf

Next r

With Me.lblMain

.Top = 0

.Caption = txt

.Width = 260

.AutoSize = True

End With

Me.frmMain.ScrollHeight = Me.lblMain.Height

Me.frmMain.ScrollTop = 0

End Sub

Note that the code adjusts the Frame’s ScrollHeight property to ensure that the scrolling covers the complete height of the Label. Again, APPNAME is a global constant that contains the application’s name.

Because a Label can’t display formatted text, I used underscore characters in the HelpSheet worksheet to delineate the help topic titles.

On the Web

A workbook that demonstrates this technique is available on the book’s website in a file named userform2\formletter.xlsm.

Using a ComboBox control to select a help topic

The example in this section improves upon the preceding example. Figure 19.6 shows a UserForm that contains a ComboBox control and a Label control. The user can select a topic from the drop-down ComboBox or view the topics sequentially by clicking the Previous or Next button.

Figure 19.6 Using a drop-down list control to select a help topic.

This example is a bit more complex than the example in the preceding section, but it’s also much more flexible. It uses the label-within-a-scrolling-frame technique (described previously) to support help text of any length.

The help text is stored in a worksheet named HelpSheet in two columns (A and B). The first column contains the topic headings, and the second column contains the text. The ComboBox items are added in the UserForm_Initialize procedure. The CurrentTopic variable is a module-level variable that stores an integer that represents the help topic.

Private Sub UpdateForm()

Me.cbxTopics.ListIndex = CurrentTopic - 1

Me.Caption = APPNAME & _

" (" & CurrentTopic &" of" & TopicCount &")"

With Me.lblMain

.Caption = HelpSheet.Cells(CurrentTopic, 2).Value

.AutoSize = False

.Width = 212

.AutoSize = True

End With

With Me.frmMain

.ScrollHeight = Me.lblMain.Height + 5

.ScrollTop = 1

End With

If CurrentTopic = 1 Then

Me.cmdNext.SetFocus

ElseIf CurrentTopic > TopicCount Then

Me.cmdPrevious.SetFocus

End If

Me.cmdPrevious.Enabled = CurrentTopic > 1

Me.cmdNext.Enabled = CurrentTopic < TopicCount

End Sub

On the Web

A workbook that demonstrates this technique is available on the book’s website in the userform3\formletter.xlsm file.

Displaying Help in a Web Browser

This section describes two ways to display user help in a web browser.

Using HTML files

Yet another way to display help for an Excel application is to create one or more HTML files and provide a hyperlink that displays the file in the default web browser. The HTML files can be stored locally or on your corporate intranet. You can create the hyperlink to the help file in a cell (macros not required). Figure 19.7 shows an example of help in a browser.

Figure 19.7 Displaying help in a web browser.

Easy-to-use HTML editors are readily available, and your HTML-based Help system can be as simple or as complex as necessary. A disadvantage is that you may need to distribute a large number of HTML files. One solution to this problem is to use an MHTML file, which I describe next.

On the Web

A workbook that demonstrates this technique is available on the book’s website in the web browser\formletter.xlsm file.

Using an MHTML file

MHTML, which stands for MIME Hypertext Markup Language, is a web archive format. MHTML files can be displayed by Microsoft Internet Explorer (and a few other browsers).

The nice thing about using an MHTML file for an Excel Help system is that you can create these files in Excel. Just create your help text using any number of worksheets. Then choose File ➜ Save As, click the Save As Type drop-down list, and select Single File Web Page (*.mht; *.mhtml). VBA macros aren’t saved in this format.

In Excel, you can create a hyperlink to display the MHTML file.

Figure 19.8 shows an MHTML file displayed in Internet Explorer. Note that the bottom of the file contains tabs that link to the help topics. These tabs correspond to the worksheet tabs in the Excel workbook used to create the MHTML file.

Figure 19.8 Displaying an MHTML file in a web browser.

On the Web

A workbook that demonstrates this technique is available on the book’s website in the mhtml_file\formletter.xlsm file. Also included is the workbook used to create the MHTML file (helpsource.xlsx). Apparently, some versions of Internet Explorer won’t display an MHTML file that’s hyperlinked from a Microsoft Office file if the filename or path includes space characters. The example on the book’s website uses a Windows API function (ShellExecute) to display the MHTML file if the hyperlink fails.

Note

If you save a multisheet Excel workbook as an MHTML file, the file will contain JavaScript code — which may generate a security warning when the file is opened.

Using the HTML Help System

One of the most common Help systems used in Windows applications is HTML Help, which creates CHM files. This system replaces the old Windows Help system (WinHelp), which used HLP files. Both Help systems enable the developer to associate a context ID with a particular help topic, which makes it possible to display context-sensitive help topics.

Office XP was the last version of Microsoft Office to use HTML Help. Although HTML Help can’t duplicate the look and feel of Microsoft Office Help, it is still useful because it’s easy to work with — at least for simple Help systems.

In this section I briefly describe the HTML help-authoring system. Details on creating such Help systems are well beyond the scope of this book. However, you’ll find lots of information and examples online.

Note

If you plan to develop a large-scale Help system, I strongly recommend that you purchase a help-authoring software product. Help-authoring software makes it much easier to develop help files because the software takes care of lots of the tedious details for you. Many products are available, including freeware, shareware, and commercial offerings.

A compiled HTML Help system transforms a series of HTML files into a compact Help system. Additionally, you can create a combined table of contents and index as well as use keywords for advanced hyperlinking capability. HTML Help can also use additional tools such as graphics files, ActiveX controls, scripting, and DHTML (Dynamic HTML). Figure 19.9 shows an example of a simple HTML Help system.

Figure 19.9 An example of HTML Help.

On the Web

A workbook that demonstrates this technique is available on the book’s website in the html help\formletter.xlsm file.

HTML Help is displayed by HTML Help Viewer, which uses the layout engine of Internet Explorer. The information is displayed in a window, and the table of contents, index, and search tools are displayed in a separate pane. In addition, the help text can contain standard hyperlinks that display another topic or even a document on the Internet. It’s also important that HTML Help can access files stored on a website, so that you can direct users to more up-to-date information.

You need a special compiler (HTML Help Workshop) to create an HTML Help system. HTML Help Workshop, along with lots of additional information, is available free from Microsoft’s MSDN website. Navigate to this address and search for HTML Help Workshop:http://msdn.microsoft.com.

Figure 19.10 shows HTML Help Workshop with the project file that created the Help system shown in Figure 19.9.

Figure 19.10 Using HTML Help Workshop to create a help file.

general Displaying an Excel help topic

In some situations, you may want your VBA code to display a particular topic from Excel’s Help system. Every help topic has a topic ID, but identifying the topic ID is tricky in Excel 2016. For example, assume that you’d like to give the user the option to view Help information about the AGGREGATE function.

Start by searching for this topic in the Excel Help system. In the search results, highlight the appropriate link and press Ctrl+C to copy the link. Then activate a blank cell and press Ctrl+V to paste the link. Press Ctrl+K to display the Edit Hyperlink dialog box, which displays the address of the hyperlink. You’ll find, for example, that the address for the AGGREGATE function help topic is:

https://support.office.microsoft.com/client/AGGREGATE-function-

43B9278E-6AA7-4F17-92B6-E19993FA26DF?NS=EXCEL&Version=16

Copy that address and paste it into a web browser. View the source HTML of the web page by right-clicking on the page and choosing View Source (the menu item caption will be different for different browsers). In the head tag, near the top, look for a meta tag similar to the following:

<meta name="search.contextid" content="xlmain11.chm60533,

AGGREGATE, XLWAEnduser_AGGREGATE" />

This figure shows the meta tag on line 26 using the Chrome browser.

The topic ID is the first part of the content parameter. In this example, the topic ID is xlmain11 .chm60533. Use this topic ID as the argument in a VBA statement that uses the ShowHelp method of the Assistance object. For this example, the statement is:

Application.Assistance.ShowHelp" xlmain11.chm60533"

When this statement is executed, Excel’s Help system displays information about the AGGREGATE function.

Another option for displaying Excel help is to use the SearchHelp method. Just supply a search term, and the user will see a list of matching help topics. Here’s an example:

Application.Assistance.SearchHelp"AGGREGATE function"

Using the Help method to display HTML Help

Use the Help method of the Application object to display a help file — either a WinHelp HLP file or an HTML Help CHM file. This method works even if the help file doesn’t have context IDs defined.

The syntax for the Help method is as follows:

Application.Help(helpFile, helpContextID)

Both arguments are optional. If the name of the help file is omitted, Excel’s help file is displayed. If the context ID argument is omitted, the specified help file is displayed with the default topic.

The following example displays the default topic of myapp.chm, which is assumed to be in the same directory as the workbook from which it’s called. Note that the second argument is omitted.

Sub ShowHelpContents()

Application.Help ThisWorkbook.Path &"\myapp.chm"

End Sub

The following instruction displays the help topic with a context ID of 1002 from an HTML help file named myapp.chm:

Application.Help ThisWorkbook.Path &"\myapp.chm", 1002

Associating a help file with your application

You can associate a particular HTML help file with your Excel application in one of two ways: by using the Project Properties dialog box or by writing VBA code.

In Visual Basic Editor (VBE), choose Tools ➜ xxx Properties (where xxx corresponds to your project’s name). In the Project Properties dialog box, click the General tab and specify a compiled HTML help file for the project. This file should have a .chm extension.

The statement that follows demonstrates how to associate a help file with your application by using a VBA statement. The following instruction sets up an association to myfuncs.chm, which is assumed to be in the same directory as the workbook:

ThisWorkbook.VBProject.HelpFile = ThisWorkbook.Path &"\myfuncs.chm"

Note

If this statement generates an error, you must enable programmatic access to VBA projects. In Excel, choose Developer ➜ Code ➜ Macro Security to display the Trust Center dialog box. Then select the option labeled Trust Access to the VBA Project Object Model.

When a help file is associated with your application, you can call up a particular help topic in the following situations:

· When the user presses F1 while a custom worksheet function is selected in the Insert Function dialog box.

· When the user presses F1 while a UserForm is displayed. The help topic associated with the control that has the focus is displayed.

Associating a help topic with a VBA function

If you create custom worksheet functions with VBA, you might want to associate a help file and context ID with each function. After these items are assigned to a function, the help topic can be displayed from the Insert Function dialog box by pressing F1.

To specify a context ID for a custom worksheet function, follow these steps:

1. Create the function as usual.

2. Make sure that your project has an associated help file (refer to the preceding section).

3. In VBE, press F2 to activate Object Browser.

4. Select your project from the Project/Library drop-down list.

5. In the Classes window, select the module that contains your function.

6. In the Members Of window, select the function.

7. Right-click the function and then select Properties from the shortcut menu.

The Member Options dialog box is displayed, as shown in Figure 19.11.

8. Enter the context ID of the help topic for the function.

You can also enter a description of the function.

Figure 19.11 Specify a context ID for a custom function.

Note

The Member Options dialog box doesn’t let you specify the help file. It always uses the help file associated with the project.

You may prefer to write VBA code that sets up the context ID and help file for your custom functions. You can do this by using the MacroOptions method.

The following procedure uses the MacroOptions method to specify a description, help file, and context ID for two custom functions (AddTwo and Squared). You need to execute this macro only one time.

Sub SetOptions()

' Set options for the AddTwo function

Application.MacroOptions Macro:="AddTwo", _

Description:="Returns the sum of two numbers", _

HelpFile:=ThisWorkbook.Path &"\myfuncs.chm", _

HelpContextID:=1000, _

ArgumentDescriptions:=Array("The first number to add", _

"The second number to add")

' Set options for the Squared function

Application.MacroOptions Macro:="Squared", _

Description:="Returns the square of an argument", _

HelpFile:=ThisWorkbook.Path &"\myfuncs.chm", _

HelpContextID:=2000, _

ArgumentDescriptions:=Array("The number to be squared")

End Sub

After executing these procedures, the user can get help directly from the Insert Function dialog box by clicking the Help on This Function hyperlink.

On the Web

A workbook that demonstrates this technique is available on the book’s website in the function help\myfuncs.xlsm file.