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 (this blog)
- 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:
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:
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 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:
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!
- How VBA programming REALLY works (this blog)
- Collections, Objects, Methods and Properties