Writing macros with GUI for Microsoft Word like a PRO [Part 2, final]

Final word macro form view

Yes, this is a macro for MS Word!

Let's dive deeper into the topic of macros in Microsoft Word. We will add the user interface for our macro, which replaces two or more consecutive line breaks with a single one. Why would you ever need some kind of interface for a macro? Well, for example, you want to remove extra line breaks on all pages of the document, except for some specific ones. The interface would allow you to specify the page numbers that you want to skip during processing (or vice versa, only process specified pages). This is the functionality we will implement.

Open the familiar VBA window by clicking "Visual Basic" on the "Developer" panel. Add a new user form to our macros by right-clicking on "Normal" and selecting "Insert" -> "UserForm":

Word add UserForm

A new form with the name UserForm1 will appear. You can rename it, as well as change other properties of the form, on the "Properties" tab:

Edit UserForm in Word

I will name the form DocumentFixer. Let's add some controls to the form. We will create a frame with some settings and the "Enable" checkbox for our macro. When you click on the form, a Toolbox will appear, where you can select controls to be added:

Word UserForm toolbox

After placing a certain number of controls on the form, it looks like this:

Word macro UserForm

You can preview the form by clicking on it in the editor and pressing F5 or the "Run" button in the VBA editor interface, which can also run macros for debugging. I've added the following controls to the form:

  • The "Line breaks" frame, where all the settings for our macro are located.
  • The "Remove excessive line breaks" checkbox, which will enable or disable our macro. I called this element RemoveExcessiveLineBreaks.
  • Two radio buttons: "Include pages" (named ExcessiveLineBreaksIncludePages) and "Exclude pages" (named ExcessiveLineBreaksExcludePages) that specify which pages should be processed or excluded from processing, respectively. To bind these radio buttons together, set the same value for their GroupName property. I set it to ExcessiveLineBreaksPageOption.
  • The text field (named ExcessiveLineBreaksPageNumbers) with the "Comma-separated page numbers" label, where the user can enter comma-separated page numbers.
  • The "Run" button named RunMacros, which will run the selected macros (we only have a single one so far).

Of course, this is not flexible like HTML or WPF, but it’s quite possible to implement some kind of graphical user interface. Double-click on the "Run" button on the form, and this will automatically generate code for the button click event handler. It looks like this for me:

For now, let's leave this handler and write a function below it that will parse comma-separated page numbers.

In this function, we create a new pageNumbersCollection collection (essentially an indexed associative array), as well as a pageNumbers array. In line #4, we split the page numbers string from the ExcessiveLineBreaksPageNumbers textbox and put the result to the pageNumbers array (note the Me prefix: we refer to the current form). Line #5 sets up a handler to ignore all errors. Then we add all page numbers to the pageNumbersCollection collection in the loop. If there are duplicate page numbers, then the error will not occur, because we ignore them. Please note that when returning from a function or assigning a variable to any object created via New, you need to use the Set keyword.

Now, let's return to the RunMacros_Click handler and write code that will call our macro with the necessary parameters. I will write comments directly in the code, as it is quite long:

It remains to implement the RemoveExcessiveLineBreaksImpl function, which will take two parameters: a page numbers collection with numbers that we need to either skip or, on the contrary, process only them. The second parameter (the value of the ExcessiveLineBreaksIncludePages radio button) exactly tells what to do with these page numbers (True - process only them, False - exclude them from processing). The user can also leave the page numbers textbox empty, in this case the collection will be empty as well, and the macro will ignore this option.

Let's open the AllMacros module file by double-clicking on its name. I implemented it in the previous post. We'll write some auxiliary private functions. We will need to determine if a key exists in the collection to find out if the current page is in the list of pages specified by the user:

VBA collections are extremely truncated: keys can only be strings, that's why I didn't convert page numbers to integers when parsing them, and the HasKey function also takes the value key of String type. In this function, we try to query the key from the collection (line #3). If something goes wrong, an error will occur, that we process with the handler from line #2. If the error number is not zero (i.e., an error has occurred), then the key is not in the collection. We'll return this value from the HasKey function and clear the error number afterwards.

Now we’ll write a helper function that determines whether it's required to delete unnecessary line breaks on the current page:

Finally, the main function that will do the replacement work:

Please note that this function is not private (public by default), since it must be visible to our form. This function will not appear in the list of Word macros, because it accepts arguments. Only functions with no parameters are displayed in the list. The function is almost completely identical to the RemoveExcessiveEnters macro from the first part of the article, except that it takes a couple of arguments. Also, before calling RemoveNextEnters it checks whether it is necessary to remove line breaks by calling NeedToProcessCurrentPage. To avoid code duplication, we’ll fix our old macro RemoveExcessiveEnters so that it calls this function, too:

This way we saved the old macro and avoided code duplication: this macro will now call our new function with parameters for replacing extra line breaks on all pages (just like it did before). The last thing we need is a function that will appear in the list of Word macros and show our form. Everything is very simple here:

Now we can check the macro! In Word, click "Developer" -> "Macros" and double-click "FixDocument" in the list. The form will open, where you can set the parameters and click "Run", which will execute our macro!

There are several improvements that would be nice to add. Firstly, when running a macro on a large document, the Word screen will be continuously updating, which can make Word freeze and produce unpleasant visual effects. In addition, there is no way to track the progress of macro execution. Let's fix all these issues. I'll start with the option to disable screen updates during macro execution. I will add a checkbox with the name DisableScreenUpdates to the form, and refine the code of the "Run" button click handler the following way:

Now the Word screen will not be updated during the macro execution (if the user checks the checkbox). Good, now let's move on to displaying the progress of an operation. Moreover, let's make it possible to cancel the operation. We could start our macros on a huge document, having some options set incorrectly. In this case, it would be nice to cancel execution, adjust the settings, and restart the macros. Add a new form and name it MacroProgressForm. Throw a few controls on it: a label to display the current operation description (StepName), a progress bar for the current operation (ProgressBarLabel), a label to display the text progress (StepProgress), and a cancel button (CancelMacro). My form looks like this:

Word macro progress form

I implemented a progress bar using two labels, changing their styles and colors (one displays progress, it is located above the other, which is a frame). Let's switch to the form code window. You can right-click on the form in the editor and select "View code". Let's create three variables, make them private and thus accessible only to the form. The first one will contain the maximum progress value for the current operation, the second one - the current progress value, and the third one - the flag, whether the user canceled the operation:

Next, we write the cancel button handler:

Here we ask the user whether it is necessary to cancel all macro operations, and if the user agrees, set the progressCancelled value to True. Next, we write a number of functions that will control the progress of execution. Our form can run several macros in a row (although we have implemented only one so far). Let's consider this and write several public functions that each macro will be able to use:

The flow will be as follows:

  1. The macro receives a progress form instance, the macro can call its public functions.
  2. The macro calls SetStep, passing a text description of what it does. The progress form will display this description and hide the progress bar and the label (as progress is not known for now).
  3. The macro calls SetMaxProgres, indicating the maximum progress value for its operation. Alternatively, the macro can call SetMaxProgressByPageCount, then the maximum progress value will be set to the total number of pages in the document. Our macro to remove extra line breaks will do exactly that. We don't know in advance how many spots with consecutive line breaks there are in the document. This call will display the progress bar and the label, as the form now knows the maximum progress value.
  4. The macro calls IncreaseProgress or SetSelectionPageNumber in order to increase the progress. The form will automatically update and draw everything.
  5. The macro can either check the return values ​​from these functions, or regularly call CheckCancel to determine if the operation has been canceled by the user.

We need one last function:

It resets the cancel flag, and this function will be called before any macro execution by the first form we've created.

Now let's refine our RemoveExcessiveLineBreaksImpl function to make it work with the progress and cancellation form. We'll add a corresponding parameter to this function. I'll write the full code and comment the changes:

There are not so many changes, and now our macro is ready to work with the new interface. Don't forget to refine the old RemoveExcessiveEnters function:

Finally, we change the DocumentFixer form code so that it transfers the prepared progress form to the macros it invokes. We need to display that form, too. There are very few changes, it makes no sense to paste the entire code. After the ActiveDocument.Activate line we add the following:

When calling the macro, we now pass the progress form to it:

In the end, right after the Finish label, add the following line to hide the progress form:

That's all, folks! Now our high-tech macro can display its operation progress, and we can cancel its execution at any time. At the same time, we preserved the original old macro RemoveExcessiveEnters, which was written in the first part of the article. Here is how the progress display form looks like during execution:

Word macro progress form in action

The numbers 60/893 below is the amount of the page on which the last change was made, and the total number of pages in the document before the macro started. And here is the cancellation dialog:

Word macro cancel request

We can even further improve our macro infrastructure. We can add any number of macros with necessary settings to the DocumentFixer form, and all of them will be able to work with the progress display and operation cancel form. We can also, for example, improve the undo history of macro operations. Currently Word logs every smallest action performed by a macro, but we can make some of these actions combine into named groups with meaningful names, and the Word undo menu will not have such a mess of a lot of obscure operations.

But let's come to the conclusion. If someone needs an improvement from the ones listed above, perhaps I will make a separate post on that subject. As a bonus, I added a picture to the form, and a screenshot of this form is at the very beginning of this post. Moreover, I added some code so when you reset the RemoveExcessiveLineBreaks checkbox, all controls related to our macro are disabled (and enabled again when the checkbox is set). If you're interested, you can download the full source code for macros and forms and import them to your Word.

Leave a Reply

Your email address will not be published. Required fields are marked *