Basic concepts of VBA: objects, methods, and properties

The first thing to know is that VBA code is assembled in a way that mimics how you’d describe things around you. It’s made up of the following four concepts:

Concept

Description

Example

Object

A "thing"

Worksheet

Method

Action, or something a "thing" can do

Add a "thing"

Property

Description, or characteristic of a "thing"

Name

Collection

A group of "things"

Worksheets

Together, these four concepts are what programmers call an object oriented programming model. This is a way to accomplish a task by describing the “thing” or “Object/Collection” you want to act on the specific characteristics or “Property” to describe the thing you want to act on, and how or “Method” you want to use to perform your action.

In the line, Selection.Rows.Delete, Selectionis theobject, or “thing” that you’re referring to in your code. You described that “thing” in further detail by saying it’s the Rows property that you wanted to act on. Next, you applied the method of deleting the row with the Delete syntax.

At first, these concepts may seem a little confusing, but try talking out what you’re trying to accomplish, and identify what is the “thing” you are trying to work on, what are the characteristics of that “thing”, and what action you want to perform on that “thing”. With a little practice and perseverance, you will be on your way to creating some powerful VBA macros.

Basic control flow statements

Here are two more concepts that you’ll want to learn in order to be able to build useful VBA macros.

If this, then that: The If… Then statement is a programming tool, which allows you to control the type of action you want to perform, based on some type of criteria. For instance, say you wanted all Heading 1 text to have Helvetica 16 point bold format, and all Heading 2 text to have a Helvetica 12 point italic format. The If… Then statement is the tool that would enable you to check if the text is a Heading 1 or Heading 2 style, and then apply the appropriate format.

For Each Loop: This is helpful when you want to process through a collection, or a set of “things” and perform an action. The action could also be to apply the If This, Then That concept, so you can have even more granular control. For instance, say you have a table and you want to look through each row in that table for a certain word, and whenever you find that word yellow, you want to color the cell yellow, and whenever you find the word red, you color the table cell red. You would use the For Each loop and the If… Then statement together to build this procedure.

More in-depth VBA information

Now that you have learned the fundamentals, here are some additional links for more in-depth information about VBA for each Office app.