The Worksheet Object

A sheet is a thing, not a word

When you work by hand you click a tab and start typing. VBA has no hands, so it needs a way to say which sheet it means. That way is the Worksheet object. Every workbook owns a collection called Worksheets, and you pick one out of it either by its tab name, Worksheets("Data"), or by its position, Worksheets(1).

Worksheets or Sheets?

• Worksheets — only normal grid sheets. This is what you want 95% of the time.
• Sheets — grid sheets and chart sheets mixed together, so Sheets(2) might not be a grid at all.
• By name — Worksheets("Data") reads well, but it breaks the moment a colleague renames the tab.
• By index — Worksheets(1) is just the leftmost tab, so it breaks the moment someone drags a tab.

Sub ShowSheetFacts()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")

    Debug.Print "Tab name: " & ws.Name
    Debug.Print "Position: " & ws.Index
    Debug.Print "Last row: " & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Debug.Print "Used range: " & ws.UsedRange.Address
End Sub

The code name is the stable one

Open the Project Explorer in the VBA editor and you will see entries like Sheet1 (Data). The part in brackets is the tab name the user sees. The part in front, Sheet1, is the code name, and you can type it straight into your code as if it were a variable: Sheet1.Range("A1").Value = 10. The huge advantage is that renaming the tab does not touch the code name, so your macro keeps working. You can rename the code name yourself in the Properties window (press F4) to something meaningful like shData.

Why this matters

Almost every broken macro in the world breaks because it lost track of which sheet it was pointing at. Getting a solid, named reference into a variable on line one of your procedure is the single cheapest habit you can adopt, and the rest of this course builds on it.