Dutch Gemini's Weblog Pages

February 26, 2013

Show a specific page of a CHM help file from your Office application with VBA

Filed under: Excel,Programming,VBA — dutchgemini @ 11:20 am

Update for users of Office 2010: starting this version things have changed slightly, and the information provided here does not work anymore.  I have added the necessary modifications at the bottom of this article.

Users of a pre-2010 version of Office may benefit, too.


This thread is not about creating a help file for your VBA application – there are many other pages on the Internet that tell or sell better solutions than you will find here – but about accessing pages of a CHM via VBA code.

As most programmers know, a strong added value of any application developed is on-line help. In the application I develop I had the need to provide some on-line help on the format that one could use for customising the display and printout of dates.

Office (XP/2003/2007) VBA provides a method called ‘Application.Help’. This method has 2 parameters:

  1. (string) The name of the help file, either HLP or CHM
  2. (long) The Help Context ID

Thanks to Ron De Bruin – a dutch MVP making tons of great tips available to the public  – I obtained the list of Help Context ID’s that give access to Excel’s Help pages.

However, I could not use it because what I was looking for was not in Excel’s Help, but is provided by VBA in a file called ‘Visual Basic Language Reference’ and named ‘VBLR6.CHM’. Where is this file? In a standard installation of Office, it is located in the folder called ‘C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033’. In addition, for VBLR6.CHM there is no such list as the Help Context IDs, or I was not able to find it which is far more likely.

I was finally able to resolve the problem because the above mentioned method is capable to open help pages from URL as well, that is, the CHM does not have to be on a local disk.

I am not an expert and have only seen someone else create CHM’s (we outsource this activity). But since a CHM is a compiled help file, I thought it had to have some kind of file or menu structure embedded inside (how can it otherwise organise and create cross-references?) and that it should be possible, knowing the ‘key’, to target a specific page in the CHM file structure and display it. How?

The answer came from investigating the page I was displaying. I have summarised what I did in the following steps.

Step 1 – from the VB-IDE I located the help page related to the information I was looking for, in my example ‘User Defined Formats’ and opened the related help page:

Looking for help in VBIDE

Visual Basic Help

Step 2 – I right clicked on the page and selected ‘Properties‘. The key we need is in the highlighted box:

Page Properties

Step 3 – I selected all the text in the Address (URL) field and copied it to the clipboard.

Address to this page

In my case, the full URL sounds:

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove the first part being ‘mk:@MSITStore:‘ leaving us with:

C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. Note that I omitted the Help Context ID here (which is optional, btw).

Application.Help "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\1033\vblr6.chm::/html/vafmtStringFormats.htm"

There is still one problem to tackle, and that is the actual path to the file. Depending on your language and installation of Windows and on your version of Office, this path can change. For instance in french we would have (I have highlighted the differences in bold):

C:\Programmes\Fichiers Communs\Microsoft Shared\VBA\VBA6\1036\vblr6.chm::/html/vafmtStringFormats.htm

VBA again is able to help us, offering us these 2 functions:

  • Environ() retrieving settings from the OS, and
  • Application.LanguageSettings.LanguageID() returning the ID of the language.

With this final information at hand we create this chunk of code and VBA is going to show us exactly the page we wanted:

Application.Help VBA.Environ$("commonprogramfiles") & _
                 "\Microsoft Shared\VBA\VBA6\" & _
                 Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                 "\vblr6.chm::/html/vafmtUserDefinedDateFormats.htm"

That’s it. Hook it to a command button or menu and you have your context sensitive help.

Should work with any CHM file (no guarantee though, my name is not Bill G.)


Instructions for Office 2010 users

Office 2010 uses the new VBA7 Visual Basic for Application engine, and I have seen that this one does not really cope correctly with the Application.Help instruction. Instead of opening the given file, I always end up with standard Office Help. But there is a solution that also works on pre-2010 versions of Office, based on a Win32API function.

First we need to load the Win32API function in our project. The declaration is for a 32-bit version of Office

Public Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" (ByVal hwndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Any) As Long
Public Const HH_DISPLAY_TOPIC = &H0

The important part in the definition is the last parameter declaration: it use ‘As Any‘ instead of ‘As Long’ what you may find in other posts, which will allow us providing help ID’s as Longs and as Strings, the latter we will use here. The only constant declared (there are others) is used when calling this function to display the desired topic from our help file.

Finally we need to call this function. The next example uses again the instructions explained above for Office XP/2003/2007, but with the necessary differentiation for handling 2010’s new VBA7 engine while supporting legacy VBA6 engine.

The page reference is slightly different from the one for Office XP/2003/2007. You must take the path to the desired page working the HTML structure from the root up. Similar to above, the full URL for 2010 is (note the different folder VBA7 instead of VBA6):

mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA7\1033\vblr6.chm::/html/vafmtStringFormats.htm

From this URL we can remove everything up to the double colon including the first backslash:

html/vafmtStringFormats.htm

This last chunk we use in VBA to access this page and as such translated it for use in VBA. The next statements include localisation support for retrieving the correct language file.

Dim sCHMHelpFile As String, sPageReference As String
#If VBA7 Then
    ' Office 2010: the help file is in a folder named 'VBA7'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA7</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#Else
    ' Office pre-2010: the help file is in a folder named 'VBA6'
    sCHMHelpFile = VBA.Environ$("commonprogramfiles") & _
                   "\Microsoft Shared\VBA\<strong>VBA6</strong>\" & _
                   Application.LanguageSettings.LanguageID(msoLanguageIDUI) & _
                   "\vblr6.chm"
#End If
sPageReference = "html/vafmtStringFormats.htm"
Call HtmlHelp(0, sCHMHelpFile, HH_DISPLAY_TOPIC, sPageReference)

Have fun.

Advertisements

4 Comments »

  1. Thanks, I was able to use this post to finish off a problem.

    Comment by Phil Bornemeier — April 16, 2012 @ 1:34 pm | Reply

  2. This is exactly what I’ve been looking for for the better part of an hour. Thanks!

    Comment by Zach — April 17, 2012 @ 3:29 pm | Reply

  3. This is really helpful thanks. Two questions through, is there an easier way of looking up the URL associated with an error without having to go into the properties and then copying into the clipboard ? Also, it is possible to open the help file in another process, Shell ?

    Comment by davemcwish — April 23, 2014 @ 2:18 pm | Reply

    • If you can decompile the help file then you can obtain the correct URL to the desired page from the file structure, otherwise the only way I know is going through the properties dialog.

      Indeed you can call help via shell, actually I have been using “HtmlHelp()” function from Win32API:


      ' ------------------------------------------------------
      ' Used for the 'Help' functions.
      ' On HtmlHelp the use of 'As Any' allows also strings with URL/HTML pages as references.
      ' ------------------------------------------------------
      '
      Public Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" (ByVal hWndCaller As Long, ByVal pszFile As String, ByVal uCommand As Long, ByVal dwData As Any) As Long
      Public Const HH_DISPLAY_TOPIC As Long = &H0 ' Display the help file.
      Public Const HH_CLOSE_ALL As Long = &H12 ' Close the help file.

      {...}

      ' Activate the help.
      '
      Call HtmlHelp(0&, "C:\vblr6.chm", HH_DISPLAY_TOPIC, "html/vafmtUserDefinedDateFormats.htm")

      {...}

      ' Close the help when not needed any more.
      '
      Call HtmlHelp(0&, "C:\vblr6.chm", HH_CLOSE_ALL, ByVal 0&)

      You may want to use the window handle “HWnd” as the first parameter to “HtmlHelp()” so that help opens consistent with the UserForm you are calling help from. To obtain the “HWnd” from an Excel UserForm google for “UserForm FindWindow ThunderDFrame VBA”.

      One thing to really do, is closing the help file when not needed any more: if you call the HH API directly from your application, and not via a second helper program like HH.EXE or KEYHH.EXE, then you MUST close any open help windows before shutting down the application or you will probably crash Windows. Starting Excel 2007, you get crashes with a GPF General Protection Fault (Microsoft Windows program crash/error).

      With VBA you need to pass the name of a help file and cannot use an empty string (e.g. vbNullString)

      Comment by dutchgemini — April 24, 2014 @ 11:22 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: