chm - Excel 2010 "Help on this function" does not launch HH.EXE with -mapid parameter

Question

I am developing an Excel add-in with a CHM help file. The CHM has topics that I am trying to reach from Excel's "Insert Function" dialog. This is confirmed when I invoke HH.EXE as follows:

HH.EXE -mapid 1234 "mk:@MSITSTORE:<path-to-my-chm-file>"

I registered all of my UDFs with the Application.MacroOptions( ) function, passing the appropriate parameters (Macro, Category, HelpContextID and HelpFile).

When I click the "Help on this function" link, HH.EXE is invoked with the correct path and file name of my CHM. However, there is no -mapid parameter used for the invocation of HH.EXE. Consequently, when my CHM file is loaded, HH does not go to the desired topic.

Does anyone know or have a guess as to why Excel may be omitting this parameter?

Thank you!

Answer 1

As I understand you want something like shown in the snapshot. Context-sensitive help is complex.

I add sample code for buttons and code for calling the HTMLHelp (CHM) API.

Office 2007 is installed on my machine only.

This is done by a HTMLHelp API call (code snippet (1)):

    '******************************************************************************
    '----- Modul - definition for HTMLHelp
    '----- (c) Ulrich Kulle, http://www.help-info.de/en/Visual_Basic_Applications/vba.htm
    '----- 2002-08-26 Version 0.2.xxx
    '******************************************************************************
    '----- Portions of this code courtesy of David Liske.

    Declare Function IsWindow Lib "user32.dll" (ByVal hwnd As Long) As Long

    Private Declare Function SendMessage Lib "user32" _
     Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
     ByVal wParam As Long, lParam As Any) As Long

    Private Declare Function HtmlHelp Lib "hhctrl.ocx" Alias "HtmlHelpA" _
                    (ByVal hwndCaller As Long, ByVal pszFile As String, _
                    ByVal uCommand As Long, ByVal dwData As Long) As Long

    Declare Function HTMLHelpTopic Lib "hhctrl.ocx" Alias "HtmlHelpA" _
             (ByVal hwndCaller As Long, ByVal pszFile As String, _
             ByVal uCommand As Long, ByVal dwData As String) As Long

    Private Declare Function HtmlHelpSearch Lib "hhctrl.ocx" Alias "HtmlHelpA" _
            (ByVal hwndCaller As Long, ByVal pszFile As String, _
            ByVal uCommand As Long, dwData As HH_FTS_QUERY) As Long

    '--- to keep the handle of the HH windows when calling help by API --------
    Public HHwinHwnd As Long

    '--- some constants used by the API ---------------------------------------
    Public Const HH_DISPLAY_TOPIC = &H0     ' select last opened tab, [display a specified topic]
    Public Const HH_DISPLAY_TOC = &H1       ' select contents tab, [display a specified topic]
    Public Const HH_DISPLAY_INDEX = &H2     ' select index tab and searches for a keyword
    Public Const HH_DISPLAY_SEARCH = &H3    ' select search tab and perform a search

    Public Const HH_HELP_CONTEXT = &HF      ' display mapped numeric value in dwData

    Public Const HH_CLOSE_ALL = &H12


    Public Type HH_FTS_QUERY                ' UDT for accessing the Search tab
      cbStruct          As Long             ' Sizeof structure in bytes.
      fUniCodeStrings   As Long             ' TRUE if all strings are unicode.
      pszSearchQuery    As String           ' String containing the search query.
      iProximity        As Long             ' Word proximity.
      fStemmedSearch    As Long             ' TRUE for StemmedSearch only.
      fTitleOnly        As Long             ' TRUE for Title search only.
      fExecute          As Long             ' TRUE to initiate the search.
      pszWindow         As String           ' Window to display in
    End Type

    Public Function HFile(ByVal i_HFile As Integer) As String
    '----- Set the string variable to include the application path of helpfile
      Select Case i_HFile
      Case 1
        HFile = ThisWorkbook.Path & "CHM-example.chm"
      Case 2
    '----- Place other Help file paths in successive case statements
        HFile = ThisWorkbook.Path & "CHM-example.chm"
      End Select
    End Function

    Public Sub ShowContents(ByVal intHelpFile As Integer)
       HHwinHwnd = HtmlHelp(hwnd, HFile(intHelpFile), HH_DISPLAY_TOC, 0)
    End Sub

    Public Sub ShowIndex(ByVal intHelpFile As Integer)
        HHwinHwnd = HtmlHelp(hwnd, HFile(intHelpFile), HH_DISPLAY_INDEX, 0)
    End Sub

    Public Sub ShowTopic(ByVal intHelpFile As Integer, strTopic As String)
        HHwinHwnd = HTMLHelpTopic(hwnd, HFile(intHelpFile), HH_DISPLAY_TOPIC, strTopic)
    End Sub

    Public Sub ShowTopicID(ByVal intHelpFile As Integer, IdTopic As Long)
      HHwinHwnd = HtmlHelp(hwnd, HFile(intHelpFile), HH_HELP_CONTEXT, IdTopic)
    End Sub

    Public Sub CloseHelp(ByVal hwnd As Long)
    Const WM_CLOSE = &H10
    If IsWindow(hwnd) Then
      SendMessage hwnd, WM_CLOSE, 0, 0
    End If
    End Sub
    '------------------------------------------------------------------------------
    '----- display the search tab
    '----- bug: start searching with a string dosn't work
    '------------------------------------------------------------------------------
    Public Sub ShowSearch(ByVal intHelpFile As Integer)
    Dim searchIt As HH_FTS_QUERY
      With searchIt
        .cbStruct = Len(searchIt)
        .fUniCodeStrings = 1&
        .pszSearchQuery = "foobar"
        .iProximity = 0&
        .fStemmedSearch = 0&
        .fTitleOnly = 1&
        .fExecute = 1&
        .pszWindow = ""
      End With
      Call HtmlHelpSearch(0&, HFile(intHelpFile), HH_DISPLAY_SEARCH, searchIt)
    End Sub

And some sample code (2) added:

    Sub AddUDFToCategory()
    '------------------------------------------------------------------------------
    ' insert after Description line [optional]:    Category:=2, _ => Date & Time
    '------------------------------------------------------------------------------
    ' If the UDF's are in an Addin (.xla) it's better to qualify the function name
    ' like this:
    ' Macro:=ThisWorkbook.Name & "!" & "DayName"
    '------------------------------------------------------------------------------
    ' see also Excel help for Application.MacroOptions
    '------------------------------------------------------------------------------
        application.MacroOptions _
            Macro:="TestMacro", _
            Description:="This function gives back the 'Hello world' message!", _
            Category:=2, _
            HelpFile:=ThisWorkbook.Path & "CHM-example.chm", _
            HelpContextID:=10000
        application.MacroOptions _
            Macro:="DayName", _
            Description:="A Function That Gives the Name of the Day", _
            Category:=2, _
            HelpFile:=ThisWorkbook.Path & "CHM-example.chm", _
            HelpContextID:=20000
    End Sub

    Function TestMacro()
    '----------------------------------------------------------------
    ' Display a message box with a help button linked to a help topic
    '----------------------------------------------------------------
        MsgBox "The 'Hello World' message for testing this function!.", _
        Buttons:=vbOKOnly + vbMsgBoxHelpButton, _
        HelpFile:=ThisWorkbook.Path & "CHM-example.chm", _
        Context:=20010
    End Function

    Function DayName(InputDate As Date)
    '---------------------------------------------
    '--- A Function That Gives the Name of the Day
    '--- http://www.fontstuff.com/vba/vbatut01.htm
    '---------------------------------------------
        Dim DayNumber As Integer
        DayNumber = Weekday(InputDate, vbSunday)
        Select Case DayNumber
            Case 1
                DayName = "Sunday"
            Case 2
                DayName = "Monday"
            Case 3
                DayName = "Tuesday"
            Case 4
                DayName = "Wednesday"
            Case 5
                DayName = "Thursday"
            Case 6
                DayName = "Friday"
            Case 7
                DayName = "Saturday"
        End Select
    End Function

For a working example go to Online Help and Visual Basic for Applications please. Search for VBA - Download and the download link Download Visual Basic for Applications example project (EXCEL).

1. download the ZIP file from the link above to a temp directory, right click the saved ZIP file first, click Properties and click Unblock
2. unzip to a temp directory.
3. as a test open CHM file first by double click
4. double click CHM_VBA_example.xls
5. first have a look to security warnings ( Excel) and set to Activate
6. follow the instructions from the Excel example worksheet.

For further information read Using the VBA Excel Example File too.