Datasheet

Part I: Introducing VBA

VBA in a Nutshell

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

mary 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 a

chunk of computer 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 Sub procedure that doesn’t perform properly is said to be substandard.

✓ 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

A Function procedure that doesn’t work correctly is said to be

dysfunctional.

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

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

worksheet, 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

05_503690-ch01.indd 1805_503690-ch01.indd 18 4/12/10 11:22 PM4/12/10 11:22 PM