To work through this section, get set up for working in the Visual Basic Editor with Word by performing the following steps. This chapter uses Word because it's the most widely distributed of the VBA-enabled applications. If you don't have Word, read along anyway without performing the actions on the computer; the examples are easy to follow. (Much of this will work on any VBA host application, though many of the commands shown here are specific to Word.) Here are the steps:

A procedure in VBA is a named unit of code that contains a sequence of statements to be executed as a group. For example, VBA contains a function (a type of procedure) named Left, which returns the left portion of a text string that you specify. For example, hello is a string of text five characters long. The statement Left("hello", 3) returns the leftmost three characters of the string: hel. (You could then display this three-character string in a message box or use it in code.) The name assigned to the procedure gives you a way to refer to the procedure.

All executable code in VBA must be contained in a procedure—if it isn't, VBA can't execute it and an error occurs. (The exception is statements you execute in the Immediate window, which take place outside a procedure. However, the contents of the Immediate window exist only during the current VBA session and are used for testing code.) Procedures are contained within modules, which in turn are contained within project files, templates, or other VBA host objects, such as user forms.

There are two types of procedures: functions and subprocedures.

When you program a macro in VBA, you're writing statements, which are similar to sentences in ordinary speech. A statement is a unit of code that describes an action, defines an item, or gives the value of a variable. VBA usually has one statement per line of code, although you can put more than one statement on a line by separating them with colons. (This isn't usually a good idea because it makes your code harder to read. Most programmers stick to one statement per line.)

You can also break a lengthy line of code onto a second line or a subsequent line to make it easier to read. You break a statements onto the next line by using a line-continuation character: an underscore (_) preceded by a space (and followed by a carriage return). You do so strictly for visual convenience; VBA still reads both lines, or all the continued lines, as a single "virtual" line of code. In other words, no matter how many line continuations you use for formatting, during execution it's still a single statement to VBA.

VBA statements vary widely in length and complexity. A statement can be a single word (such as Beep, which makes the computer beep, or Stop, which halts the execution of VBA code) to very long and complicated lines involving many components.

That said, let's examine the makeup of several sample VBA statements in Word. Most of them use the ActiveDocument object, which represents the active document in the current session of Word; a couple use the Documents collection, which represents all open documents (including the active document); and one uses the Selection object, which represents the current selection within a document. Don't worry if some of these statements aren't immediately comprehensible—you'll understand them soon enough.

Here are the example statements for you to try:

Documents.Open "c:	empSample Document.docm"
MsgBox ActiveDocument.Name
ActiveDocument.Words(1).Text = "Industry"
ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
Documents.Add
Selection.TypeText "The quick brown fox jumped over the lazy dog."
ActiveDocument.Save
Documents.Close SaveChanges:=wdDoNotSaveChanges
Application.Quit

Let's look at each of these statements in turn. The statement Documents.Open "c: empSample Document.docm" uses the Open method of the Documents collection to open the specified document—in this case, Sample Document.docm. Enter this statement in the Immediate window, using a path and filename of a document that exists on your computer.

Press the Enter key and VBA opens the document in the Word window. Just as when you open a document by hand while working interactively in Word, this statement in the macro makes this document the active document (the document whose window is currently selected).

The statement

MsgBox ActiveDocument.Name

uses the MsgBox function to display the Name property of the ActiveDocument object (in this example, Sample Document.docm). As an experiment, type this MsgBox statement into the Immediate window (type in lowercase, and use VBA's Help features as you choose) and press the Enter key. VBA displays a message box over the Word window. Click the OK button to dismiss the message box.

Now you see how you can quickly test a statement using the Immediate window. You don't have to execute the entire macro; you can just try a statement out in the Immediate window if you want to see its effect.

Next, the statement

ActiveDocument.Words(1).Text = "Industry"

uses the assignment operator (the equal [=] sign) to assign the value Industry to the Text property of the first item in the Words collection in the ActiveDocument object. Enter this statement in the Immediate window and press the Enter key. You'll see Word Industry (in the current typeface, and probably without the boldface) at the beginning of the document you opened.

Note that after this line executes, the blinking insertion point now appears at the beginning of this word rather than at the end of the word where it would be if you'd typed the word. This happens because VBA manipulates the properties of the document directly rather than imitating "typing" into it.

The statement

ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

uses the Close method to close the ActiveDocument object. It uses one argument, SaveChanges, which controls whether Word saves the document that's being closed (if the document contains unsaved changes). In this case, the statement uses the constant wdDoNotSaveChanges to specify that Word shouldn't save changes when closing the document. Enter this statement in the Immediate window and press the Enter key and you'll see VBA make Word close the document.

An argument is information you send to a procedure. For example, in this next statement, the argument is the text string show, which is sent to the built-in MsgBox function:

MsgBox ("show")

A MsgBox function will display any text. So you send it an argument: the particular text you want it to display. You'll learn more about arguments shortly.

Now try entering this statement in the Immediate window:

Documents.Add

This statement uses the Add method of the Documents collection to add a new Document object to the Documents collection. In other words, it creates a new document. Because the statement doesn't specify which template to use, the new document is based on the default template (Normal.dotm). When you enter this statement in the Immediate window and press Enter, Word creates a new document. As usual, this new document becomes the active document.

The statement

Selection.TypeText "The quick brown fox jumped over the lazy dog."

uses the TypeText method of the Selection object to type text into the active document at the position of the insertion point or current selection. (The Selection object represents the current selection, which can be either a "collapsed" selection—a mere insertion point with nothing actually selected, as in this example—or one or more selected objects, such as one or more words.)

If text is selected in the active document, that selection is overwritten as usual—unless you've cleared the Typing Replaces Selected Text check box by pressing Alt+F, I and then clicking the Advanced option in the left pane of the Word Options dialog box. In that case, the selection is collapsed to its beginning and the new text is inserted before the previously selected text.

But in this example—because you just created a new document—nothing is selected. Enter the previous Selection.TypeText statement in the Immediate window and press the Enter key and Word enters the text. Note that this time the insertion point ends up after the inserted text; the TypeText method of the Selection object is analogous to typing something into Word yourself.

The statement

ActiveDocument.Save

uses the Save method (command) to save the ActiveDocument object. This statement is the VBA equivalent of clicking the File tab on the Ribbon and choosing Save while working interactively in Word. If you enter this statement in the Immediate window and press Enter, Word displays the Save As dialog box so you can save the document as usual. For now, however, click the Cancel button to dismiss the Save As dialog box. Word displays a Microsoft Visual Basic error message box (see Figure 5.1). Click the OK button to dismiss it; you'll learn how to handle errors such as this in your code in Chapter 17, "Debugging Your Code and Handling Errors."

Figure 5.1. The Visual Basic Editor displays a VBA error message box when it encounters an unhandled error in an executed command.

The statement

Documents.Close SaveChanges:=wdDoNotSaveChanges

is similar to the previous ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges statement except that it works on the Documents collection rather than the ActiveDocument object. The Documents collection represents all open documents in the current Word session. So this statement closes all open documents and doesn't save any unsaved changes in them. Enter this statement in the Immediate window and press Enter and you'll see that Word closes all the open documents.

The statement

Application.Quit

uses the Quit method of the Application object to close the Word application. Enter the statement in the Immediate window and press the Enter key. Word closes itself, also closing the Visual Basic Editor in the process because Word is the host for the Visual Basic Editor.

Getting Help in Visual Basic for Applications

The Visual Basic Editor offers comprehensive help on the Visual Basic for Applications programming language. To view it, choose Help

The Visual Basic Help files use a couple of conventions you should know about before you try to use them:

• Italics denote variables or values you'll need to change yourself.

• Brackets ([ and ]) denote optional arguments.

This book uses the same conventions, so you'll see them in use soon.

If your computer doesn't offer you any help with VBA, whoever installed the application in question may not have installed the relevant files (perhaps to save space). Install the files, and all should be well.

If you don't find what you need by searching the Microsoft Visual Basic Help section, choose Help

Or paste this URL in your browser's address field: www.microsoft.com/downloads/details.aspx?FamilyID=2baf54a3-bbde-4cb8-b0e6-19005939db6a&displaylang=en.

Then scroll down until you find the Word2010DevRef.exe download. Click the Download button. Then run this .exe file to install the library. By default, the file will install in C:Office 2010 Developer ResourcesDeveloper References. As you can see on the web page, there are DefRef downloads for all component applications of Office 2010 as well as for Office itself (Office2010DevRefs.exe). These are large libraries of useful information about Office, Word, and the other applications' VBA implementations.

A keyword is a word defined as part of the VBA language. Here are some examples:

The names of functions and subprocedures are not keywords (neither built-in procedures nor procedures you write). Note that in this book I sometimes use the term command as a synonym for keyword.

Identifying Keywords by Color

The Visual Basic Editor displays all keywords in the color specified for Keyword Text on the Editor Format tab of the Options dialog box (choose Tools

An expression consists of a combination of keywords, operators, variables, and/or constants that produces a string, number, or object. For example, you could use an expression to run a calculation or to compare one variable against another. Here's an example of a numeric expression (shown in boldface) that compares the variable N to the number 4 by using the > (greater than) operator:

If N > 4 Then

An operator is a symbol you use to compare, combine, or otherwise work with values in an expression. VBA has four kinds of operators:

You'll look at the different kinds of operators and how they work in Chapter 6.

A variable is a location in memory set aside for storing a piece of information that can be changed while a procedure is running. (Think of it as a named, resizable compartment within the memory area.) For example, if you need the user to input their name via an input or a dialog box, you'll typically store the name in a variable so you can work with it in the procedure.

VBA uses several types of variables, including these:

Either you can let VBA create Variant variables in which to store your information by default or you can specify a data type for any given variable. Specifying the types of variables has certain advantages that you'll learn about in due course.

For the moment, try creating a variable in the Immediate window. Type the following line and press Enter:

myVariable = "Some sample text"

Nothing visible happens, but VBA has created the myVariable variable. It has set aside some memory and labeled that area myVariable. Now, type the following line and press Enter:

MsgBox myVariable

This time, you can see the result: VBA goes to the area you specified (with the variable name myVariable) and retrieves the value. A message box appears containing the text you stored in the variable.

You can declare variables either explicitly or implicitly. An explicit declaration specifies the name you want to give the variable, and usually its type, before you use the variable in your code. An implicit declaration occurs when you tell VBA to store data in a variable that you have not explicitly declared. VBA then stores the data in a Variant variable. (In other words, if you just use a variable in your code without declaring it—as with myVariable in the previous example—it's implicit.)

In the next few chapters, you'll use a few implicit variable declarations to keep things simple. You don't have to type in any code that declares implicit variables. VBA will create them for you when you first use them in a statement. After that, you'll start using explicit variable declarations to make your code run faster and be easier to read. Also, some types of errors can be avoided if you explicitly declare all variables. So declaring is a good habit to get into.

A constant is a named item that keeps a constant value while a program is executing. The constant's meaning doesn't change at different times of program execution. (It's not a variable.)

VBA uses two types of constants: intrinsic constants, which are built into the VBA language itself (and individual Office applications' implementations of VBA), and user-defined constants, which you create. For example, the built-in constant vbOKCancel is always available in VBA to be used with the MsgBox function. This constant creates a message box that contains an OK and a Cancel button. There are sets of built-in constants for colors, printing (vbTab, for example), and other properties.

Concerning constants that you define, you might want to create one to store a piece of information that doesn't change, such as the name of a procedure or the distance between Boston and New York. In practice, the built-in intrinsic constants are used quite often in VBA programming; user-defined constants not so much. It's just as easy to put the distance between those cities in a variable.

An argument is a piece of information—supplied by a constant, a variable, a literal, or an expression—that you pass to a procedure, a function, or a method. Some arguments are required; others are optional. The text hello there in the this MsgBox function is an argument:

MsgBox "hello there"

Here's another example. As you saw earlier, the following statement uses the optional argument SaveChanges to specify whether Word should save any unsaved changes while closing the active document:

ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

This optional argument uses the built-in constant wdDoNotSaveChanges.

txtMsg = "Hi there!"
MsgBox (txtMsg)

MsgBox ("Hi there!")

The Visual Basic Editor's helpful prompts and the Visual Basic Help file show the list of arguments for a function, a procedure, or a method in parentheses, with any optional arguments enclosed in brackets. If you have its Auto Quick Info feature activated, the editor displays the argument list for a function, procedure, or method after you type its name followed by a space.

Figure 5.2 shows the argument list for the Document object's Open method. Type Documents.Open, then press the spacebar to see the argument list.

The FileName argument is required, so it isn't surrounded by brackets. All the other arguments (ConfirmConversions, ReadOnly, AddToRecentFiles, and so on) are optional and so are surrounded by brackets.

If you don't supply a value for an optional argument, VBA uses the default value for the argument. (To find out the default value for an argument, consult the VBA Help file. The default is usually the most commonly employed value.) The Visual Basic Editor uses boldface to indicate the current argument in the list; as you enter each argument, the next argument in the list becomes bold.

Figure 5.2. Optional arguments are enclosed within brackets.

Documents.Open ReadOnly:=False, FileName:= "c:	empExample.docm", _
    ReadOnly:=False, ConfirmConversions:=True

Documents.Open FileName:="c:	empExample.docm", _
    ConfirmConversions:=True, ReadOnly:=False

Documents.Open "c:	empExample.docm",, False

objMyDocument = Documents.Open(FileName:="c:	empExample.docm", _
    ConfirmConversions:=True, ReadOnly:=False)

MsgBox ("Hi there!")
MsgBox "Hi there!"

To VBA, each application consists of a series of objects. Here are a few examples:

Most of the actions you can take in VBA involve manipulating objects. For example, as you saw earlier, you can close the active document in Word by using the Close method on the ActiveDocument object:

ActiveDocument.Close

A collection is an object that contains other objects, the way an umbrella stand object contains umbrella objects. Collections provide a way to access all their members at the same time. For example, the Documents collection contains all the open documents, each of which is an object. Instead of closing Document objects one by one, you can close all open documents by using the Close method on the Documents collection:

Documents.Close

Likewise, you can use a collection to change the properties of all the members of a collection simultaneously.

Each object has a number of properties. For example, the current document in Word has properties such as the number of sentences. Type this into the Immediate window, then press Enter:

MsgBox (ActiveDocument.Sentences.Count)

Here you're using the Count property of the Sentences collection to find out how many sentences are in the document.

Likewise, even a single character has various properties, such as its font, font size, and various types of emphasis (bold, italic, strikethrough, and so on).

A method is an action you can perform with an object. Loosely speaking, a method is a command. Different objects have different methods associated with them—actions you can take with them or commands you can specify that they must perform. For example, here's a list of some methods associated with the Document object in Word (and with other objects such as the Workbook object in Excel and the Presentation object in PowerPoint):

An event is an occurrence that VBA recognizes as having happened, usually something that happened to an object. For example, the opening of a file (either by a user or by a procedure) typically generates an event. The user's clicking a button in a user form generates a Click event.

By writing code for an event, you can cause VBA to respond by taking actions when that event occurs. For example, for a user form, you might write some code in an OK button's click event. This code might check that all necessary settings were specified by the user when the user clicked the OK button to dismiss the user form and apply the settings. You might write additional code within that button's click event that responded (perhaps by displaying a message box) if the user had failed to type in some required information. In essence, you can write code in an event to tell VBA what to do if that event is triggered. You don't have to write code for all events; sometimes you'll write code in only one of them. But if you put a button captioned "Display Results" on a user form, you'd better at least write some code in that button's Click event to display some results.

Objects and Their Components

I'll have much more to say about objects throughout the rest of this book. For now, see if you can identify the three primary parts of a typical object: properties (its qualities), methods (ways you can make the object behave), and events (something that happens to an object while a program or application is executing). Collectively, these three components of an object are called the object's members.

Take a look at the following code window. See if you can spot properties, methods, and an event of the Document object:

Here, you can see that the ThisDocument object is selected in the Project Explorer on the left. This object has available to it the properties in the long list displayed in the Properties window on the left side. You can either modify those properties directly in the Visual Basic Editor or write code that modifies them on the fly while the macro executes.

On the right side is a drop-down list of events—actions that can happen to a Document object or at least happen while the document is in existence within the computer. You can write code in any of these events (in the Code window, each event will be a separate subprocedure, enclosed within the Sub and End Sub statements).

In this example, I'm writing code to query users if they attempt to close the document. This code will execute anytime this document's close event is triggered (when the user clicks the x button in the upper-right corner of the window, for instance).

Only one method is shown in the Code window illustration. Can you spot it? It's in boldface in the following code example:

Private Sub Document_Close()

Dim intAnswer As Integer

intAnswer = MsgBox("Do you want to check the spelling?", _
        vbOKCancel, "Document Is Being Closed")

If intAnswer = 1 Then ' they clicked OK. 1 = OK  2 = Cancel

    ThisDocument.CheckSpelling

End If

End Sub

As you can see, CheckSpelling is a method (an action you can specify that the object should do).