Beginning with VBA: Recording a Simple Macro in Word

Programs written using VBA are referred to as macros. This appears to be a contraction of the term “macroinstruction”, which begins to make sense. Macro by itself comes from the Greek language and means “large”, and why that would refer to a computer program is not immediately obvious. But as Wikipedia explains:

Macros are used to make a sequence of computing instructions available to the programmer as a single program statement, making the programming task less tedious and less error-prone.[1][2] (Thus, they are called “macros” because a “big” block of code can be expanded from a “small” sequence of characters.)

So, essentially what VBA is trying to do is to let you combine a series of instructions into a single macro command. To a real programmer this is a type of subroutine.

Macro Recording

For many people their first exposure to creating a macro is through macro recording. This is done by turning on a recording program, doing a series of operations, and then turning off the recorder when you are done. Give it a name and save it and you can use this over and over. In Office 2016 only Word and Excel have a macro recorder. If you want to create a macro for other applications you will have to actually write code.

As an example, in a word processing application I have used this to record the closing of a letter. This macro would be along the lines of “hit Enter a couple of times, then tab in 7 times and type ‘Sincerely’ followed by a comma, then hit Enter 5 more times to create space for a signature, then tab in 7 times and type my name, hit Enter again for a new line, hit tab 7 times again, type my address” and so on. The idea is that I would probably close all of my letters doing the exact same thing, and why do it from scratch each time? My operating principle is always:

Anything you will do repeatedly should be automated!

So, what is going on when you do this? We can see it in action pretty easily. Using Word 2016, start a blank document, and in the lower left is an icon for starting a Macro. I am not sure what the icon “means” exactly, but if you mouse over the icons in the lower left you will find the one that says “No macros are currently recording. Click to begin recording a new macro.”

Button to turn on macro recording

Button to turn on macro recording

 

 

 

When you turn it on, it will immediately open a Dialog window where you need to give it certain information:

Macro dialog window

Macro dialog window

 

 

 

 

 

 

 

The first thing you need to specify is the name. There are some restrictions on naming, such as not using spaces. I use CamelCase for my names, which means that the first letter of each word is capitalized but there are no spaces between the words. This seems to work pretty well, and is the way methods in VBA are named. In this case I called it ClosingSignature.

For the moment, skip over the “Assign macro to” piece, we’ll come back to that in a moment.

“Store macro in” is telling you that if you ever want to use this macro again, it needs to be stored in an appropriate Template. This is something I discuss in great length in my LibreOffice series, but it is a general principle of all Office software suites. If I had gone to the trouble of create a Template specifically for writing letters, I would assign this macro to that Template. But the default choice is Normal.dotm, which is the macro file associated with default Template for Word 2016. So any document that is started the usual way without choosing a Template will have this macro available. But note that macros can also be copied from one Template to another, so I could make this macro available in multiple Templates. If you place a macro in another Template, use the name of that Template with a “*.dotm” extension to save the macro.

Description is where you can make a note for yourself or others to read at any future time when you are wondering what this macro is and what it does. sometimes the Macro name can be pretty cryptic, so put in something a little more descriptive. Here I entered “Inserts the usual closing and signature space at the end of a letter.”

Now, let’s get back to the “trigger”. You can assign your macro to a button on the Quick Access Toolbar or to a keyboard shortcut. If you choose the Keyboard option, you get this dialog window:

Assign Macro to Keyboard shortcut

Assign Macro to Keyboard shortcut

 

 

 

 

 

 

 

 

The field Current Keys is blank right now because we haven’t assigned anything. So go to the “Press new shortcut key:” and try something. you may find that your first few choices are already in use. When you type a keyboard shortcut into this field, another dialog appears just under the Current Keys field which says “Currently assigned to:” For example, if you had tried Control+F11 as your shortcut it would display LockFields. So you know this shortcut is already assigned to a different subroutine, and you should not use it. So try another one. For me, when I tried Alt+C it said it was unassigned, so I was safe to use it. (I chose C for Closing. Obviously, Control+C is already in use for Copy). Then I click the Assign button, and now I see Alt+C appear in the Current Keys field because it is now the current keyboard shortcut for this macro. If I now close this dialog, I can go to my Developer tab in the ribbon bar, and there is a Macros button on the left. I click that, and now I see that ClosingSignature is listed as a macro.

Note: In Excel all keyboard shortcuts for Macros have to begin with Control.

When I close it, I can look at my mouse cursor and note that it now has a little icon of an audio cassette tape attached to the arrow, and this tells me that it is recording. So I start recording my macro, and when I am done I can click on Stop Recording, which is right next to the Macros button on the Ribbon bar in the Developer tab.

Now, what did this record? I can see this easily if I go back to my Macros button, then select the ClosingSignature macro, and click Edit on the right. My code looks like this:

Sub ClosingSignature()
'
' ClosingSignature Macro
' Inserts the usual closing and signature space at the end of a letter.
'
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & _
vbTab & "Sincerely,"
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeParagraph
Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & _
vbTab & "Kevin O'Brien"
Selection.TypeParagraph
Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & _
vbTab & "Michigan, USA"
End Sub

So, this starts with Sub, to indicate that it is a subroutine, then the name of the subroutine (ClosingSignature). This is followed by a little metadata, like my Description of the macro. Note that those four lines are “commented out” by putting an apostrophe at the beginning of the line. This means VBA will not attempt to execute anything on those four lines. But then it gets into actual code:

Selection.TypeParagraph is the code for hitting the Enter key. In Office programs hitting the Enter key means to start a new paragraph.

Selection.TypeText Text:=vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & _
vbTab & “Sincerely,” is the code for hitting the tab key 7 times and then typing “Sincerely” followed by a comma.

And so on for all of the code. It finally concludes with End Sub which says this subroutine is not done.

The point is that what we recorded is actually stored as code, and we could have gotten the same results by writing the code directly. Since we have a Macro Recorder it is a lot easier to do it that way, but if we wanted to modify the macro we could do it by editing the code, which might be easier than starting over with the recorder. And for PowerPoint or Access, where there is no Macro recorder, the only way to create Macros is by writing code. So it we can start to learn the structure of VBA by recording some actions and seeing how they come out as code.

Running the Macro

Running the macro is pretty easy, and you have two options at this point. You can either use the keyboard shortcut Alt+C, or you can go to the Developer Tab, click the Macros button, select the ClosingSignature macro, and click Run. Either one of these will run the macro and insert your standard closing into the letter. But note that it will do this at exactly the location of your insertion point (which is the name for the blinking cursor in the document). So if you managed to run it in the wrong location, don’t panic, just use your Undo (Ctrl+Z) to remove it one line at a time, or highlight and delete. Be careful about when you trigger it. For instance, I recorded the macro by beginning with two line feeds (i.e. Enter). That means I am going to run the macro as soon as I have typed the last period in the body of the letter, and not hit Enter first.

Security Note

Macros are programs, though usually fairly small. And they can be a vehicle for malware. So be careful if you start looking at macros from other sources when you start experimenting. It is conceivable that a macro could embed a command to delete or encrypt all of your files, for instance. So for now stick to macros you have created.

A good overview of VBA security can be found at https://technet.microsoft.com/en-us/library/ee857085(v=office.15).aspx. Though it refers specifically to Office 2013, it is just as useful for Office 2016.

 Save as PDF