Microsoft Office Tutorials and References
In Depth Information
Covering the Basics of VBA
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
h VBA variables: 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
h Object methods: 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
h Standard programming constructs: VBA also includes many constructs found in modern
programming languages, including arrays, loops, and so on.
h Events: Some objects recognize specific events, and you can write VBA code that is
executed when the event occurs. For example, opening a workbook triggers a Workbook_
Open event. Changing a cell in a worksheet triggers a Worksheet_Change event.
Believe it or not, the preceding section pretty much describes VBA. Now it’s just a matter of
learning the details.
If you like analogies, here’s one for you that may help you understand the relationships between
objects, properties, and methods in VBA. In this analogy, I compare Excel with a fast-food
The basic unit of Excel is a Workbook object. In a fast-food chain, the basic unit is an individual
restaurant. With Excel, you can add workbooks and close workbooks, and the set of all the open
workbooks is known as Workbooks (a collection of Workbook objects). Similarly, the management
of a fast-food chain can add restaurants and close restaurants — and all the restaurants in the chain
can be viewed as the Restaurants collection — a collection of Restaurant objects.
An Excel workbook is an object, but it also contains other objects, such as worksheets, charts,
VBA modules, and so on. Furthermore, each object in a workbook can contain its own objects.
For example, a Worksheet object can contain Range objects, PivotTable objects, Shape
objects, and so on.