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

You’ll be able to automate particular duties by repurposing Phrase’s built-in instructions. It requires a little bit of VBA, nevertheless it’s tremendous straightforward. This is how.

Microsoft Word icon on screen

Picture: PixelMe/Shutterstock

The article, How one can use VBA to replace fields in a Phrase doc reveals you 3 ways to replace fields: printing the article and two VBA procedures. The 2 VBA procedures work properly collectively, however you will need to shut or open the doc to set off the procedures. In that article, I promised to point out you a technique to repurpose built-in instructions to replace the fields. On this article, I am going to present you the way to repurpose the Save command to replace all fields within the doc. That is a really particular job, however you’ll be able to repurpose most any built-in command in the identical approach. We’ll be utilizing Phrase within the examples, however you may as well apply the identical code to an Excel file.

SEE: 83 Excel ideas each consumer ought to grasp (TechRepublic)

The demonstration file makes use of Phrase fields. If you do not know the way to use and enter fields, you would possibly need to learn 3 methods to enter fields in Microsoft Phrase. Nevertheless, the fields are supplied within the demonstration information, so that you need not know the way to use them to finish the instance.   

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should use earlier variations. VBA procedures aren’t supported within the internet variations. On your comfort, you’ll be able to obtain the demonstration .docm, .doc and .bas information.

How one can add the VBA process to Phrase

When you’ve created VBA procedures earlier than, that you simply work within the Visible Primary Editor . Nearly all of Phrase’s built-in instructions will be repurposed to incorporate further duties utilizing VBA, however the route is a bit completely different.

We’ll repurpose the built-in Save command by including the required code to replace all fields if you click on Save. You would possibly suppose the Save As command extra applicable, however you’ll be able to’t alter it. It is a part of the Backstage association now, and you may’t repurpose these instructions. Nevertheless, that does not imply you’ll be able to’t get shut with Save. 

When you’re utilizing a ribbon model, make sure you save the workbook as a macro-enabled file earlier than you proceed. When you’re utilizing a menu model, you’ll be able to skip this step.

Now, let’s add the process proven in Itemizing A (under):

  1. Press Alt + F8.
  2. Within the Macros In dropdown record, select Phrase Instructions, as proven in Determine A.
  3. Thumb down till you discover FileSave and choose it. Or enter FileSave. (Instructions proceed to start with the menu identify related to the command within the earlier menu model.)
  4. Return to the Macros in dropdown record and choose the doc or template the place you need to retailer this macro. In Determine B, you’ll be able to see that I chosen the doc at the moment open (the demonstration .docm file).
  5. Click on Create to open the VBE with the suitable stub and command, as proven in Determine C.

Determine A


Determine B


  Choose the file the place you plan to avoid wasting the process.

Determine C


  After Phrase opens the VBE, you are prepared so as to add code to the Save command.

The process incorporates just one line, the Save methodology. To repurpose the command, add the suitable code. Itemizing A consists of the VBA command essential to replace all fields within the doc. The demonstration Phrase .docm file incorporates this repurposed Save command (in its NewMacros module).

Itemizing A

Sub FileSave()

‘ FileSave Macro

‘ Saves the lively doc or template

‘Replace all fields when closing doc; repurpose Save command.



Finish Sub

On this case, you’ll be able to enter the ThisDocument.Fields.Replace line to the Save process you opened earlier should you’re working by yourself. When you enter the code manually, do not paste that line from this internet web page. As an alternative, kind if from the keyboard or copy the road right into a textual content editor after which paste that code into the Save process. Doing so will take away any phantom internet characters that may in any other case trigger errors.

How one can use the VBA process in Phrase

As you’ll be able to see in Determine D, the demonstration file contains two pages and every web page has two Phrase fields, {PAGE} and {NUMPAGES}. When you’re working with the demonstration .docm, you’ll be able to see the underlying fields by urgent Alt + F9. Press once more to cover the codes and present the outcomes.

Determine D


    Each pages use Phrase fields to show the web page quantity and the variety of pages within the doc.

To see how this all works collectively, delete the arduous web page break between the 2 pages. To delete the arduous web page break, place the cursor at the start of web page 2, earlier than the phrase This. Then, press Backspace. Doing so will take away web page two and reposition the textual content and fields from web page 2. As I discussed, the fields within the second line of textual content do not replace, as proven in Determine E. That is typical of Phrase fields; most do not replace routinely. (The 2 fields will replace routinely solely within the header or footer.) 

Determine E


  The fields do not replace routinely.

With each strains on web page one, click on the Save icon on the prime of the display screen or on the Fast Entry Toolbar. Doing so executes the Save command, which now consists of the replace discipline line. Consequently, clicking Save updates the fields, as proven in Determine F.

Determine F


  Clicking Save now updates all fields within the doc.

The repurposed Save command will replace all fields, and that may not be what you need. As well as, this does not embody the Save As command, Shut, or the X (Shut) icon. The demonstration information embody a repurposed FileClose process so you’ll be able to see the way it works, nevertheless it’s unlikely you will need to depend on it. 

Additionally see

Recent Articles


Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox