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!

  1. How VBA programming REALLY works
  2. 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:

A collection of elephants

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.

 
Collection of teletubbies

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

 
Collection of worksheets

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:

  1. Reset the Status property of the object in the collection of Appliances called Kettle to xlOn
  2. Wait for the IsBoiled property of this object to be True.
  3. Change the Level property of the first object in the Mugs collection to be 80%.
  4. Apply the Dip method several times to the first object in the Teabags collection.
  5. 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.

  1. How VBA programming REALLY works
  2. Collections, Objects, Methods and Properties (this blog)
This blog has 0 threads Add post