This is the third blog in a series about creating macros in Excel, Word and other applications within the Microsoft Office.  This article is about Understanding Macro Code.

Understanding Macro Code

VBA is a Language

Many people, after learning how to record a macro, take a look at the code and then they panic. “How in tarnation are we supposed to understand all that gibberish?” The code is definitely not English. Well, with the help of this article, we hope to demystify that gibberish, and show you that it is similar to English.

The code is written in a programming language called “Visual Basic for Applications” or VBA. VBA, like other programming languages is a language. And, like English, VBA has Nouns, Adjectives, Verbs, and Adverbs. But that’s not what they are called in VBA. Instead they are called Objects, Properties, Methods, and Arguments.

Objects are like Nouns

We learned in grade school that a noun is a person, place, or thing. In VBA an “object” is a thing. For example, in the real world a pen, pencil, marker, and eraser are objects. In Excel the objects include cells, rows, columns, worksheets, and workbooks. In Word objects include documents, paragraphs, and characters. PowerPoint has slides, shapes, and other objects, while Microsoft Access has tables, queries, forms, reports, and controls.

Properties are like Adjectives

An adjective describes a noun. Similarly, a property describes an object. In the real world, a crayon may have the color blue. In this case color is the property and blue is the value of that property. In Excel a cell with red text has a font color property which has a value of red. The cell could also have a an interior color property with a value of white. In Word the paragraph object has an alignment property that determines if the paragraph is left, center, or right justified.

Properties can also be objects, or sub-objects. For example, in the real world your hand is an object that has five fingers. Each finger is a property of the hand. But each finger is also an object. Similarly, a PowerPoint slide is an object that can have multiple shapes. Each shape is a property of the slide, but each shape is also an object with its properties (color, size, shape, etc).

Because small objects can be properties of larger objects, you may soon discover there is whole hierarchy of objects and properties. For example, in Excel you have a workbook composed of multiple sheets. Each sheet is composed of multiple rows and columns. Each row and each column has multiple cells. Each of these is an object. But the sheet is also a property of the workbook, and the rows and columns are properties of the sheet, and the cell is a property of the respective row and column. As you can see, this can quickly get very complex.

Methods are like Verbs

A verb is an action that the noun can do. A method is like a verb, it is an action that the object can take. For example, in the real world pens and pencils have a write method. In Excel you can use a column’s AutoFit method to automatically resize an Excel column. In Word you can use a windows activate method to activate that window. In Access you can put the cursor in a text box by using the text box’s SetFocus method.

Arguments are like Adverbs

In the same way that an adverb describes a verb, an argument describes how to execute a method. For example, in the real world the pen’s write method would have a “what” argument that defines what the pen is supposed to write. In VBA the file’s Close method has a SaveChanges argument that determines if changes should be saved.

Syntax

Syntax is the structure of a sentence or phrase. For example in English we usually put the adjective before the noun, and we separate them with a space. Example: “Red Pencil.” But other languages have a different syntax in that they list the noun first and then the adjective.

In VBA the syntax always puts the object first and then uses a period to separate the object from the property or method. Thus we get Object.Property = value or Object.Method Argument:=value. Here are some examples with their plain English translation.