Datasheet

VBA in a Nutshell

Just to let you know what you’re in for, I’ve prepared a quick and dirty

summary of what VBA is all about. Of course, I describe all this stuff in

semi-excruciating detail later in the book.

 You perform actions in VBA by writing (or recording) code in a

VBA module. You view and edit VBA modules by using the Visual

Basic Editor (VBE).

 A VBA module consists of Sub procedures. A Sub procedure has noth-

ing to do with underwater vessels or tasty sandwiches. Rather, it’s com-

puter code that performs some action on or with objects (discussed in a

moment). The following example shows a simple Sub procedure called

AddEmUp. This amazing program displays the result of 1 plus 1.

Sub AddEmUp()

Sum = 1 + 1

MsgBox “The answer is “ & Sum

End Sub

 A VBA module can also have Function procedures. A Function proce-

dure returns a single value. You can call it from another VBA procedure

or even use it as a function in a worksheet formula. An example of a

Function procedure (named AddTwo) follows. This Function accepts

two numbers (called arguments) and returns the sum of those values.

Function AddTwo(arg1, arg2)

AddTwo = arg1 + arg2

End Function

 VBA manipulates objects. Excel provides dozens and dozens of objects

that you can manipulate. Examples of objects include a workbook, a work-

sheet, a cell range, a chart, and a Shape. You have many more objects at

your disposal, and you can manipulate them by using VBA code.

 Objects are arranged in a hierarchy. Objects can act as containers for

other objects. At the top of the object hierarchy is Excel. Excel itself is an

object called Application. The Application object contains other objects

such as Workbook objects and Add-In 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 and

PivotTable objects. The term object model refers to the arrangement of

these objects. (Object model mavens can find out more in Chapter 4.)

 Objects of the same type form a collection. For example, the Worksheets

collection consists of all the worksheets in a particular workbook. The

Charts collection consists of all Chart objects in a workbook. Collections

are themselves objects.

Part I: Introducing VBA

05_046746 ch01.qxp 1/12/07 6:16 PM Page 16