Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 37 ยท Objects and Collections

Objects, Properties, Methods and the Object Model

Understand VBA objects: properties, methods, collections, the Application-Workbook-Worksheet-Range hierarchy, qualifying references and the Object Browser.

Everything in Office VBA revolves around objects. An object is a thing the application knows about: a workbook, a worksheet, a cell range, a Word paragraph, an Outlook mail. Objects have properties (what they are), methods (what they can do) and sometimes events (what they notice). Once this model clicks, any Office application becomes approachable.

Properties and methods

VB
Range("A1").Value = 42              ' property: set a value
Range("A1").Font.Bold = True        ' property of a child object
Debug.Print Range("A1").Address     ' property: read ($A$1)
Range("A1").ClearContents           ' method: do something
Range("A1:A5").Sort Key1:=Range("A1")   ' method with arguments
  • A property is a noun: Value, Name, Count, Color. You read it (x = obj.Prop) or set it (obj.Prop = x). Some are read-only.
  • A method is a verb: Select, Copy, Save, Close, Delete. Arguments follow the name; with a return value you need parentheses.
  • The dot (.) walks from an object to what belongs to it.

The hierarchy

Objects live in a containment tree. In Excel:

flowchart TD
    A["Application"] --> B["Workbooks (collection)"]
    B --> C["Workbook"]
    C --> D["Worksheets (collection)"]
    D --> E["Worksheet"]
    E --> F["Range (cells)"]
    F --> G["Font, Interior, Borders"]

Excel's main objects, from the application down to formatting.

To reach a cell fully qualified:

VB
Application.Workbooks("Sales.xlsx").Worksheets("Q1").Range("B2").Value = 100

You usually write shorter versions, and VBA fills in the missing parents:

VB
Worksheets("Q1").Range("B2").Value = 100   ' active workbook assumed
Range("B2").Value = 100                    ' active sheet of the active workbook assumed

The shortcuts depend on what is active at run time, which is fragile: if the user clicks another sheet, Range("B2") hits a different cell. Qualify explicitly in serious code.

Special references

Name Refers to
ThisWorkbook the workbook containing the running code
ActiveWorkbook the workbook the user is working in
ActiveSheet the active sheet of the active workbook
ActiveCell the current cell
Selection whatever is selected
Sheet1 (code name) a sheet by its code name, stable even if renamed

Prefer ThisWorkbook for your own file's sheets and code names for particular sheets. Use Active... only when the task is truly about what the user is looking at.

Collections

A collection is an object that holds a group of similar objects. Worksheets is a collection of Worksheet objects; Workbooks of Workbooks.

VB
Debug.Print Worksheets.Count            ' how many sheets
Debug.Print Worksheets(1).Name          ' by position (1-based)
Debug.Print Worksheets("Data").Name     ' by name
Worksheets.Add After:=Worksheets(Worksheets.Count)   ' method of the collection

Note the plural/singular pattern: Worksheets (collection) vs Worksheet (one item). Loop with For Each: see For Each and the collections lesson.

Press F2 in the editor to open the Object Browser. Choose a library (Excel, VBA, Office, stdole), pick a class on the left, and see its properties (hand icon), methods (green box) and events (lightning) on the right with a description at the bottom. F1 on a selected item opens online help. The Locals window (View > Locals Window) shows the properties of an object while you step through code.

Autocomplete helps too: type the object name and a dot, and the editor lists everything you can do with it, which only works when the variable has a specific type.

Default properties and Set

Some properties are "default", so Range("A1") alone means Range("A1").Value. Avoid relying on it; write .Value to be clear, and .Value2 when you do not want Excel to convert dates and currency.

Assigning an object to a variable needs Set; see With Blocks and Object Variables.

Stand-in: an object model in JavaScript

A JavaScript stand-in (not VBA, and not the real Excel) with a miniature hierarchy: Application > Workbooks > Worksheets > Range. You can follow every dot like in VBA.

JS Stand-in: hierarchy, properties and methods

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The two sheets show why qualifying matters: the same address B2 means different cells on different sheets.

Common mistakes

  • Relying on ActiveSheet and Selection in code that runs on any sheet.
  • Confusing the collection (Worksheets) with an item (Worksheets("Q1")).
  • Forgetting parentheses when a method returns a value, or adding them where they are not allowed (statement calls).
  • Typing object names wrong: only Excel-specific objects fail at run time, because the editor can't check them.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is a property?

    Properties describe an object; methods are actions.
  2. 2. Which is a method?

    ClearContents does something; the others read or set a property.
  3. 3. What does ThisWorkbook refer to?

    ActiveWorkbook is the one in front; ThisWorkbook is the file holding your macro.
  4. 4. What does the Object Browser open with?

    F2 opens the Object Browser; F5 runs, F8 steps.
  5. 5. Why is Range("B2") alone risky in a macro?

    Unqualified references depend on the active sheet, so qualify with the worksheet.

Summary

  • Objects have properties (nouns), methods (verbs) and events.
  • Excel's hierarchy is Application > Workbook > Worksheet > Range; the dot walks down it.
  • Collections hold similar objects; pick items by name or 1-based position.
  • Qualify references; prefer ThisWorkbook and explicit sheets to ActiveSheet and Selection.

Next: With Blocks and Object Variables