How to use VBA to repurpose a built-in command in Word and Excel

The article, How to use VBA to update fields in a Word document shows you three ways to update fields: printing the article and two VBA procedures. The two VBA procedures work well together, but you must close or open the document to trigger the procedures. In that article, I promised to show you a way to repurpose built-in commands to update the fields. In this article, I’ll show you how to repurpose the Save command to update all fields in the document. That’s a very specific task, but you can repurpose most any built-in command in the same way. We’ll be using Word in the examples, but you can also apply the same code to an Excel file.

SEE: 83 Excel tips every user should master (TechRepublic)

The demonstration file uses Word fields. If you don’t know how to use and enter fields, you might want to read 3 ways to enter fields in Microsoft Word. However, the fields are provided in the demonstration files, so you don’t need to know how to use them to complete the example.   

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. VBA procedures aren’t supported in the web versions. For your convenience, you can download the demonstration .docm, .doc and .bas files.

How to add the VBA procedure to Word

If you’ve created VBA procedures before, you know that you work in the Visual Basic Editor . Almost all of Word’s built-in commands can be repurposed to include extra tasks using VBA, but the route is a bit different.

We’ll repurpose the built-in Save command by adding the necessary code to update all fields when you click Save. You might think the Save As command more appropriate, but you can’t alter it. It’s part of the Backstage arrangement now, and you can’t repurpose those commands. However, that doesn’t mean you can’t get close with Save. 

If you’re using a ribbon version, be sure to save the workbook as a macro-enabled file before you continue. If you’re using a menu version, you can skip this step.

Now, let’s add the procedure shown in Listing A (below):

1. Press Alt + F8.
2. In the Macros In dropdown list, choose Word Commands, as shown in Figure A.
3. Thumb down until you find FileSave and select it. Or enter FileSave. (Commands continue to begin with the menu name associated with the command in the earlier menu version.)
4. Return to the Macros in dropdown list and select the document or template where you want to store this macro. In Figure B, you can see that I selected the document currently open (the demonstration .docm file).
5. Click Create to open the VBE with the appropriate stub and command, as shown in Figure C.

Figure A

Figure B

Figure C

The procedure contains only one line, the Save method. To repurpose the command, add the appropriate code. Listing A includes the VBA command necessary to update all fields in the document. The demonstration Word .docm file contains this repurposed Save command (in its NewMacros module).

Listing A

Sub FileSave()

‘ FileSave Macro

‘ Saves the active document or template

‘Update all fields when closing document; repurpose Save command.

    ThisDocument.Fields.Update

    ActiveDocument.Save

End Sub

In this case, you can enter the ThisDocument.Fields.Update line to the Save procedure you opened earlier if you’re working on your own. If you enter the code manually, don’t paste that line from this web page. Instead, type if from the keyboard or copy the line into a text editor and then paste that code into the Save procedure. Doing so will remove any phantom web characters that might otherwise cause errors.

How to use the VBA procedure in Word

As you can see in Figure D, the demonstration file comprises two pages and each page has two Word fields, {PAGE} and {NUMPAGES}. If you’re working with the demonstration .docm, you can see the underlying fields by pressing Alt + F9. Press again to hide the codes and show the results.

Figure D

To see how this all works together, delete the hard page break between the two pages. To delete the hard page break, position the cursor at the beginning of page 2, before the word This. Then, press Backspace. Doing so will remove page two and reposition the text and fields from page 2. As I mentioned, the fields in the second line of text do not update, as shown in Figure E. This is typical of Word fields; most don’t update automatically. (The two fields will update automatically only in the header or footer.) 

Figure E

With both lines on page one, click the Save icon at the top of the screen or on the Quick Access Toolbar. Doing so executes the Save command, which now includes the update field line. Consequently, clicking Save updates the fields, as shown in Figure F.

Figure F

The repurposed Save command will update all fields, and that might not be what you want. In addition, this doesn’t include the Save As command, Close, or the X (Close) icon. The demonstration files include a repurposed FileClose procedure so you can see how it works, but it’s unlikely you’ll want to rely on it. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see