VBA is what is called an object-orientated programming language (OOP if you want to impress people), and you won't get far with it without understanding what objects, collections, methods and properties mean. That's what this blog tries to demystify!
How VBA Differs from English
Consider the following simple statement in English:
In English you put the verb at the start of the sentence and the thing you're doing something to (the object) at the end. In VBA it's the opposite, and you'd write:
or better still:
or perhaps:
Birds("Pigeon").Freedom = False
In this context:
Birds is a collection (a group of similar things)
Pigeon is an object (a thing)
Catch is a method (a verb - something you do to something)
Freedom is a property (an attribute of something)
Using Intellisense to Distinguish between Properties and Methods
Fortunately all this is easy to spot when you're writing VBA. Here we're starting a sentence to activate a workbook:
The code to activate a particular workbook
Here we have listed the following methods:
Symbol | What it is | Examples |
|---|
 | Method | AcceptAllChanges, Activate, AddToFavorites |
 | Property | ActiveChart, ActiveSheet, Application |
Presumbably the logic for the symbols is that throwing a green brick at someone is definitely a doing thing, and not a property!
Getting Help in VBA
When you click in any word and press F1 , you'll get help on how it's used:
An example of the help given for the Workbook object.
Every VBA help screen will always give a title divided into 2 parts:
The first part is the thing you're getting help for; the second part is what sort of a thing it is.
The Excel Object Model
Every application which uses VBA has its own object model. So (for example) here are some of the collections in different object models:
Application | Typical collections |
|---|
Word | Words, Documents, Paragraphs, Characters |
Excel | Worksheets, Workbooks, PivotTables |
PowerPoint | Presentations, Slides, Shapes |
Access | TableDefs, Forms, Reports |
You can press F2 in any application to get at its object model using something called Object Browser, but the results aren't easy to understand. Here's what you get for Excel:
A tiny part of the complete Excel object model (ie the set of all of the collections, objects, methods and properties which together make up Excel).
So with all that preamble out of the way, let's have a look at what collections, objects, methods and properties are in more detail!
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:
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 |
Testing your Understanding
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.
