Chapter 39: Introducing Visual Basic for Applications
813
l (^) You perform actions in VBA by writing (or recording) code in a VBA module sheet
and then executing the macro in any one of various ways. VBA modules are stored in
an Excel workbook, and a workbook can hold any number of VBA modules. To view or
edit a VBA module, you must activate the VB Editor window. (Press Alt+F11 to toggle
between Excel and the VB Editor window.)
l A VBA module consists of procedures. A procedure is basically computer code that per-
forms some action. The following is an example of a simple Sub procedure called
ShowSum, which adds 1 + 1 and displays the result:
Sub ShowSum()
Sum = 1 + 1
MsgBox “The answer is “ & Sum
End Sub
l (^) A VBA module also can store function procedures. A function procedure performs calcu-
lations and returns a single value. A function can be called from another VBA procedure
or can even be used in a worksheet formula. Here’s an example of a function named
AddTwo. (It adds two values, which are supplied as arguments.)
Function AddTwo(arg1, arg2)
AddTwo = arg1 + arg2
End Function
l VBA manipulates objects. Excel provides more than 100 classes of objects that you can
manipulate. Examples of objects include a workbook, a worksheet, a range on a work-
sheet, a chart, and a rectangle shape.
l (^) Objects are arranged in a hierarchy and can act as containers for other objects. For
example, Excel itself is an object called Application, and it contains other objects, such
as Workbook 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 all these objects
is referred to as an object model.
l (^) Objects that are alike form a collection. For example, the Worksheets collection con-
sists of all worksheets in a particular workbook. The ChartObjects collection consists
of all ChartObjects on a worksheet. Collections are objects in themselves.
l You refer to an object in your VBA code by specifying its position in the object hier-
archy, using a period as a separator.
For example, you can refer to a workbook named Book1.xlsx as
Application.Workbooks(“Book1.xlsx”)
This expression refers to the Book1.xlsx workbook in the Workbooks collection. The
Workbooks collection is contained in the Application object (that is, Excel).
Extending this to another level, you can refer to Sheet1 in Book1 as follows:
Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”)