McFedries P. Absolute Beginner's Guide to VBA / Chapter 2. Writing Your Own Macros

Computers & Technology
Education & Reference
Business & Investing
Science & Math

Absolute Beginner's Guide to VBA
Authors: McFedries P.
Published year: 2003
Pages: 15-17/146

<< Previous page
Table of contents
Next page >>

Chapter 2. Writing Your Own Macros

In this chapter

Understanding the advantages of writing your own macros
Getting to know the Visual Basic Editor
The basic steps required to write a macro
Learning how to craft your very own user -defined functions
Creating and using procedures
Understanding and working with VBA modules

Letting VBA do all the work by recording your macros is an easy way to automate tasks , and it's a technique you'll use often. However, to get the most out of VBA, you need to do some full-fledged programming, which means writing your own macros, either from scratch or by using a recorded macro as a starting point. Here are just a few of the advantages you gain by doing this:

If you make a mistake while recording a macro, particularly one that requires a large number of steps, you can edit the macro to fix the mistake rather than re-recording the whole thing from scratch.
You get full control over each macro, which means you ensure that your macros do exactly what you need them to do.
You can take advantage of the hidden power of VBA to manipulate the Office programs and to perform some impressive programming feats that are simply not available via the recording process.

To help you realize these advantages and many more, this chapter introduces you to the basics of writing simple procedures and functions, as well as how to get around in the Visual Basic Editor, which is the tool that VBA provides for writing macros by hand. This will set the stage for the next few chapters when I take a closer look at the specifics of the VBA language.

Displaying the Visual Basic Editor

To get the Visual Basic Editor onscreen in any Office program, select Tools, Macro, Visual Basic Editor. (Note, however, that for simplicity's sake, I use a single Office application ”Excel ”for the examples throughout this chapter.) Figure 2.1 shows the new window that appears.

Figure 2.1. You use the Visual Basic Editor to craft and edit your macros.

graphics/02fig01.jpg

tip

You can also get to the Visual Basic Editor by pressing Alt+F11. In fact, this key combination is a toggle that switches you between the Visual Basic Editor and the underlying application.

Touring the Visual Basic Editor

The idea behind the Visual Basic Editor is simple: It's a separate program that's designed to do nothing else but help you create and edit VBA macros. (In professional programming circles, the Visual Basic Editor is called an integrated development environment or IDE .)

When you open the Visual Basic Editor for the first time, you don't see much. The left side of the editor has two windows labeled Project and Properties. The latter you don't need to worry about right now. (I'll talk about it in Chapter 5, "Working with Objects.") The Project window (technically, it's called the Project Explorer) shows you the contents of the current VBA project. In simplest terms, a project is an Office file and all of its associated VBA items, including its macros and its user forms. (You learn about user forms in Chapter 13, "Creating Custom VBA Dialog Boxes.")

Opening an Existing Module

To do some work in the Visual Basic Editor, you usually start by opening a module , which is a VBA item that contains one or more macros. Here's how you do that:

In the Project window, open the Modules branch by clicking the plus sign (+) to its left.
Double-click the name of the module you want to open.

If you recorded a macro in Chapter 1, you should see a module named Module1. Go ahead and double-click that module to open it, as shown in Figure 2.2.

Figure 2.2. To write or edit a macro, first open the module you want to work with.

graphics/02fig02.jpg

As you can see in Figure 2.2, the open module contains the macro code from my example in Chapter 1. I should also point out that each module window has two drop-down lists beneath the title bar:

Object list This is the list on the left and it contains a list of the available objects for whatever project item you're working with. (I explain objects in detail in Chapter 5.) Modules don't contain objects, so this list contains only (General) for a module window.

Procedure list This is the list on the right and it contains all the procedures and functions in the module. When you select an item from this list, the editor displays that item in the module window.

Creating a New Module

If you don't have an existing module, you can create one any time you like by selecting the Insert, Module command. The Visual Basic Editor assigns the module a generic name, such as Module1 or Module2, but you're free to rename the module. See "Renaming a Module," later in this chapter.