Learn VBA Macro Coding with Word 2007

Part 1 of an About Visual Basic Tutorial

Computer class - Tetra Images - GettyImages-75650646
Tetra Images - GettyImages-75650646

The goal of this course is to help people who have never written a program before learn to write one. There's no reason why office workers, homemakers, professional engineers and pizza delivery persons shouldn't be able to take advantage of their own hand crafted custom computer programs to work faster and smarter. It shouldn't take a 'professional programmer' (whatever that is) to do the job. You know what needs to be done better than anyone else. You can do it yourself!

(And I say this as someone who has spent many years writing programs for other people ... 'professionally'.)

With that said, this is not a course in how to use a computer.

This course assumes that you know how to use popular software and in particular, that you have Microsoft Word 2007 installed on your computer. You should know basic computer skills like how to create file folders (that is, directories) and how to move and copy files. But if you've always wondered what a computer program actually was, that's OK. We'll show you.

Microsoft Office isn't cheap. But you can get more value from that expensive software you already have installed. That's a big reason we use Visual Basic for Applications, or VBA, along with Microsoft Office. There are millions who have it and a handful (maybe no one) who uses everything it can do.

Before we go any further, however, I need to explain one more thing about VBA. In February 2002, Microsoft made a 300 billion dollar bet on a totally new technology base for their entire company. They called it .NET. Since then, Microsoft has been moving their entire technology base into VB.NET. VBA is the very last programming tool that still uses VB6, the tried and true technology that was used before VB.NET. (You'll see the phrase "COM based" to describe this VB6 level technology.)


Microsoft has created a way to write VB.NET programs for Office 2007. It's called Visual Studio Tools for Office (VSTO). The problem with VSTO is that you have to buy and learn to use Visual Studio Professional. Excel itself is still COM based too and .NET programs have to work with Excel through an interface (called the PIA, Primary Interop Assembly).

So ... until Microsoft gets their act together and gives you a way to write programs that will work with Word and doesn't make you join the IT department, VBA macros are still the way to go.

Another reason we use VBA is that it really is a 'fully baked' (not half baked) software development environment that has been used for years by programmers to create some of the most sophisticated systems in existence. It doesn't matter how high your programming sights are set. Visual Basic has the power to take you there.

What is a macro?

You may have used desktop applications that support what is called a macro language before. Macro's are traditionally just scripts of keyboard actions grouped together with one name so you can execute them all at once. If you always start the day by opening your "MyDiary" document, entering today's date, and typing the words, "Dear Diary," -- Why not let your computer do that for you? To be consistent with other software, Microsoft calls VBA a macro language too. But it's not. It's much more.

Many desktop applications include a software tool that will let you record a "keystroke" macro. In Microsoft applications, this tool is called the Macro Recorder, but the result is not a traditional keystroke macro. It's a VBA program and the difference is that it does not simply replay the keystrokes. A VBA program gives you the same end result if possible, but you can also write sophisticated systems in VBA that leave simple keyboard macros in the dust. For example, you can use Excel functions in Word using VBA. And you can integrate VBA with other systems like databases, the web, or other software applications.

Although the VBA Macro Recorder is very useful for simply creating simple keyboard macros, programmers have discovered that it's even more useful to give them a running start in more sophisticated programs. That's what we're going to do.

Start Microsoft Word 2007 with a blank document and get ready to write a program.

The Developer tab in Word

One of the first things that you have to do to write Visual Basic program in Word 2007 is find Visual Basic! The default in Word 2007 is to not display the ribbon that is used. To add the Developer tab, first click the Office button (the logo in the upper left corner) and then click Word Options. Click Show Developer tab in the Ribbon and then click OK.

When you click the Developer tab, you have a whole new set of tools used to write VBA programs. We're going to use the VBA Macro Recorder to create your first program. (If the ribbon with all your tools keeps disappearing, you might want to right-click the ribbon and make sure Minimize the Ribbon is not checked.)

Click Record Macro. Name your macro: AboutVB1 by typing that name in the Macro Name textbox. Select your current document as the location to store your macro and click OK. See the example below.

(Note: If you pick All Documents (Normal.dotm) from the drop down menu, this test VBA program will, in effect, become a part of Word itself because it will then become available for every document you create in Word. If you only want to use a VBA macro in a specific document, or if you want to be able to send it to someone else, it's a better idea to save the macro as part of the document. Normal.dotm is the default so you must change it.)

With the Macro Recorder turned on, type the text, "Hello World." into your Word document. (The mouse pointer will change into a miniature picture of a tape cartridge to show that keystrokes are being recorded.)

(Note: Hello World is almost required for a "First Program" because the very first programming manual for the early computer language "C" used it. It's been a tradition ever since.)

Click Stop Recording. Close Word and save the document using the name: AboutVB1.docm. You have to select a Word Macro-Enabled Document from the Save as Type dropdown.

That's it! You have now written a Word VBA program. Let's see what it looks like!

Understanding what a VBA program is

If you have closed Word, open it again and select the AboutVB1.docm file that you saved in the previous lesson. If everything was done correctly, you should see a banner at the top of your document window with a security warning.

VBA and Security

VBA is a real programming language. That means that VBA can do just about anything you need it to do. And that, in turn, means that if you receive a Word document with an embedded macro from some 'bad guy' that macro can do just about anything too. So Microsoft's warning is to be taken seriously. On the other hand, you wrote this macro and all it does is type "Hello World" so there's no risk here. Click the button to enable macros.

To see what the Macro Recorder has created (as well as to do most other things that involve VBA), you need to start the Visual Basic Editor. There's an icon to do that at the left side of the Developer ribbon.

First, notice the left hand window. This is called the Project Explorer and it groups together the high level objects (we'll talk more about them) that are part of your Visual Basic project.

When the Macro Recorder was started, you had a choice of the Normal template or the current document as a location for your macro. If you selected Normal, then the NewMacros module will be part of the Normal branch of the Project Explorer display. (You were supposed to select the current document. If you did select Normal, delete the document and repeat the previous instructions.) Select NewMacros under Modules in your current project. If there still isn't any code window displayed, click Code under the View menu.

The Word document as a VBA container

Every Visual Basic program must be in some kind of file 'container'. In the case of Word 2007 VBA macros, that container is a ('.docm') Word document. Word VBA programs can't run without Word and you can't create standalone ('.exe') Visual Basic programs like you can with Visual Basic 6 or Visual Basic .NET. But that still leaves a whole world of things you can do.

Your first program is certainly short and sweet, but it will serve to introduce the major features of VBA and the Visual Basic Editor.

The program source will normally consist of a series of subroutines. When you graduate to more advanced programming, you'll discover that other things can be part of the program besides subroutines.

This particular subroutine is named AboutVB1. The subroutine header must be paired with an End Sub at the bottom. The parenthesis can hold a parameter list consisting of values being passed to the subroutine. Nothing is being passed here, but they have to be there in the Sub statement anyway. Later, when we run the macro, we will look for the name AboutVB1.

There is only one actual program statement in the subroutine:

Selection.TypeText Text:="Hello World!"

Objects, methods and properties

This statement contains the big three:

  • an object
  • a method
  • a property

The statement actually adds the text "Hello World." to the contents of the current document.

The next task is to run our program a few times. Just like buying a car, it's a good idea to drive it around for a while until it feels a little bit comfortable. We do that next.

Programs and documents

We have our glorious and complicated system ... consisting of one program statement ... but now we want to run it. Here's what that's all about.

There's one concept to be learned here that is very important and it often really confuses first timers: the difference between the program and the document. This concept is foundational.

VBA programs have to be contained in a host file. In Word, the host is the document. In our example, that's AboutVB1.docm. The program is actually saved inside the document.

For example, if this was Excel, we would be talking about the program and the spreadsheet. In Access, the program and the database. Even in standalone Visual Basic Windows application, we would have a program and a form.

(Note: There is a trend in programming to refer to all high level containers as a "document". This is specifically the case when XML ... another up and coming technology ... is being used. Don't let it confuse you. Although it's a slight inaccuracy, you can think of "documents" as being roughly the same as "files".)

There are ... ummmmm .... about three main ways to run your VBA macro.

  1. You can run it from the Word Document.
    (Note: Two subcategories are to select Macros from the Tools menu or just press Alt-F8. If you have assigned the macro to a Toolbar or Keyboard shortcut, that's one more way.))
  2. You can run it from the Editor using the Run icon or Run menu.
  3. You can single-step through the program in debug mode.

You should try every one of these methods just to become comfortable with the Word/VBA interface. When you finish, you will have a whole document filled with repeats of "Hello World!"

Running the program from Word is fairly easy to do. Just select the macro after clicking the Macro icon under the View tab.

To run it from the Editor, first open the Visual Basic editor and then either click the Run icon or select Run from the menu. Here's where the difference between the Document and the Program might become confusing to some. If you have the document minimized or perhaps have your windows arranged so the editor is covering it, you can click the Run icon over and over and nothing seems to happen. But the program is running! Switch to the document again and see.

Single stepping through the program is probably the most useful problem solving technique. This is also done from the Visual Basic editor. To try this out, press F8 or select Step Into from the Debug menu. The first statement in the program, the Sub statement, is highlighted. Pressing F8 executes the program statements one at a time until the program ends. You can see exactly when the text is added to the document this way.

There are a lot of more refined debugging techniques such as 'Breakpoints', examining program objects in the 'Immediate Window', and the use of the 'Watch Window'. But for now, simply be aware that this is a primary debugging technique you will use as a programmer.

Object Oriented Programming

The next class lesson is all about Object Oriented Programming.

"Whaaaattttt!" (I hear you moaning) "I just want to write programs. I didn't sign up to be a computer scientist!"

Fear Not! There are two reasons why this is a great move.

First, in today's programming environment, you simply can't be an effective programmer without understanding object oriented programming concepts. Even our very simple one-line "Hello World" program consisted of an object, a method, and a property. In my opinion, not understanding objects is the biggest single problem beginning programmers have. So we're going to confront the beast right up front!

Second, we're going to make this as painless as possible. We're not going to confuse you with a load of computer science jargon.

But right after that, we're going to jump right back into writing programming code with a lesson where we develop a VBA macro that you can probably use! We perfect that program a little more in the next lesson and finish up by showing you how to start using VBA with several applications at one time.