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

Providing Help

From the very start of our project, we should be thinking about how to provide assistance to our users. For simple add-ins, this can take the form of a set of instructions displayed at the bottom of an About dialog, or a separate text file or Word document distributed with the add-in. For more complex add-ins and dictator applications, we should consider providing a help file. Doing so requires us to add code throughout the application to display pages from the help file as well as writing the help text.

To demonstrate how to create a help file and display it from our application, we use the Microsof219 Help Workshop to create a very simple help file for our PETRAS reporting application. Th204 Help Workshop is a fairly rudimentary tool, best used for simple help files. For large and/or complex help files, we recommend you use a third-party application, such as Macromedia's RoboHelp ([ www.robohelp.com ]) or Component One's Doc-to-Help ([ http://www.doctohelp.com/products.aspx?ProductCode=1&ProductID=122 ]).

Overview

We create each page of a213 help file as a separat204 file and give it a code word, known as a topic. We can give it any name we like, but it's good practice to use a naming convention to help us quickly and easily identify each topic. For example, we might use the name htFrmActivities to denote the help topic for a userform used to maintain a list of activities. Th204 Help Workshop is used to create and maintain a help project file, which contains configuration information and a list of the topics to include in the help file. After all the help content has been written, th204 Help Workshop is used to compile the individua211 files into a singl204 help file (with an extension of .chm) that we distribute with our application. To display help pages from Excel, we have to give each topic a unique number. The mapping between topic names and numbers is also stored in the help project file.

Getting Started

If you don't already have th204 Help Workshop installed, you can download it from [ http://go.microsoft.com/fwlink/?LinkId=14188 ]. To produce a compiled skeleton help file, you need to follow a number of steps:

Create a Help Project File

To create a new help project, start th204 Help Workshop, click File > New, select Project from the list and click OK to launch the New Project Wizard. We're not converting an old help project, so skip over Step 1. In Step 2, type in or select a location and name for the help project file, such as C:\PETRAS\Help\PETRAS.hhp (where the C:\PETRAS\Help directory must already exist). Ideally, this should be in an empty directory, because we'll be adding lots of files later. By default, the name we give to the help project file will be used for the compiled help file, but we can change it later if we want. We don't have any existing files to include, so skip Step 3 and click Finish. We end up with a help project similar to

Update the Project Options

Click the Change project options button shown in Figure 24-2 to set the initial project options.

Figure 24-2. The Change Project Options Button

In the General tab of the Options dialog, give our help file the title of PETRAS Reporting Application. In the Files tab, type in a Contents filename of Contents.hhc, an Index filename of Index.hhk and tick both check boxes. By doing this, the Help Workshop will automatically populate a table of contents and an index file for us when the project is compiled. Set the Maximum head level to 1, which tells the compiler to create TOC entries from all the <H1> header tags we include in ou217 files. In the Compiler tab, tick the Compile full-text search information, to add a Search tab to the help file. Click OK to close the Options dialog.

To create the table of contents and index files, click the Contents and Index tabs. With each tab, ignore the error message that the file can't be found and let the Help Workshop create new files. Give them the names Contents.hhc and Index.hhk.

Create an Introductory HTML File

To create the pages of the help file, we can use any application that will generat204 files, such as FrontPage, Word, Notepad or th204 Workshop itself. We can create a simple introductory page within th204 Workshop, so click File > New > HTML File and give it a title of Introduction. Just below the <BODY> tag, type in the following:

<H1>Introduction</H1>
This is the help file for the PETRAS reporting application.

Save the file as C:\PETRAS\Help\htIntro. After creating the file, we need to include it in our help project. Click the Add/Remove topic files button shown in Figure 24-3, click the Add button and select the htIntro file.

Figure 24-3. The Add/Remove Topic Files Button

Create a "No Help Available" Topic File

When we first set up the help file, we'll point every topic to the same help page, which will display a simple "No Help Available" message. To create the page, add a ne222 file and type the following text after the <BODY> tag:

Sorry, there is no help available for this topic.

Save the file as htNoHelp and use the Add/Remove topic files button to add it to our project.

Create a List of Topics

We can create a skeleton help file containing just a few topics and add to it as we develop our application. Click File > New > Text to create a blank text file and type in the list of topics shown in Listing 24-1, with each one referring to the htNoHelp file.

Listing 24-1. The PETRAS Help File Topic List

htNoHelp=htNoHelp           ;The No Help page
htFrmActivities=htNoHelp    ;The Activities userform
htFrmConsultants=htNoHelp   ;The Consultants userform
htFrmClients=htNoHelp       ;The Clients/Projects userform
htFrmExtractData=htNoHelp   ;The Extract Data userform

Save the file as C:\PETRAS\Help\TopicToFile.h. Each time we add a form to our application, we should give it a topic name and add the topic to this list. When we come to write the help file content, we can edit this list to point each topic to the correc219 file. To include the list of topics in our help project, click the

button (the fourth of the Project buttons), select the Alias tab, click the Include button and type in the filename of TopicToFile.h.

Give Each Topic a Numeric ID

When we display a help file from Excel, we have to use numeric IDs for our help topics. We map each topic to an ID in the same way we map them t214 files. Click File > New > Text to create another text file and type in the list of mappings shown in Listing 24-2.

Listing 24-2. The PETRAS Help File Topic IDs

#define htNoHelp           100   //The No Help page
#define htFrmActivities   101   //The Activities userform
#define htFrmConsultants  102  //The Consultants userform
#define htFrmClients       103  //The Clients/Projects userform
#define htFrmExtractData  104  //The Extract Data userform

We're actually creating a C-style header file, defining these topic names as constants using the #define directive. The // is used to indicate the start of a comment. Save the file as C:\PETRAS\Help\TopicToID.h. Again, each time we add a new feature to our application we should give it a topic ID and add the topic name and ID to this list. We also need to include this file in our help project, by clicking the

button, selecting the Map tab, clicking the Header file button and typing in the filename of TopicToID.h.

Compile the Project

Click File > Compile to create the PETRAS.chm help file and then click View > Compiled File to display it. If all went well, you should see a window similar to Figure 24-4.

Figure 24-4. The PETRAS Reporting Skeleton Help File

Writing Content

The best recommendation we can give about writing help content is this: Don't write it yourself. As the developer of the application, you know far too much about its inner workings to be able to explain it at a level that an average user can understand. The best person to write the help content is a user representative, if you have one. He will be able to explain the application in business terms, including how it should be used within the business environment. When writing the content, there are two things a user representative needs to do to allow the Help Workshop to automatically generate the Table of Contents and Index files.

Table of Contents

In the Files tab of the project's Options dialog, we can specify the Maximum head level to use when automatically creating the table of contents. That number corresponds to the <Hn> heading styles used in th204 files. If Word is being used to write the help content, the number corresponds to Word's heading styles. If these tags are used consistently throughout all the help files, the table of contents can be generated without any effort on our part.

Index

In the Files tab of the project's Options dialog, we can tick the Include keywords fro212 files box. The content author can then include the keywords for the index within the sourc204 file. To do so, the author needs to include the <Object> tag shown in

<Object type="application/x-oleobject" 
classid="clsid:1e2a7bd0-dab9-11d0-b93a-00c04fc99f9e">
<param name="Keyword" value="Keyword1">
<param name="Keyword" value="Keyword2">
</Object>

Displaying Help from VBA

When we display a help topic from VBA, we have to use the numeric topic IDs rather than the names. Instead of scattering these "magic numbers" throughout our code, it is a very good idea to expose them in an enumeration, such as the one shown in Listing 24-4.

Listing 24-4. Enumeration for the Help Topic IDs

Public Enum pxlHelpTopics
htNoHelp = 100               'Generic 'No Help Available'
htFrmActivities = 101       'The Activities userform
htFrmConsultants = 102      'The Consultants userform
htFrmClients = 103           'The Clients/Projects userform
htFrmExtractData = 104      'The Extract Data userform
End Enum

We can then use the enumeration members instead of the topic IDs. For example, the code in Listing 24-5 can be used to display a message box with a Help button that will show our help file when clicked.

Listing 24-5. Displaying a Help File from a MsgBox

Sub ShowAMessage()
Dim sMessage As String
Dim sHelpFile As String
sHelpFile = ThisWorkbook.Path & "\petras.chm"
sMessage = "The activity name is already being used."
MsgBox sMessage, vbOKOnly + vbMsgBoxHelpButton, _
"Add Activity", sHelpFile, htFrmActivities
End Sub

Other than using the MsgBox's additional arguments, the best way to display a help file from VBA is to call directly into the HHCtrl.ocx file, using th204Help API function. Although it is possible to use Application.Help to display a custom help file, it usually mixes our help file with Excel's. In Listing 24-6, we've wrapped the call in a generic ShowHelp procedure, to verify that the help file exists and tidily handle missing topic IDs.

Listing 24-6. Displaying a Help File by Calling the HHCtrl.ocx

Declare Functio213Help Lib "HHCtrl.ocx" _
Alias "HtmlHelpA" (ByVal hwndCaller As Long, _
ByVal pszFile As String, ByVal uCommand As Long, _
ByVal dwData As Long) As Long
Const HH_DISPLAY_TOPIC = &H0
Const HH_HELP_CONTEXT = &HF
'Show a topic of a help file
Sub ShowHelp(ByVal uTopicID As pxlHelpTopics)
Dim sHelpFile As String
Dim sCheckFile As String
Dim lResult As Long
'Locate the help file
sHelpFile = ThisWorkbook.Path & "\PETRAS.chm"
'Check the help file exists
On Error Resume Next
sCheckFile = Dir(sHelpFile)
On Error GoTo 0
If Len(sCheckFile) > 0 Then
'Try to show the requested help topic
lResult = HtmlHelp(0, sHelpFile, HH_HELP_CONTEXT, _
uTopicID)
If lResult = 0 Then
'If it failed, try to show the 'No Help' topic
lResult = HtmlHelp(0, sHelpFile, HH_HELP_CONTEXT, _
htNoHelp)
End If
End If
'If we couldn't find the help file, or failed to show it,
'display a message in VBA.
If lResult = 0 Then
MsgBox "No help is available at this time.", _
vbOKOnly, "PETRAS Reporting"
End If
End Sub

We should call this procedure whenever we want to display a help topic, such as in the Click event handler of a form's Help button, shown in Listing 24-7. Using the enumeration member helps to ensure we're showing the correct topic for the form.

Listing 24-7. Using the ShowHelp Procedure from a Form's Help Button

Private Sub cmdHelp_Click()
ShowHelp htFrmActivities
End Sub