After recording a macro in Microsoft Excel or Microsoft Word, often there is a need to edit the macro. However, before being able to successfully edit a macro written in VBA (Visual Basic for Applications), one first needs to know how to read it. That is the subject of this blog post.

English vs VBA

VBA is a programming language. Like the English language, VBA has different parts of speech. For example, in English we have nouns, adjectives, verbs, and adverbs. In VBA we have similar concepts called objects, properties, methods, and events.

Objects are like nouns

An object is like a noun – it’s a thing. For example, a cell is an object. So is a worksheet, and so is a workbook.

You can refer to a cell by its address. For example, in VBA the following code will refer to cell A1. Notice that the cell’s address is in quotes.

Range(“A1”)

You can refer to a worksheet by its name. For example, in VBA the following code will refer to Sheet2. Again, notice the use of quotes.

Worksheets(“Sheet2”)

You can also refer to a workbook by its name. For example, in VBA the following code will refer to a workbook named Movies. Again, notice the use of quotes.

Workbooks("Movies.xlsx")

Properties are like adjectives

Like the way an adjective describes a noun in English, a property in VBA describes a characteristic of an object.

In English, adjectives usually precede a noun. Notice in the following examples, Red Ball, Good Boy, Great Job,  that the adjectives Red, Good, and Great come before the nouns Ball, Boy, and Job.

Unlike English, in VBA the property comes after object, and they are separated by a period.

Syntax:

Object.Property

Examples:

ActiveCell.Address
Range(“A3”).Value

In the examples above, ActiveCell and Range(“A3”) are objects, Address and Value are properties.

In VBA you can write code to change a property.

Example:

Range("D2") = 10

In the example above the code changes the value of cell D2 to 10.

Some properties are complex, meaning they may have subproperties. For example, a cell’s font has a font name, font color, and font size. To refer to these properties two sets of periods are needed. The first period separates the object from the font property, and the second separates the font from its subproperty.

Examples:

Range("F10").Font.Name = "Arial"
Range("F10").Font.Size = 14
Range("F10").Font.Color = vbBlue

The three statements above change cell F10’s font to Arial 14 point with a blue color.

Methods are like Verbs

In English, Verbs are action words. In VBA, actions are performed with methods. Each executed method affects an object, and therefore VBA syntax requires that you first list the object, then a period, and then the method.

Syntax:

Object.Method

Example:

Range("C5").Select

The statement above moves the cursor to cell C5.  Range(“C5”) is the object, and Select is the method.

Example:

ActiveWorkbook.Save

The statement above saves the active workbook.  ActiveWorkbook is the object and Save is the method.

Example:

Range("A1:F10").Clear

The statement above clears the contents of all the cells between A1 and F10.  Range(“A1:F10”) is the object and Clear is the method.

Arguments are like Adverbs

In English, an adverb describes how a verb is executed. For example, a track coach may tell an athlete to run quickly. Quickly is the adverb that describes how the verb, run, is to be executed. Similarly, in VBA, an argument describes how a method is to be executed.  VBA allows for two syntaxes where arguments are used.  The first includes the name of the argument, the second does not.

Syntax 1:

Object.Method ArgumentName:=Value

Syntax 2:

Object.Method Value

Example 1:

Workbooks(“January 2021.xlsx”).SaveAs FileName:="D:\Archive\January 2021.xlsx"

Example 2:

Workbooks(“January 2021.xlsx”).SaveAs "D:\Archive\January 2021.xlsx"

The examples above execute the SaveAs method with the FileName argument, that tells Excel where to save the file.  Notice that Workbooks(“January 2021.xlsx”) is the object, SaveAs is the method, FileName is the name of the argument, “D:\Archive\January 2021.xlsx” is the value of the argument.  In the first syntax the FileName argument is specified.  In the second syntax the FileName argument is implied.

Notice in both examples the  value of the argument is included. Because the argument value is text, is it enclosed in quotation marks.  If the value is numeric, quotation marks are not used.  Also note, when the argument name is included it is separated from its value not by a mere equal sign, but by a combination of a colon and an equal sign.This blog post has documented the basic syntax of VBA code. As shown in the following summary table, the objects, properties, methods, and arguments are similar to the English terms noun, adjective, verb and adverb. We have also seen that unlike English, in VBA the object is listed first, followed by a period, and then followed by a property or a method.

I hope you found this blog post helpful. If you would like additional help editing your VBA code, please contact us.