Sunday, December 9, 2007

The Basics of VBA

Before I get into the meat of things, I suggest that you read through the material in
this section to get a broad overview of where I’m heading. These are the topics that
I cover in the remainder of this chapter.
Following is a quick-and-dirty summary of what VBA is all about:
✦ You perform actions in VBA by executing VBA code.
✦ You write (or record) VBA code, which is stored in a VBA module.
VBA modules are stored in an Excel workbook, but you view or edit a module
using the Visual Basic Editor (VBE).
✦ A VBA module consists of procedures.
A procedure is basically a unit of computer code that performs some action.
Here’s an example of a simple Sub procedure called Test: This procedure
calculates a simple sum and then displays the result in a message box.
Sub Test()
Sum = 1 + 1
MsgBox “The answer is “ & Sum
End Sub
✦ Besides Sub procedures, a VBA module can also have Function procedures.
A Function procedure returns a single value (or possibly an array). A function
can be called from another VBA procedure, or used in a worksheet formula.
Here’s an example of a function named AddTwo:
Function AddTwo(arg1, arg2)
AddTwo = arg1 + arg2
End Function
✦ VBA manipulates objects contained in its host application (in this case, Excel
is the host application).
Excel provides you with more than 100 classes of objects to manipulate. Examples
of objects include a workbook, a worksheet, a range on a worksheet, a
chart, and a drawn rectangle. Many more objects are at your disposal, and
you can manipulate them using VBA code.
✦ Object classes are arranged in a hierarchy.
Objects can act as containers for other objects. For example, Excel is an
object called Application, and it contains other objects, such as Workbook
and CommandBar objects. The Workbook object can contain other objects,
such as Worksheet objects and Chart objects. A Worksheet object can
contain objects such as Range objects, PivotTable objects, and so on. The
arrangement of these objects is referred to as Excel’s object model.
✦ Like objects form a collection.
For example, the Worksheets collection consists of all the worksheets in a
particular workbook. The CommandBars collection consists of all CommandBar
objects. Collections are objects in themselves.
✦ When you refer to a contained or member object, you specify its position in
the object hierarchy using a period (also known as a “dot”) as a separator
between the container and the member.
For example, you can refer to a workbook named Book1.xls as
Application.Workbooks(“Book1.xls”)
This refers to the Book1.xls workbook in the Workbooks collection. The
Workbooks collection is contained in the Excel Application object.
Extending this to another level, you can refer to Sheet1 in Book1 as
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
You can take it to still another level and refer to a specific cell as follows:
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range
(“A1”)
✦ If you omit a specific reference to an object, Excel uses the active objects.
If Book1 is the active workbook, the preceding reference can be simplified as
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the reference
even more:
Range(“A1”)
✦ Objects have properties.
A property can be thought of as a setting for an object. For example, a range
object has properties such as Value and Name. A chart object has properties
such as HasTitle and Type. You can use VBA to determine object properties
and also to change them.
✦ You refer to properties by combining the object with the property, separated
by a period.
For example, you can refer to the value in cell A1 on Sheet1 as
Worksheets(“Sheet1”).Range(“A1”).Value
You can assign values to VBA variables. Think of a variable as a name that you
can use to store a particular value.
To assign the value in cell A1 on Sheet1 to a variable called Interest, use the
following VBA statement:
Interest = Worksheets(“Sheet1”).Range(“A1”).Value
✦ Objects have methods.
A method is an action that is performed with the object. For example, one of
the methods for a Range object is ClearContents. This method clears the
contents of the range.
✦ You specify methods by combining the object with the method, separated by
a period.
For example, to clear the contents of cell A1 on the active worksheet, use this:
Range(“A1”).ClearContents
✦ VBA also includes all the constructs of modern programming languages,
including arrays, looping, and so on.
Believe it or not, the preceding section pretty much describes VBA. Now it’s just a
matter of learning the details, which is what I cover in the rest of this chapter.
Introducing the Visual Basic Editor
In Excel 5 and Excel 95, a VBA module appeared as a separate sheet in a workbook.
Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook.
Rather, you use the Visual Basic Editor (VBE) to view and work with VBA modules.
VBA modules are still stored with workbook files; they just aren’t visible unless you
activate the VBE.
The VBE is a separate application that works seamlessly with Excel. By seamlessly, I
mean that Excel takes care of the details of opening the VBE when you need it. You
can’t run VBE separately; Excel must be running in order for the VBE to run.
Activating the VBE
When you’re working in Excel, you can switch to the VBE using any of the following
techniques:
✦ Press Alt+F11.
✦ Select Tools➪Macro➪Visual Basic Editor.
✦ Click the Visual Basic Editor button, which is located on the Visual Basic
toolbar.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
Figure 7-3: The Visual Basic Editor window
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
The VBE also features shortcut menus. As you’ll discover, you can right-click
virtually anything in a VBE window and you’ll get a shortcut menu of common
commands.
Toolbars
The Standard toolbar, which is directly under the menu bar by default, is one of six
VBE toolbars available (the menu bar is also considered a toolbar). VBE toolbars
work just like those in Excel: You can customize toolbars, move them around, display
other toolbars, and so forth. Use the View➪Toolbars➪Customize command
to work with VBE toolbars.
Project Explorer window
The Project Explorer window displays a tree diagram that consists of every workbook
that is currently open in Excel (including add-ins and hidden workbooks).
Each workbook is known as a project. I discuss the Project Explorer window in
more detail in the next section (“Working with the Project Explorer”).
If the Project Explorer window is not visible, press Ctrl+R. To hide the Project
Explorer window, click the Close button in its title bar (or right-click anywhere
in the Project Explorer window and select Hide from the shortcut menu).
Code window
A code window (sometimes known as a Module window) contains VBA code. Every
item in a project has an associated code window. To view a code window for an
object, double-click the object in the Project Explorer window. For example, to view
the code window for the Sheet1 object, double-click Sheet1 in the Project Explorer
window. Unless you’ve added some VBA code, the code window will be empty.
Another way to view the code window for an object is to select the object in the
Project Explorer window and then click the View Code button in the toolbar at the
top of the Project Explorer window.
I discuss code windows later on in this chapter (see “Working with Code
Windows”).
Immediate window
The Immediate window is most useful for executing VBA statements directly, testing
statements, and debugging your code. This window may or may not be visible.
If the Immediate window isn’t visible, press Ctrl+G. To close the Immediate window,
click the Close button in its title bar (or right-click anywhere in the Immediate window
and select Hide from the shortcut menu).

No comments: