This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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! 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.

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!

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 07 July 2023

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:

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!

Parts of this blog
How VBA programming REALLY works (this blog) Collections, Objects, Methods and Properties

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR