Collections, Objects, Methods and Properties
Life is full of collections, objects, methods and properties - and so is
Excel!
Collections
A collection is a set of objects which behave in the same way. Here are
some examples of collections:
This is a collection of objects of type
elephant. All of these objects have Size
and Colour properties, as well as (for the unethical big game
hunter) Capture and Shoot
methods.
This is a collection of
Teletubbies (to put this another way, each object in the collection is of type
Teletubbie). Typical properties include
IsIrritating (permanently set to True)
and Color (xlGreen,
xlYellow, xlRed or xlPurplish),
while typical methods to apply to each object might include
Hit and Punch.
This is a collection of all of the
Worksheet objects in a workbook (or to be more
exact, all of the objects in the Worksheets
collection, which is contained within the workbook).. There are 3 objects in the collection.
Objects
An object is a single thing in a collection. The following are all
objects:
Elephants (1)
Teletubbies ("Tinky-Winky")
Worksheets ("Sheet1")
From this we see that you can refer to any object in two ways:
- By its name, where this is known; or
- By its number (although you need to know the order of the
objects in the collection to do this).
Generally, you should always refer to objects within a collection in VBA by
their name rather than their number. For example, Teletubbies("Dipsy") is far safer than
Teletubbies(1).
Methods
Remember that methods are things you do to objects - verbs, or actions,
depending on which way you want to look at it. For any given object, you
can only choose from a restricted set of things to do. For example, here
are some of the methods that work and don't work for a worksheet:
|
Method |
Whether it works or not |
|
Select |
Yes (selects the worksheet) |
|
Protect |
Yes (protects the worksheet) |
|
Delete |
Yes (deletes the worksheet) |
|
Discombobulate |
No |
|
Reticulate |
No |
|
Close |
No (you can close a workbook, but not a worksheet) |
Remember that you can use Intellisense to work out which methods are
supported by any object, although sadly the specific example of a worksheet is
the exception that proves the rule - for some reason Intetllisense doesn't work
properly for this particular object.
Properties
A property of an object is an attribute or characteristic of it. You
can only do two things to a property:
- find out what it is (read its value); or
- change it (write its value)
For this reason, properties are sometimes referred to as one of 3 different
types:
|
Type |
What it means |
|
Read-only |
You can find out the value of the property, but you can't change it |
|
Write-only |
You can change the property, but you can't ask what it is |
|
Read-write |
You can both ask for the value of the property and change it |
Any VBA statement involving a property will usually have an =
sign in it. For example:
ActiveSheet.Name = "Bob"
Dim s As String
s = ActiveCell.Value
Examples of collections, objects, methods and properties
Now that you're familiar with what collections, objects, methods and
properties are, let's see an example of their use. Consider the following
code:
Workbooks("test.xlsm").Activate
Worksheets(1).Select
Range("A1").Value = "Wise Owl"
Here's a breakdown showing what all the parts of this code are:
|
Part |
What it is |
|
Workbooks |
This is a collection, but ... |
|
Workbooks("test.xlsm") |
... this is an object within the collection |
|
Activate |
A method (something you do to a workbook) |
|
Select |
Another method (something you do to a worksheet) |
|
Range("A1") |
An object (of type Range) |
|
Value |
A property of a range which tells you what it contains |
Conclusion
Now that you understand what objects, collections, methods and properties
are, we can use these terms in this training blog! Perhaps now it's time
to:
- Reset the Status property of the object in
the collection of Appliances called
Kettle to xlOn
- Wait for the IsBoiled property of this
object to be True.
- Change the Level property of the first
object in the Mugs collection to be 80%.
- Apply the Dip method several times to the
first object in the Teabags collection.
- Apply the Pour method to the
ActiveMilkBottle object, and relax.
You've earned it!
Alternatively you could blow your mind by learning how to create your own
objects in VBA, using something called class modules.
