Collections, objects, methods and properties in Excel VBA
Part one 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 (this blog)
  2. Collections, Objects, Methods and Properties

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.

How VBA programming REALLY works

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:

Catch the pigeon

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:

Pigeon.Catch

or better still:

Birds("Pigeon").Catch

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:

Activating a workbook - code

The code to activate a particular workbook

Here we have listed the following methods:

Symbol What it is Examples
Method symbol Method AcceptAllChanges, Activate, AddToFavorites
Property symbol 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:

Example of help for workbook object

An example of the help given for the Workbook object.

 

Every VBA help screen will always give a title divided into 2 parts:

Example of help text

The help text always gives:

 

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:

The Excel object browser window

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!

 

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