Problem with EXCEL MacroOptions method to assign help file to UDF

AliM67 0
Viewed 49

In Excel, it is possible to assign help file (e.g. chm file) to a user-defined function using VBA code, Application.MacroOptions method. So, I faced a strange issue with it.

Suppose that we wrote the UDF named as "myUDF()".

Function myUDF()
myUDF = "it is test"
End Function

When you open Function Wizard for it, and click on "Help on this function", Excel get error : "No help available."

So, we run below code to assign help file to it:

Sub SetHelpFile()
        Application.MacroOptions _
            Macro:="'" & ThisWorkbook.Name & "'!" & "myUDF", _
            HelpFile:=ThisWorkbook.Path & "\CHM-example.chm", _
            HelpContextID:=10010
End Sub

It is necessary that chm file contains Topic ID. I used chm file from this sample. After running the code, chm file is assigned to UDF. But there is a problem. If you click on "Help on this function", it can't show specific page according mentioned topic ID (for this sample: 10010)

It is more complicate If you run code SetHelpFile again. After that If you click on "Help on this function", it show correct page.

I don't know what's the problem. Is it a bug?

EDIT: To reproduce the issue, you can get this file and test it!

chm helpfile user-defined-functions vba excel
1 Answers

Your code to assign the help file to the User Defined Function (UDF) is correct (tested on my Windows 10, Excel 2019 and 2007). However, I recommend RegisterUDF as a name for the sub. This fits better with what is happening.

I assume you changed the TopicID in the VBA Code Editor during your test and clicked on the Help on this function link immediately after closing the Editor.

It is necessary to re-register the UDF when the TOPIC ID changes. You know, run the code from Developer Tab > Macros > select the RegisterUDF macro > Run. This step is not required later after the UDF function development has been completed. During development it may be useful to delete the file hh.dat (info see below).

Please note that you will get another help window every time you call Help on this function. This can exactly cover the other help window unnoticed.

Additional information:

Edit (2022-09-17):

To fix the behaviour, the registered UDF must be known to the Excel file. If the TopicID is set only once and the Excel file is still open, Excel will have an undefined state of the specified TopicID.

  • Open your sample *.xlsm file
  • Click the Set help file to UDF button once
  • Select the cell with your function and click Shift+F3
  • Click the Help on this function link
  • You'll see the welcome help page
  • Click the Set help file to UDF button a second time
  • Select the cell with your function and click Shift+F3
  • Click the Help on this function link
  • You'll see the content of TopicID=10010 help page
  • Save your Excel file
  • Open again and test ...

Please note the hint from above : It is necessary to re-register the UDF when the TOPIC ID changes.

help-info.de0
Related