How to Create Help Files for MS Excel (VBA) Application in Dr.ExplainDennis Crane
This article is devoted to creation of help file in CHM format and its further integration into the Microsoft Excel workbook. From the article you will know:
· How to create and adjust a project Dr.Explain program;
· How to work with a help file structure;
· How to create an annotated image of Microsoft Excel workbook;
· How to save a project;
· How to export a project to CHM format;
· How to create a help file call item in Microsoft Excel and how to set a corresponding macros.
To download Dr.Explain installation program, click «Free download» button on the product web-site http://www.drexplain.com. After finishing, run a downloaded program. To complete an installation process, follow the setup wizard instructions (Picture 1).
You can use a trial version; it has the same functional as a full version; however, all images in exported file will have watermarks.
Picture 1. Dr.Explain setup wizard
Creating and saving a new project
To create a new help file project select «Create a new project» radio button in the «Dr.Explain projects» window (Picture 2) and then press «OK». Also you can use «Create» item in the «File» menu.
Picture 2. «Dr.Explain projects» window
To change CHM format export parameters use «Options -> Project settings -> Export to CHM».
Creating help file topics
To make help file structure handy, you have to create separate topics for describing various Microsoft Excel workbook elements.
New topic can be created either by clicking «Options -> Add topic» in the main menu, or by using «Add -> Add topic» pop-up menu item, which can be activated by right-click on any project tree item (Picture 3). To make an exhaustive workbook description you have to create topics for every its element.
Picture 3. «Dr.Explain» project tree
To change order of topics and their names use «Rename», «Move Up» and «Move Down» pop-up menu items. To fill out the particular topic, select it in the project tree and then enter its content in the editor window. There is a standard text editing toolkit as well as some tools for working with images, videos, tables and variables (variables usage allows you to replace recurrent data with variable; in this case if you change variable value, corresponding text will be changed in the whole document).
For increasing a convenience of help file searching you have to create a list of keywords. You can add and delete keywords using buttons located on the panel which can be shown up by pressing the «Keywords» button (Picture 4). To set properties of keywords to topic link, select a corresponding topic in the tree project, and enter labels for the keywords you want to associate the topic with.
Picture 4. Keywords
To make an illustrative description of Microsoft Excel workbook as well as its separate components use «Capture an object» function (Picture 5) – «Snapshot» button.
Picture 5. «Capture an object» window
In capture window check «Accessible-object» or «Win32-window» boxes, then go to Microsoft Excel window and select describable object by one of mentioned in the window ways.
After that a new topic containing annotated interface image will be created in the editor window (Picture 6). Using editor you can add and delete single elements annotations and change order of various items.
Picture 6. Result of object capturing
Previewing and saving a new project
Before you start document export, use the preview function. For this click «CHM preview» button on the top of the current project window. In the preview mode make sure all pages and topics are shown properly. Go back to editing mode and make desired changes if necessary.
If you want to resume your work with the project later it is recommended to save the project. For this use the «File -> Save as» main menu item. After you choose it, the standard save file window will come up where you can enter a file name or point its location.
Exporting project to CHM file
After you fill all topics out you should export your project. For this click «Options -> Export to CHM» in the main menu. If you would like to validate the project before exporting process check «Run the validation tool before exporting». If you want generated file to be open immediately after the exporting process, tick «Open CHM file» check box. To overwrite an existing file with no user confirmation tick «Overwrite silently» check box. After you set all desired parameters click «Start export» button. You will see «Export to CHM file» window where exporting process report will be shown.
Picture 7. «Export to CHM file» window
Integrating CHM file into Microsoft Excel workbook
To open a help file from Microsoft Excel workbook you should create an object which calls a corresponding CHM file. Such object can be an image, a figure or a text box.
To create the object:
- Open a desired workbook in Microsoft Excel;
- Open a sheet where you want to place a help file call element;
- Select a cell or a range of cells where you want to locate this element;
- Add the element. To add an image or a figure, use the corresponding buttons on the «Illustrations» panel of the «Insert» tab on the main toolbar. To add a text box, use a corresponding button on the on the «Text» panel of the «Insert» tab on the main toolbar;
- Change element location if necessary. Just drag it by mouse to the desired cell;
- After placing the element you can create its title. For example «Help»;
- Open a context menu by right click on the added object (Picture 8);
- Select «Assign macro» menu item. Click «Edit» in the appeared window, after that Microsoft Visual Basic editor window will come up (Picture 9);
Picture 8. Context object menu
Picture 9. Element code in Microsoft Visual Basic editor
- You can see your element’s name in the following string: "Sub Rectangle1_Click()" (in our example this is "Rectangle1"). Enter the following string below - Call Shell("explorer.exe " & ActiveWorkbook.Path & "\help.chm", vbNormalFocus), where help.chm is the help filename in CHM format. After all changes element code should has the following look:
Sub Rectangle1_Click() Call Shell("explorer.exe " & ActiveWorkbook.Path & "\help.chm", vbNormalFocus) End Sub
- Close the Microsoft Visual Basic editor window;
- Make sure your control element works properly. Just open the form and click «Help» button. Corresponding help file created in Dr.Explain should be open;
- Repeat described procedure for each sheet where you want to insert a help file call element.
PLEASE NOTE! To make a help file call element work properly, you have to put the CHM file and the Microsoft Excel workbook in the same folder. If it is necessary to put the help file separately from the workbook file; you have to enter the following string in the control element code: Shell "cmd /cstart D:\work\techwrite\help.chm", where D:\work\techwrite\help.chm is the full path to the help file in CHM format. The code will look like this:
Sub Rectangle1_Click() Shell "cmd /cstart D:\work\techwrite\help.chm" End Sub
In this case, every time you change the help file location you have to change the path in the control element code to the actual one.
Be careful in entering control element codes; strictly follow the syntax shown in the examples. This is necessary condition for proper work of control elements.
You can use a hyperlink bound to an object to open a help file. To create a hyperlink, perform the following steps:
- Right-click on the added object to open a context menu (Picture 8);
- Click on «Hyperlink» menu item;
- Enter a file location in the «Insert hyperlink» dialog;
- If you would like to set a pop-up hint, use a «ScreenTip» button;
- Click «OK» when finished.
Please note the file path you enter for the hyperlink is a relational one. So every time you change the workbook and help file locations relatively to each other, you have to update a help file path.