560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Collections, objects, methods and properties in Excel VBA
Part two of a two-part series of blogs
Visual Basic for Applications (VBA) is what's called an object-orientated programming language. This blog explains what this means!
Life is full of collections, objects, methods and properties - and so is Excel!
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.
An object is a single thing in a collection. The following are all objects:
'the first elephant in the collection
'the second worksheet
From this we see that you can refer to any object in two ways:
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).
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)|
|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.
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:
'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
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
'now go to the first worksheet in it
'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|
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:
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.
|Parts of this blog|
25 Aytoun Street