Writing Programs with VBA

Now that you know your way around the VB Editor workspace, it’s time to learn the basics of Visual Basic programming.

In VBA, a complete program is called a project, and contains forms and modules. Both forms and modules contain their own programming code. An individual line of code is called a statement; a group of statements, executed together, is called a procedure.

When you run a program, the statements and procedures within the modules are executed, in order, and appropriate forms (representing dialog boxes and the like) are displayed as they’re “called” from within the modules.

You can start a new project by creating a form and then programming modules around the form, or by creating a module and then adding forms and other modules to that module. All the modules and forms for a project are listed in the Project Editor, and have their own individual Code windows. Within the Code windows are the statements and procedures that make up the code for that module or form (or control).

Understanding Objects and Events

To introduce some of the most common VB concepts, we’ll create a simple custom form. Select Insert Ø User Form, or click the Insert User Form button on the toolbar. A new User Form window opens in the VB Editor workspace, along with a VB toolbar called the Toolbox.

Any object that you add to a form and can control the form’s behavior, such as a button or a text field, is called a control. For example, the OK buttons that show up in most dialog boxes are controls. The Toolbox contains a variety of common VB controls, which you’ll learn more about in Chapter 27. Attached to (or “behind”) each control, you create code that makes the control do what you want. If the control you placed on the form is a CommandButton control, you can, for example, program the button to open a Save dialog box to automatically let you choose a folder and save the current document; or you might have the button print the current view, close the application window, or do any other task within VB’s universe of programmability. The CommandButton is just a button to which you can attach code.

An event is a user- or system-generated action, such as the user’s click on a button, or a CD-ROM’s activation of the AutoPlay software. Visual Basic is called an event-driven programming language because it responds to events. You place control objects on your forms and then program them to activate in response to specific events. In the case of a command button, you would program it to respond to a Click event—the event that happens when a user mouses over to and clicks the button.

Each control on a form has its own capabilities, and can be coded to operate independently of everything else on the form. Consider again the OK button that you find in almost every dialog box. To create an OK button in VB, you simply click the CommandButton tool in the Toolbox, drag the cursor to the form, and drop it in. This procedure creates a button called CommandButton1, as shown in Figure 26.5. Note that the Project Explorer window is now set to your new UserForm, and the Properties window is displaying properties for the CommandButton you have just added.

Figure 26.5: A new Command-: Button on a form

Understanding Controls and Properties

Each control has a set of properties that defines how the control works; these properties are displayed in the Properties window. The properties for the CommandButton control are simple, and you use them to define the way the control looks and behaves. For our example form, you want to change the Default property of CommandButton1 to True. (You can see this Default property in the Properties window shown earlier in Figure 26.3.) When this property is set to True, and you open this form in an application and then press Enter, the button will “click” as if you’d clicked the button with the mouse. In Windows applications, almost all dialog boxes have a default button that responds this way when you press the Enter key. The default button is enabled when the dialog box opens.

Another property of a control is its Name. Each control receives a unique name when it’s created so it can be individually identified later. You can rename your controls anything you like as long as each name is unique. It is also a good idea to make the names easily recognizable so that you can readily identify their purpose when you reuse or edit the code next year.

Tip 

In the naming convention used by most VB programmers, command button names are prefixed with cmd—for example, cmdOK, cmdPrintReport, and cmdCancel.

When you choose a control to place on a form, your choice is based largely on the properties the control needs to have and the events the control needs to be able to respond to. If you want a control that responds to a user mouse click, a command button will do fine. It’s not the control you’d choose, however, if you want a control that a user can enter text in.

If we create a simple form, place a CommandButton control and a TextBox control on it, and program the form to put something in the TextBox control when the CommandButton is clicked, we have a simple example of programming in action:

If, on the other hand, we include the controls and omit the code, we’ll have an example of programming “inaction.” The form will look the same, but it won’t do anything without code to tell the button control what to do. When you click the button, nothing will happen. It’s safe to say that users don’t appreciate this type of form very much, regardless of how nice it looks or how many hours you spent creating it.

Understanding Visual Basic Code

The core of a program is the programming code. You can think of writing code as writing a list of instructions for the computer to use. In VBA, each line of code is called a statement. A statement is a complete instruction, and can indicate an action, define a variable, or declare a new object.

A statement is typically entered on a single line, although you can use a colon (:) to include more than one statement on a line. You can also use a line-continuation character ( _ ) to continue a long statement onto a second line.

Statements can contain keywords, operators, variables, constants, and expressions. You enter statements in Code windows by either typing the code manually or inserting pieces of code from the Objects or Procedures/Events lists. When a program is executed, the code is executed sequentially, one statement at a time.

Warning 

If you press Enter after entering a line of code and the line displays in red, there is an error somewhere in your statement.

In our ongoing example, we begin writing the programming code by double-clicking the CommandButton control to open the Code window. When the Code window opens for our CommandButton control, you’ll see that two lines of code (which, in the world of programmers, is called a snippet or a stub) have been automatically inserted:

Private Sub CommandButton1_click() End Sub

The VB Editor assumes that you want to add code to the control you just double-clicked, which was automatically named CommandButton1 when it was placed on the form. This snippet is a procedural framework, a structure that defines the beginning and the end of a Visual Basic procedure. (In the code snippet, Private tells the control that this code will only be available in the form in which it is being constructed.)

Sub is short for subroutine. The terms subroutine and function both refer to types of procedures in VB. Independent blocks of VB code are called procedures. There are three kinds of procedures in VBA: functions, subroutines, and property procedures.

You might already know what a function is from your work with Excel functions (such as AVERAGE and SUM) or Project calculated fields: a function is code that returns a value as the result of one or more calculations, comparisons, or other operations. For example, when you SUM a column of numbers in Excel, the result (a total) is returned to the cell containing the SUM formula. A subroutine is code that performs an operation, but doesn’t return a value. For example, a subroutine can open or close a form or report, launch an application, or write values in a table.

Note 

Property procedures are beyond the scope of this book—but please don’t confuse them with plain old properties, which you’ll be working with a lot in this chapter.

CommandButton1_Click identifies the CommandButton by name, and indicates what event will trigger the procedure: a click. End Sub indicates the close of the procedure.

Note 

If you use the Name property to define a name for the control before opening the code window, that name is used in the code’s click event procedure.

To get the command button to close the window when it is clicked, we just need to add one command between the Private Sub and End Sub lines: Unload Me. The whole completed procedure looks like this:

Private Sub CommandButton1_click()   Unload Me End Sub

Now that we’ve added a command in language that VB can understand, it will perform the action we ask of it. Clicking the small blue arrow button on the toolbar that looks like the Play button of a CD or DVD player (or pressing F5) causes the form to run. This is the same as selecting Run Ø Run Sub/UserForm. If you click the Command button on the form, the form disappears.

Tip 

When you are ready to go further with VBA and Visual Basic, there are a number of good Sybex books you should consult. In particular, check out Mastering VBA 6 (Guy Hart-Davis, ISBN 0-7821-2636-7) and VBA Developers Handbook, Second Edition (Ken Getz and Mike Gilbert, 0-7821-2978-1).

Understanding Procedures

An event procedure is tied to an object event (such as a button’s click event), and automatically runs when that event occurs. The code isn’t tied to any particular button or control, and runs only on demand. These general procedures are very useful; you can run the code by assigning it to a toolbar button, a command button, the macro menu, a menu bar, or all of the above.

Warning 

All executable code for a procedure must be contained within the procedure; procedures can’t be nested within other procedures.

A procedure is contained within “on” and “off” statements. You start a Sub procedure with a Sub statement and end it with an End Sub statement, as follows:

Sub subname()   statement   statement   statement End Sub

Creating a General Procedure

To create a general procedure in VBA code, follow these steps:

1. In the VB Editor for the currently open project, insert a new module (Insert Ø Module).

2. Choose Insert Ø Procedure from the menu to open the Add Procedure dialog box, shown in Figure 26.6.

   
   Figure 26.6: The Add Procedure dialog box

3. Enter a name for the procedure in the text box. According to the naming convention for procedures, you should use the bas prefix at the end of your procedure name.

4. Choose the Type of procedure you’re creating: subroutine, function, or property.

5. Select a scope. The Scope is a procedure’s range—Private procedures can be called only by other procedures in the same module; Public procedures can be called by any procedure in the same application.

6. Click OK to add the procedure’s snippet to the Code window.

Begin documenting your procedure. Type an apostrophe, press the spacebar, and enter at least your name and the current date. Feel free to add more descriptive information if you want, beginning each line with an apostrophe. In the next section, we’ll begin entering code to create a procedure.

Mastering Troubleshooting: Getting Help with Objects from the Object Browser

When you enter code, you need to know which objects are available, and what properties and methods the object has. For a list of objects, use the VBA libraries, which are just a click away in the Object Browser. The Object Browser displays all the available objects in your project—even those not currently in use.

To open the Object Browser, shown here, click the Object Browser button or choose View Ø Object Browser.

The library for the current application should already be selected, but you can choose other libraries from the Project/Library drop-down list. To search for an object, enter part or all of the object’s name in the Search Text box and then click the Search button. When you choose an object in the Class pane, you’ll see a list of properties and methods for the object in the right (Member) pane of the browser. If you want more information about the selected object, click the Help button in the Object Browser.

For a more global view of application objects, browse the object model, the map of all the objects in an application. The model shows each object and how it relates hierarchically to other objects in the application. To access the object model for an application, open the application, launch Help, and search on the word object. (You can select any object or collection in the model to get more information on the object, including samples of code using the object.)

The model uses different colors for objects and collections, which are groups of objects. For example, there is a collection of paragraphs in a document and a collection of documents in an application. Collections are important application features from a programming point of view. You can iterate through a collection if you want to count the number of paragraphs or save all open documents, but you also have the ability to affect one item in the collection.

Procedure Example: Changing Gantt Bar Colors

We will now create a simple procedure to format the bars of a Gantt chart for a Milestone task to be blue rather than their default color of black. We will then use this new bar style when a Milestone is Overdue.

Open the Object Browser. You can do this by clicking the Object Browser button, choosing View Ø Object Browser, or pressing F2.

To find exactly which method or command to use to make this change, we will use the Object Browser’s search function. Because we know we want to modify the display of a Gantt chart, we’ll type Gantt in the search box and then click the Find button. The search results are shown in Figure 26.7.

Figure 26.7: Using the Object Browser to search for the keyword Gantt

The results of this search include the function Gantt Bar Style Edit, which is the function we’ll use to create a new Gantt Bar Style. To use this function, we need to know how it works and how to use it (the syntax). Select the function in the Object Browser, and click the Help button. This opens the function’s VBA Help page. As you can see, this function has a lot of arguments; the function’s complete syntax looks like this:

expression.GanttBarStyleEdit(Item, Create, Name, StartShape, StartType,   StartColor, MiddleShape, MiddleColor, MiddlePattern, EndShape, EndType,   EndColor, ShowFor,Row, From, To, BottomText, TopText, LeftText, RightText,   InsideText)

To create a new Gantt Chart Bar Style, we need to know the following information:

Argument	Definition
Item Number	For a new style, the Item is always 1
Create	Yes
Name	Overdue Milestone
ShowFor	Milestone
StartShape	Diamond
StartColor	Blue
MiddleShape	None

The code to create the style looks like this:

Sub CreateGanttBar()   GanttBarStyleEdit Item:=-1, Create:=True, Name:="Overdue Milestone",   ShowFor:="Milestone", StartShape:=pjDiamond, StartColor:=pjBlue,     MiddleShape:=pjNone

Now that the Overdue Milestone Gantt Bar Style exists, you can assign it to all milestones in the Gantt chart that are overdue. A milestone is overdue if it is not completed and the finish date is prior to (less than) today. If these two conditions are true, you will use the GanttBarFormat function to assign the Overdue Milestone Gantt Bar Style to your milestones.

If Task.Milestone = Yes Then If Task.ActualFinish > Today Or Empty Then GanttBarFormat GanttStyle:=15 End IfEnd

Note 

In general, VBA’s Help system is first-rate, and worth browsing through before you start any serious programming. (From within the VB Editor, select Help Ø Microsoft Visual Basic Help to open the Help system, which includes a comprehensive Visual Basic Language Reference.) If the VBA Help files were not installed as part of your original Microsoft Project installation, you will be prompted to install them when you first access the Help system. If you click Yes, make sure that you have the Microsoft Project 2002 CD-ROM available, or that Project was installed from a network installation point so the Installer can find the files it needs.

Mastering Troubleshooting: Troubleshooting Simple Code Problems

What can go wrong when you’re writing VB code? Lots, if you don’t follow all the rules.

Visual Basic expects very specific kinds of information. If you forget, for example, to put = True for the Create property, VB will stop executing in the middle of your code and open a message box:

When you click the OK button, you’ll switch to the VB Editor. The name of the faulty procedure will be highlighted, and the property that brought VB to a halt will be selected. If the problem isn’t obvious, right-click on the code line, and choose Quick Info from the menu to see the syntax, required arguments, and optional arguments for the current line of code. Refer to the VB Help files for more in-depth information and examples for objects and properties. Fix the problem and then click the Run Sub button to try again.