Introduction:
One of the biggest parts lacking in user constructed solutions is documentation in the form of help files. As coders, we usually find it easier to build the application than explain to someone how to use it properly. After all, what we write in code is rarely read by others, and most novices think it's gibberish anyway. Actually explaining what you do in plain English (or whatever language you use) can be quite difficult. Ironically, it is this very fact that tells us why help files are important... write it down once, nicely, and you should never have to explain it again.
Office makes use of Compiled HMTL (chm) help files, which can be created by any number of applications. While writing these documents can be a difficult task of explaining yourself to the end user, the final trick is getting the compiled file to work with your application. This article was written to help ease the burden of the coding part, but unfortunately I'm going to leave the actual construction of the files to you, although I will give you some pointers to get started.
Programs to build help files:
First thing is first, you'll need to install a copy of Microsoft's HTML Workshop, if you don't have it installed already. This program is used by all Help authoring software, so it's not really an optional thing.
The next step is to find a suitable HTML Help compiler. Personally, I use West Wind Help Builder. It has an excellent FAQ included on how to start using the program, and allows for creation of some very rich help files. I'm a big fan of being able to easily write my documents including screen shots, hyperlinks, and context IDs, and West Wind Help makes this fairly easy. It even integrates nicely with another fantastic product, Tech Smith's SnagIt, for capturing nice screen shots. There are trial versions of both products available at the links provided.
Other programs do exist, but I cannot speak to their usefulness, as I have not worked with any of them extensively.
Built in Help methods:
Microsoft Excel does have some built in help constants and methods that you can use to deliver help in your applications. They work to a point, but they do have limitations. My biggest complaint about them are:
- You cannot have a space in the help file's name. If you have a space in the name, trying to use Excel's built in help methods will cause the help window to open, and your computer will churn away until it eventually returns the delightful error reading "There is not enough memory available for this task. Quit one or more available programs to increase available memory and then try again." The file I tested this with was all of 87kb!
- When you do open a help file using the built in methods, it opens it without the navigation menu on the left hand side of the screen. This is purely unacceptable to me, since we want our users to be able to easily navigate the rest of our help files if the screen we presented was not sufficient to answer their question.
Using the built in Help methods:
The following are illustrations of using Excel's built in help methods to launch a help file. (The sample chm file can be downloaded in the package at the end of this article, should you want to try this at home.)
- To display the help file:
Code:
Application.Help ThisWorkbook.Path & "samplehelp.chm"
- To display the help file at ContextID 101:
Code:
Application.Help ThisWorkbook.Path & "samplehelp.chm",101
- To add help to a messagebox and link it to contextID 101
Code:
msgbox "Hello",vbOKOnly+vbMsgBoxHelpButton,"Test",ThisWorkbook.Path & "samplehelp.chm",101
Each of the above will open the help file, (or change the focus of an existing help instance), to the provided context. In every case, however, there is no menu structure associated with the project, as shown in the image below:
A better way:
The great thing about the problems above is that they can be solved. And even better, I've put together some routines so that it is done for you! The entire set of code below can be downloaded in the packaged attached to the end of this article.
The method that I am about to illustrate works by calling some Windows API functions, allowing us to control the HTML Help file engine. Unlike the built in functions, it will allow you to use help files with a space in the name, and also displays the topic tree to the left of the context window, as shown in the image below. Should you choose, you can even have the help file open at the Index or Search windows.
One big "Gotcha" to be aware of:
Thanks to Microsoft Security Update 896358 (and also Windows 2003, SP1), Chm help files cannot be run from a network location. In addition, they may not work if opened directly from the internet. The issue is actually quite well documented at HelpScribble, along with links to the actual KB entries from Microsoft.
There are some workarounds provided, but they do involve manipulating registry keys. Since they affect much more than just your chm file, I don't believe that it is a responsible way to patch this issue for clients. The best workaround solution (unfortunately) is to make every effort to ensure that your help file is saved on a local drive.
Code Required:
Place the following code in a standard module to allow you to open help files from anywhere within your project. It can also be tailored to fit in a userform, as we'll discuss later in the article.
Option Explicit '--------------------------------------------------------------------------------------- 'Windows API calls: '--------------------------------------------------------------------------------------- 'Launch the HTML help files in the HTML Help viewer Private Declare Function HTMLHelpShowContents Lib "hhctrl.ocx" _ Alias "HtmlHelpA" (ByVal hwnd As Long, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByVal dwData As Long) As Long 'Launch the HTML help files in the HTML Help viewer with the Search tab active Private Declare Function HTMLHelpShowSearch Lib "hhctrl.ocx" _ Alias "HtmlHelpA" (ByVal hwnd As Long, _ ByVal lpHelpFile As String, _ ByVal wCommand As Long, _ ByRef dwData As tagHH_FTS_QUERY) As Long '--------------------------------------------------------------------------------------- 'User Defined Types '--------------------------------------------------------------------------------------- Private Type tagHH_FTS_QUERY 'Date Created : Unknown 'Source : http://frogleg.mvps.org/helptechnologies/htmlhelp/ 'Type Purpose : Required paramaters for accessing the HTML Help Search tab cbStruct As Long fUniCodeStrings As Long pszSearchQuery As String iProximity As Long fStemmedSearch As Long fTitleOnly As Long fExecute As Long pszWindow As String End Type '--------------------------------------------------------------------------------------- 'Public Enums '--------------------------------------------------------------------------------------- Public Enum HelpType 'Date Created : October 13,2006 11:09 'Author : Ken Puls (www.excelguru.ca) 'Enum Purpose : Allow use of constants in the 'HelpType' argument HelpType_Show = 0 HelpType_Contents = 1 HelpType_Index = 2 HelpType_Search = 3 HelpType_Context = 4 End Enum '--------------------------------------------------------------------------------------- 'Public Functions '--------------------------------------------------------------------------------------- Public Function ShowHelp(ByVal lType As HelpType, ByVal strHelpPath As String, _ Optional ByVal lContextID As Long = 0) As Boolean 'Date Created : October 13,2006 11:09 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: Open a CHM (Compiled HTML Help) file at the correct location as ' specified by the user Dim lSuccess As Long Dim HH_FTS_QUERY As tagHH_FTS_QUERY 'Check what point user wants file opened at Select Case lType Case Is = HelpType_Show 'Display the help file at last selected item lSuccess = HTMLHelpShowContents(0, strHelpPath, &H0, 0) Case Is = HelpType_Contents 'Display the table of contents at last selected item lSuccess = HTMLHelpShowContents(0, strHelpPath, &H1, 0) Case Is = HelpType_Index 'Display the index. lSuccess = HTMLHelpShowContents(0, strHelpPath, &H2, 0) Case Is = HelpType_Search 'Display full text search. With HH_FTS_QUERY .cbStruct = Len(HH_FTS_QUERY) .fUniCodeStrings = 0& .pszSearchQuery = "" .iProximity = 0& .fStemmedSearch = 0& .fTitleOnly = 0& .fExecute = 1& .pszWindow = "" End With lSuccess = HTMLHelpShowSearch(0, strHelpPath, _ &H3, HH_FTS_QUERY) Case Is = HelpType_Context If lContextID = 0 Then 'No context provided, open at top level lSuccess = HTMLHelpShowContents(0, strHelpPath, &H1, 0) Else 'Ensure that Contents window is active HTMLHelpShowContents 0, strHelpPath, &H1, 0 'Open at context ID provided lSuccess = HTMLHelpShowContents(0, strHelpPath, &HF, lContextID) End If End Select If lSuccess = 0 Then 'Was not succesful opening file ShowHelp = False Else 'Opened successfully ShowHelp = True End If End Function
It is the very last function in this block of code that makes it all work. To call a help file, you need only ask for the return of the ShowHelp function. If a help file is opened, it will return true, and if not, it will return false. This allows you to tell if your call to Help was successful or not.
Using Custom Help:
You would use the custom help function in much the same way as you'd use regular help. The following routine, (to be entered in the immediate window,) demonstrates one potential use:
? showhelp(HelpType_Context,ThisWorkbook.Path & "samplehelp.chm",101)
If the help file is succesfully shown, the value True will be printed to the immediate window.
Using Custom Help in a Userform:
Now the above is all great... you can now show your help file with the navigation tree, but what about including it in a userform? In truth, you don't actually need to modify it at all, if you don't mind having a module to hold the HTML Help code. You'd just call the function as you normally would from any standard module (probably from a button_click event.)
On the other hand, I often want to include the code in a userform to make one nice little package. (This allows me to export the userform to file, so that I can import it into any other project.) Doing this is quite simple:
- Copy all of the help file code above into the userform
- Replace the two instances of "Public" with "Private"
That's it! The code will function just the same, but no other module will interfere with it, and your userform is portable. (Providing you've coded the rest well, anyway.) Even better is that if you do import that userform into a project that has other Help laden userforms or even the HTML Help module code from above, they won't conflict.
Using Custom Help with a MessageBox:
The last topic we'll cover is how to use the custom HTML help function in a messagebox. The sad part about this is that it's not quite as easy as using it elsewhere, since we cannot attach this code to the default VBA messagebox. We can, however, create our own instance of a messagebox to replace VBA's.
"Rolling your own" messagebox is not an easy task for the newly initiated. You'e seen just how easy converting the help code is, but unfortunately it's replicating the rest of the messagebox features that is the difficult part. Because of this, I have attached a copy of a standalone userform that can be imported into your project. This userform is a custom VBA messagebox which contains virtually all the regular VBA messagebox functionality except the ability to show a dialog box modally. (If you don't know what that means, don't worry about it.)
The Custom Userform has been tested extensively in Excel 2003. Additional tests were performed in Excel 2002, Excel 2007 and Access, Publisher, Powerpoint and Word 2003. I feel fairly confident that this messagebox will work in any Office application version 2002 and higher, and may even work in Office 2000 as well. If you need to convert it to work with Office 97, you will need to replace the Enum in the userform (at a minimum), as Office 97 did not support the use of Enum declarations.
To use the custom messagebox, you must call it from the following routine:
Public Function CustomMsgBox(ByVal Prompt As String, Optional ByVal Buttons As VbMsgBoxStyle = vbOKOnly, _ Optional ByVal Title As String, Optional ByVal HelpFile As String, _ Optional ContextID As Long = 0) As VbMsgBoxResult 'Author : Ken Puls (www.excelguru.ca) 'Macro Purpose: To retrieve feedback from a custom messagebox With MsgBoxCustom 'Load the default properties .Prompt = Prompt .Buttons = Buttons .Title = Title .HelpFile = HelpFile .ContextID = ContextID 'Show the userform .Initialize 'Record the user's selection CustomMsgBox = .UserClick 'Close the userform .Terminate End With End Function
An example of testing this code from the immediate window is shown below:
? CustomMsgBox("Testing custom messagebox", vbOKOnly + vbInformation + vbMsgBoxHelpButton, _
"This is a test", Thisworkbook.Path & "samplehelp.chm",101)
Download Package:
The attached download package is a zip file that contains five files:
- CustomMsgBox.bas
- The module that contains the code to run the custom messagebox
- CustomMsgBox.frm
- The userform file that contains the custom messagebox, complete with Help code
- CustomMsgBox.frx
- An additional component to the above
- HTMLHelp.bas
- The module that contains the complete HTML Help code
- SampleHelp.chm
- A sample help file (contains context ID's 1,2,101,102,201,202)
To import the modules into your project:
- Download and extract the files from the link below
- Enter the Visual Basic Editor
- Right click the project that you want to use the code in
- Choose Import
<img src="/images/node73-2.jpg"</a>
- Find the appropriate file(s) that you want to import and say okay
Remember that to use the CustomMsgBox, you will need to import both the bas and frm objects. The HTMLHelp can be imported on its own, or with the CustomMsgBox objects.
One potential issue (and resolution):
Due to the invoking of some API's, it is important to let VBA clean up at the end of a procedure. This cannot happen when you exit a routine using a coding structure such as the following:
If *condition* Then End
If you do have code like this in your project, you may find that you get multiple instances of Help. Worse, if you call help with a userform open, dismiss the form, then click on the help window, Excel may crash. To eliminate this, use proper error handling, and allow the application to exit the routine gracefully.
References and Recognition:
I would like to thank the following people:
- John Walkenbach for giving me his generous permission to post this publicly. While the messagebox code has been expanded from the original version, this userform is built on John's code, which was included as a sample file with his excellent "Excel 2002 Power Programming With VBA" book.
- Stephen Bullen for reviewing my alterations to the CustomMsgBox function and suggesting some improvements.
- The Help Technology Centre, for their excellent white paper on Using HTML Help with Microsoft Access (website no longer available)
- Andy Pope, for pointing me to the above article.
- Jon Peltier, for pointing out the crashing issue, as well as the solution.
Syndication:
This article has also been published at Professional Office Developers Association (website no longer available).
Sample File:
