564 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
Search our website
We also send out useful tips in a monthly email newsletter ...
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!
This series of blogs is part of our Excel VBA online tutorial. We also run classroom-based training courses in Excel and in VBA.
|
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!
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:
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!
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:
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!
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.