Today's Goals

Understand what objects are in VBA
Access object properties and methods with confidence
Get to know the Excel object model hierarchy

Key Concepts

1. The Excel Object Model

Application
  └─ Workbooks (collection)
      └─ Workbook
          └─ Worksheets (collection)
              └─ Worksheet
                  └─ Range

A helpful way to picture the VBA object model: think of Excel as a company. Application is the company itself, Workbook is a project folder, Worksheet is a sheet of paper inside it, and Range is a cell on that sheet. To work on a cell you navigate down the hierarchy one level at a time — that is exactly where long references like Workbooks(...).Worksheets(...).Range(...) come from.

2. Accessing Objects

The dot . operator: object.property or object.method
Properties are an object's data (e.g. Range.Value); methods are its actions (e.g. Worksheets.Add)
Collections can be indexed by name, Worksheets("Sheet1"), or by position, Worksheets(1)

3. Common Objects

Application: the entire Excel application
Workbook: one workbook
Worksheet: one worksheet
Range: a cell or a block of cells

Note: ThisWorkbook is the workbook the code lives in, while ActiveWorkbook is whichever workbook is currently active — and the user can switch it at any moment. In serious scripts, prefer ThisWorkbook plus explicit sheet names.

4. Range vs. Cells

Range("A1") suits hard-coded addresses; Cells(row, column) accepts variables, which makes it the natural choice inside loops.

Today's Code

vba
Sub ObjectExample()
    ' Access the Application object
    Application.Name
    Application.Visible = True

    ' Access a Workbook object
    ThisWorkbook.Name
    ActiveWorkbook.Path

    ' Access a Worksheet object
    Worksheets("Sheet1").Name
    ActiveSheet.Range("A1").Value = "Hello"

    ' Access a Range object
    Range("A1").Value = "Hello"
    Cells(1, 1).Value = "Hello"
End Sub

When you set several properties on the same object in a row, use With ... End With: lift the object out once, then start each inner line with a dot. The real-world scenario below does exactly that.

Real-World Scenario

To give cell A1 of every worksheet the same title formatting:

vba
Sub FormatAllTitles()
    Dim ws As Worksheet
    For Each ws In Worksheets
        With ws.Range("A1")
            .Value = "Monthly Report"
            .Font.Bold = True
            .Font.Size = 14
            .Interior.Color = RGB(255, 200, 200)
        End With
    Next ws
End Sub

ws holds a reference to each sheet in turn, so .Range("A1") always acts on that particular sheet, regardless of which one happens to be active.

Exercises

Open a specific workbook
Add a new worksheet and name it
Set the value and format of cell A1

Exercise Solutions

Use Workbooks.Open "file path" to open a specific workbook.
Create the sheet with Worksheets.Add, then name it with Sheets(1).Name = "NewName".
Set the value with Range("A1").Value = "content" and the format with Range("A1").Font.Bold = True.

Common Errors and Troubleshooting

Run-time error '9': Subscript out of range — the sheet name doesn't exist or differs by a stray space. Confirm the real name with ?Worksheets(1).Name in the Immediate Window.
Run-time error '424': Object required — a Set was missing when assigning an object variable: ws = Worksheets("Sheet1") should be Set ws = ....
Run-time error '1004': Application-defined or object-defined error — the Range address is invalid, such as Range("A0"). Use Cells for dynamic positions.
Data landed on the wrong sheet — an unqualified Range("A1") points at the active sheet by default, so write out the full reference chain.

Extension Exercises

Loop through the Workbooks collection and list every open workbook with MsgBox.
Use a nested loop plus Cells to fill A1:E5 with row-times-column products, and bold the diagonal.
Rework the real-world scenario so it only processes sheets whose names start with a given prefix such as "Mo" (hint: Left(ws.Name, 2)).

由在线工具箱(www.vba.net)整理制作

🌱 Day06 - Introduction to the Object Model