The VBA Editor: Objects, Methods, Properties and Events

The problem

A recorded macro opens the door to VBA, and the first look through it can be discouraging. The code is full of dots, With blocks and words such as ActiveSheet, PageSetup and Interior, the editor looks like software from another decade, and it is not obvious where code should be written or why the same macro sometimes works on one sheet and not on another.

The good news is that VBA, for all its size, has a small grammar. Once you know where things live in the editor and how the language names things, most code becomes readable, including the code that the recorder or an AI assistant writes for you.

The grammar: objects, properties, methods, events

VBA describes Excel as a set of objects nested inside each other. The Application (Excel itself) contains workbooks, a workbook contains worksheets, a worksheet contains ranges of cells. To reach a value, you start from the largest container and move inward, separating each level with a dot:

Workbooks("Prices.xlsm").Worksheets("Markets").Range("C2").Value

Read from left to right: in the workbook Prices.xlsm, on the worksheet Markets, cell C2, its value. The order is the reverse of how we would speak. To ask a colleague to dry the dew drop on the first leaf of the cherry tree in the garden, VBA would have to say something like Garden.Trees("Cherry").Leaves(1).Drop.Dry: the garden first, the action last. Once that inversion feels natural, most lines of VBA read easily.

Many objects come in groups called collections. Workbooks is the collection of open workbooks, Worksheets the collection of sheets in a workbook. You pick an item from a collection by name, Worksheets("Markets"), or by position, Worksheets(1), the first sheet from the left. A name is safer: positions change when someone moves a tab.

Everything you can write about an object falls into one of four classes:

• an object is a thing: a workbook, a sheet, a range, a chart;
• a property is a characteristic of an object, which you can read and often change: its value, its name, its number format, its color;
• a method is an action an object can perform: clear its contents, copy itself, close, save;
• an event is something that happens to an object and to which code can respond: a cell changes, a workbook opens, a sheet is activated.

Properties are set with an equal sign, methods are just called. Range("A1").Value = 10 changes a property; Range("A1").ClearContents runs a method.

Find your way around the editor

Press Alt+F11, or click Developer > Visual Basic, to open the Visual Basic Editor. Its design has barely changed in a quarter of a century, but it holds everything needed to write, run and fix code. Four areas matter:

• The Project pane, top left (Ctrl+R if hidden), lists every open workbook as a VBAProject. Under each one, the Microsoft Excel Objects folder holds one code module per sheet plus ThisWorkbook, and the Modules folder holds the standard modules where macros normally live.
• The Properties window, bottom left (F4), lists the properties of whatever is selected in the Project pane, such as the name of a module or of a sheet.
• The code window, on the right, is where code is written. Double-click a module to open it.
• The Immediate window, at the bottom (Ctrl+G), runs single lines of code on the spot and shows the output of Debug.Print. It is the quickest way to test an idea.

To add a module to a workbook, select its project and choose Insert > Module. Code in a standard module is saved with the workbook, as long as the file is saved as .xlsm or .xlsb.

One setting is worth changing on day one: Tools > Options > Editor > Require Variable Declaration. From then on, every new module starts with the line Option Explicit, which forces you to declare every variable before using it. Without it, a typo such as pirce instead of price silently creates a new, empty variable, and the macro produces wrong numbers without any error. With it, the code does not even start and the editor highlights the misspelled name.

The editor also helps as you type. After a dot, it lists the properties and methods available for that object. A line turns red when it contains a syntax error, as soon as you leave it. Lines starting with an apostrophe are comments, shown in green and ignored when the code runs. A long statement can be split over several lines by ending each line with a space and an underscore. And F1, with the cursor on a keyword, opens the documentation for it, while F2 opens the Object Browser, a catalog of every object, property and method.

Properties: reading and changing

In the companion file, the procedure Read_Properties reads a few properties and writes them on the sheet Object Lab:

Sub Read_Properties()
    Dim wb As Workbook, lab As Worksheet, mk As Worksheet
    Set wb = ThisWorkbook
    Set lab = wb.Worksheets("Object Lab")
    Set mk = wb.Worksheets("Markets")

    lab.Range("B3").Value = wb.Name
    lab.Range("B4").Value = wb.Worksheets.Count
    lab.Range("B5").Value = mk.Name
    lab.Range("B6").Value = mk.Range("A1").CurrentRegion.Address
    lab.Range("B7").Value = mk.Range("C2").Value
    lab.Range("B8").Value = mk.Range("C2").NumberFormat

    Debug.Print "Workbook: "; wb.Name; " | sheets: "; wb.Worksheets.Count
    Debug.Print "First price: "; mk.Range("C2").Value
End Sub

The first lines create three object variables, so that the rest of the code can say lab instead of ThisWorkbook.Worksheets("Object Lab") every time. Variables holding objects are assigned with Set; how variables work in detail is a topic of its own. Then each line reads a property on the right and writes it into a cell on the left. Count is a property of the Worksheets collection. CurrentRegion is a property that returns another object, the block of data around A1, whose Address property returns $A$1:$D$7. Column C of Object Lab shows what to expect for each one.

Changing a property uses the same syntax in the other direction. Change_Properties formats the header of Markets and the price column:

With ThisWorkbook.Worksheets("Markets")
    .Range("A1:D1").Font.Bold = True
    .Range("A1:D1").Interior.Color = RGB(193, 240, 245)
    .Range("C2:C7").NumberFormat = "#,##0.00"
End With

Note the chain Range("A1:D1").Font.Bold: Font is itself an object, a property of the range, and Bold is a property of the font. Interior is the object that holds the fill of a cell, and RGB builds a color from its red, green and blue components.

Methods: asking objects to act

Use_Methods clears the output cells of Object Lab and resizes the columns of Markets:

With ThisWorkbook.Worksheets("Object Lab")
    .Range("B3:B8").ClearContents
    .Range("D3:D20").ClearContents
End With
ThisWorkbook.Worksheets("Markets").Columns("A:D").AutoFit

ClearContents removes values and formulas but keeps formats; Clear would remove the formats as well, and Delete would remove the cells themselves and shift the rest. Methods can take arguments, written after the method name: Workbooks(1).Close SaveChanges:=False closes the first open workbook without saving. The := sign assigns a value to a named argument, which makes the call readable without counting commas.

Collections and For Each

To do something with every item of a collection, VBA offers a dedicated loop. List_Sheets writes the name of every sheet of the workbook in column D of Object Lab:

Sub List_Sheets()
    Dim sh As Worksheet, r As Long
    With ThisWorkbook.Worksheets("Object Lab")
        .Range("D3:D20").ClearContents
        r = 3
        For Each sh In ThisWorkbook.Worksheets
            .Cells(r, 4).Value = sh.Name
            r = r + 1
        Next sh
    End With
End Sub

The loop needs no counter for the sheets: For Each hands each worksheet to the variable sh in turn, from the first tab to the last. Loops in general deserve their own article; For Each is the one that fits collections best.

The Immediate window

The Immediate window is a scratchpad. Type a question mark followed by an expression and press Enter to see its value: ?ThisWorkbook.Worksheets.Count returns the number of sheets. Type a statement without the question mark and Excel executes it: Worksheets("Markets").Range("C2").Value = 400 changes the cell at once. Inside a macro, Debug.Print writes to the same window, which makes it the simplest way to follow what code is doing without filling the sheet with test values.

Events: code that runs by itself

Macros in standard modules run when someone starts them. Event procedures run when something happens. They live in the module of the object they listen to: worksheet events in the sheet's module, workbook events such as Workbook_Open in ThisWorkbook. The procedure name is fixed by VBA, so the easiest way to create one is to open the sheet module and pick the object and the event from the two drop-down lists at the top of the code window.

The sheet Event Code holds this procedure, to paste into the module of the sheet Event Demo:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range, cell As Range
    Set changed = Intersect(Target, Me.Range("B2:B7"))
    If changed Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False
    For Each cell In changed.Cells
        cell.Offset(0, 1).Value = Now
    Next cell
CleanUp:
    Application.EnableEvents = True
End Sub

Every time a cell of the sheet changes, Excel calls Worksheet_Change and passes the changed range as Target. Intersect keeps only the part of Target inside the price column B2:B7; if a change happened elsewhere, the procedure exits. For each changed price, it writes the current date and time one column to the right. Me, inside a sheet module, means the sheet itself.

The two EnableEvents lines matter. Writing the timestamp is itself a change to the sheet, which would trigger Worksheet_Change again. Switching events off while the procedure writes, and back on at the end, avoids that loop. The On Error line ensures events are switched back on even if something goes wrong in between. A price-change log of this kind, with a time stamp next to every edited price, is a simple audit trail for a file shared by a pricing team.

Common mistakes

• Writing a macro in a sheet module instead of a standard module. It runs, but a plain Range("A1") there refers to that sheet, not to the active sheet, and the macro behaves differently from the same code in a module.
• Picking sheets by position, Worksheets(1), in files where tabs get moved. Use names.
• Working without Option Explicit. A misspelled variable becomes a new empty variable and the result is silently wrong.
• Confusing ClearContents with Clear or Delete: the first keeps formats, the second wipes them, the third removes the cells.
• Switching events off and leaving them off when the code fails. Without the error handler, no event fires again until Excel is restarted or the property is set back to True.
• Typing an event procedure name by hand with a typo. The procedure never runs and no error appears; use the drop-down lists.

For further reading

• Getting started with VBA in Office - Microsoft Learn
• Option Explicit statement - Microsoft Learn