Microsoft Office Tutorials and References
In Depth Information
Methods
Methods
Methods are actions that can be performed by objects. VBA objects have inherent behavioral
abilities. Following are examples of Excel objects and some of their methods:
The Range object of A1:D10 can have its cells’ contents cleared with the ClearContents
method:
Range(“A1:D10”).ClearContents
Workbooks and worksheets can be activated with the Activate method:
Workbooks(“Book1.xlsx”).Activate
Worksheets(“Sheet2”).Activate
Here’s a more complicated example, to call your attention to the fact that objects can contain
objects, not just properties. Suppose you have three pivot tables on Sheet1, and you only
want to refresh the pivot table named PivotTable2. As far as VBA is concerned, what you
really want to refresh is the PivotCache object of the PivotTable2 object of the Sheet1
worksheet object. This line of code would accomplish that, using the Refresh method:
Worksheets(“Sheet1”).PivotTables(“PivotTable2”).PivotCache.Refresh
This multiple-object syntax might look daunting at first, but you can take some
comfort in knowing that you’ve been writing VBA code in this manner since
Day 1. All objects (except the Application object, which is Excel itself) have a
Parent property, that is, another object to which they belong. In many cases,
you don’t need to specify the Parent object because it is inferred by default. For
example, if you are referring to cell A1 on your active worksheet, you do not
need to (though you could) express it as ActiveSheet.Range(“A1”) — you only
need to express it as Range(“A1”). In the preceding example, however, pivot
tables are embedded objects for which VBA requires you to specify the Parent
worksheet object. If all this talk of properties and methods is not clear yet, don’t
worry, it will all make perfect sense when you see the theory in action.
collections
Some of the VBA programming you learn in later lessons will involve the concept of collections, and
it is a topic I’ll touch on here. In object-oriented programming, a Collection is an object that
contains a group of like objects. For example, there is a Worksheets collection object that is the entire
group of Worksheet objects in your workbook. Even if one worksheet contains hundreds of
formulas and another worksheet is totally empty, both those worksheets are like objects because they are
both worksheets, and therefore they both are a part of the Worksheets collection.
As you’ll see, invoking the Collection object in your code is a terrific way to take some action
on all the objects in that collection, without needing to know anything specific about the
collected objects. For example, say you want to add some boilerplate text to every comment on your
 
Search JabSto ::




Custom Search