Excel VBA Dim ws As Worksheet: Meaning and Examples

VBA & macros · Updated November 21, 2026

Dim ws As Worksheet declares a VBA variable named ws that can refer to an Excel worksheet; it does not select or assign a sheet by itself. To use it, assign a worksheet object with Set, such as Set ws = ThisWorkbook.Worksheets("Sheet1").

What the declaration means

In VBA, Dim introduces a variable, and As Worksheet specifies its data type. Here, ws is a variable intended to hold a reference to a worksheet object. The name is only a label chosen by the programmer: ws is a common shorthand for “worksheet,” but names such as reportSheet or sourceWs work too.

A declaration establishes what kind of value the variable can hold, but it does not identify a workbook or a particular tab. Immediately after the declaration, ws has no worksheet assigned to it. Trying to use a member such as ws.Range("A1") before assigning a worksheet can cause the “Object variable or With block variable not set” runtime error.

Worksheet is an Excel object type. Once a worksheet is assigned, the variable can be used to access that sheet’s cells, ranges, names, and other worksheet properties and methods. For example, ws.Name refers to its tab name, while ws.Cells(1, 1) refers to cell A1 on that worksheet.

How to assign a worksheet with Set

Because a worksheet is an object, VBA uses the Set statement to assign it to an object variable. The usual sequence is to declare the variable, assign the intended sheet, and then use the variable:

Sub ShowSheetName()
    Dim ws As Worksheet

    ' Assign the worksheet in the workbook containing this code.
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ' Display the assigned worksheet's tab name.
    MsgBox ws.Name
End Sub

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, so a backup is useful even when a short example appears harmless. In the example, ThisWorkbook means the workbook that contains the VBA code. The text "Sheet1" must match the actual worksheet tab name, including spaces and punctuation.

Use Set for object references such as worksheets and ranges. It is not used when assigning ordinary values such as numbers or text. For example, ws.Name can be read as text, but assigning a worksheet to ws requires Set.

A practical example: write to a specific sheet

Once ws refers to a sheet, qualifying a range with that variable makes the target unambiguous. In the following macro, the text is written to cell A1 on the worksheet named “Summary” in the workbook containing the code:

Sub WriteSummaryHeading()
    Dim ws As Worksheet

    ' Select the intended worksheet by its tab name.
    Set ws = ThisWorkbook.Worksheets("Summary")

    ' Write to A1 on that sheet, not on whichever sheet is active.
    ws.Range("A1").Value = "Monthly summary"
End Sub

Save a copy of the workbook before running this macro; macros cannot be reversed with Ctrl+Z. The important detail is ws.Range("A1"). Writing Range("A1").Value without a worksheet qualifier generally acts on the active sheet, which may not be the sheet the macro author intended. A qualified reference is easier to read and less likely to change behavior when a user clicks a different tab.

VBA also supports a With block when several statements refer to the same worksheet. The leading dot on each member means “use the object named after With.” This can make longer procedures less repetitive, but the worksheet still needs to be assigned first:

Sub FormatSummaryHeading()
    Dim ws As Worksheet

    ' Assign the sheet before using it.
    Set ws = ThisWorkbook.Worksheets("Summary")

    ' Apply several settings to the same cell.
    With ws.Range("A1")
        .Value = "Monthly summary"
        .Font.Bold = True
        .ColumnWidth = 24
    End With
End Sub

Save a copy before running the macro because changes made by VBA cannot be undone with Ctrl+Z. The With block here refers to a range on the assigned worksheet, not to the worksheet variable itself.

Choose the right workbook and sheet reference

Choosing a worksheet by name is only part of choosing the right target. A workbook can be open alongside several others, so the workbook reference matters too. ThisWorkbook points to the workbook containing the running code. ActiveWorkbook points to the workbook currently active in Excel; it can be a different file. For a macro stored in the workbook it edits, ThisWorkbook is often the safer choice. A macro stored in Personal.xlsb or an add-in may need to work on ActiveWorkbook instead, because ThisWorkbook would refer to the personal macro workbook or add-in.

You can also use the worksheet’s position, for example Worksheets(1), but that means the first worksheet in the collection, not necessarily a permanently named business role. Inserting or reordering tabs can make a positional reference point somewhere different. A tab-name reference is clearer when the macro depends on a specific named sheet.

A worksheet also has a VBA code name, visible in the Project Explorer and Properties window in the Visual Basic Editor. A code name can be used directly in VBA and does not change when a user renames the visible tab. That can be useful in a workbook you control, but a name-based reference is often easier to understand when sharing a short example. Regardless of the method, make sure the reference identifies the intended workbook and sheet.

Worksheet versus Sheets and other variable types

Worksheet refers specifically to a worksheet: a tab made up of rows and columns of cells. Excel’s Sheets collection can also include chart sheets, which are separate tabs containing a chart rather than a cell grid. Therefore, use a Worksheet variable when the code needs worksheet features such as Range or Cells.

If code needs to refer to any kind of sheet tab, including chart sheets, an object variable declared as Sheet or a broader Object may be relevant, but the available members differ. A chart sheet does not behave like a worksheet and does not provide the same cell-range interface. For ordinary data work, Dim ws As Worksheet is generally clearer and catches some incompatible assignments rather than accepting an unrelated object.

Other common declarations include Dim wb As Workbook for a workbook and Dim rng As Range for a cell range. Using a specific type helps communicate intent and enables VBA’s editor to offer relevant members as you type. It also makes code easier to maintain than declaring every variable as Object.

Scope, naming, and Option Explicit

Where you put the declaration determines how long the variable exists and where it can be used. A Dim ws As Worksheet inside a procedure is local to that procedure. Once the procedure finishes, the local variable is no longer available to other procedures. If several procedures need a shared module-level variable, it can be declared in the declarations section at the top of a module, outside any procedure; broader scope should be used deliberately because shared state can make a project harder to follow.

Adding Option Explicit at the top of a module requires variables to be declared before use. This is useful for catching misspellings such as accidentally typing wss instead of ws. In the Visual Basic Editor, the setting that inserts this statement in new modules is under Tools > Options on Windows. Even when it is not enabled automatically, writing it at the top of a module is a sound practice in VBA projects.

Choose descriptive variable names when a procedure handles several worksheets. For example, Dim inputWs As Worksheet and Dim outputWs As Worksheet are more informative than two variables called ws and ws2. Variable names are not case-sensitive in VBA, so ws and WS refer to the same name.

Common errors and how to fix them

  • “Object variable or With block variable not set.” The variable was declared but not assigned, or it was set to Nothing. Assign a valid worksheet with Set ws = ... before using ws.Range or another member.
  • “Subscript out of range.” The workbook’s Worksheets collection does not contain the requested tab name or index. Check the exact visible tab name, the workbook being referenced, and whether the sheet exists.
  • Type mismatch when assigning ActiveSheet. ActiveSheet can refer to a chart sheet as well as a worksheet. If a chart tab is active, it cannot be assigned to a Worksheet variable. Activate or identify a worksheet explicitly, or test the object type before using it.
  • The macro changes the wrong file. This often comes from confusing ThisWorkbook with ActiveWorkbook. Decide whether the code should target the file containing the macro or the currently selected workbook, then qualify the reference accordingly.
  • Unqualified ranges behave unexpectedly. A bare Range or Cells reference can depend on the active sheet. Prefer ws.Range(...) or ws.Cells(...) when the operation belongs to a particular worksheet.

When a macro should handle a missing worksheet gracefully, it can check for the sheet before proceeding rather than allowing a failed lookup to stop the procedure. Error handling should be limited and intentional; using On Error Resume Next across a long section can hide unrelated problems. For a small macro, it may be clearer to make sure the expected sheet exists and report a useful message if it does not.

Where to enter and run the VBA

Excel for the web can open a workbook containing macros, but it cannot create, edit, or run VBA macros. Use the desktop Excel application. On Windows, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic if the Developer tab is visible. To run a procedure from Excel, press Alt+F8, select the macro, and choose Run. If the Developer tab is not shown, enable it through File > Options > Customize Ribbon.

On Excel for Mac, use Tools > Macro > Visual Basic Editor to open the editor; keyboard shortcuts can vary with the Mac model and function-key settings. Excel for Mac supports VBA, but ribbon layouts and some operating-system interactions differ from Windows. Macro availability may also be governed by security settings, so only enable or run code from sources you trust. Save the workbook in a macro-enabled format such as .xlsm if it needs to retain VBA code.

Frequently Asked Questions

Does Dim ws As Worksheet select the active sheet?

No. It only declares a worksheet variable. To refer to the active sheet, assign it explicitly, for example with Set ws = ActiveSheet, while accounting for the possibility that the active tab is a chart sheet. To target a known worksheet, use a workbook-qualified reference such as Set ws = ThisWorkbook.Worksheets("Summary").

Why does VBA require Set for a worksheet?

A worksheet is an object, so VBA uses Set to assign an object reference to an object variable. Ordinary values such as text and numbers are assigned without Set. The declaration and assignment are separate operations: Dim declares the variable, and Set connects it to the worksheet.

Can I use a different name instead of ws?

Yes. ws is a convention, not a required name. A descriptive name such as salesWs or archiveSheet can make code easier to understand, especially when a procedure refers to more than one worksheet.

Should I use Worksheets or Sheets?

Use Worksheets when you specifically need a grid-based worksheet. Use the broader Sheets collection only when code is intended to include other sheet types, such as chart sheets. A variable declared As Worksheet is the appropriate choice for code that works with cell ranges.

What is the difference between ThisWorkbook and ActiveWorkbook?

ThisWorkbook is the workbook containing the VBA code. ActiveWorkbook is the workbook currently active in the Excel window. They can be different, particularly when code is stored in Personal.xlsb or an add-in, so choose the reference that matches the file the macro is meant to edit.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.