BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Visual Basic for Applications (VBA) is what's called an object-orientated programming language. This blog explains what this means!
- How VBA programming REALLY works
- Collections, Objects, Methods and Properties (this blog)
This series of blogs is part of our Excel VBA online tutorial. We also run classroom-based training courses in Excel and in VBA.
Posted by Andy Brown on 09 November 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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:
'the first elephant in the collection
Elephants (1)
'Tinky-Winky
Teletubbies ("Tinky-Winky")
'the second worksheet
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:
'change the name of the current sheet
ActiveSheet.Name = "Bob"
'find out the value in the current cell
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:
'go to a specific open workbook by name
Workbooks("test.xlsm").Activate
'now go to the first worksheet in it
Worksheets(1).Select
'put words in the top left cell
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.
- How VBA programming REALLY works
- Collections, Objects, Methods and Properties (this blog)