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
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!
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:
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:
|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|
25 Aytoun Street