Set Workbook and Worksheet in VBA: How It Works

VBA & macros · Updated November 18, 2026

In Excel VBA, use Set to assign a workbook or worksheet object to a variable, then refer to that variable in your code. For example, declare wb As Workbook and ws As Worksheet, then use Set wb = ThisWorkbook and Set ws = wb.Worksheets("Data").

What Set means in Excel VBA

A workbook and a worksheet are objects: they represent parts of Excel that have properties and methods, such as a workbook’s name or a worksheet’s cells. In VBA, Set assigns an object reference to a variable. It does not copy the workbook or worksheet, create a new sheet, or change the selected sheet. It tells VBA which existing object the variable should refer to.

That is different from assigning an ordinary value. For example, a variable declared as Long can receive a number with a regular assignment statement, while a variable declared as Worksheet needs Set. If you omit it when assigning an object, VBA reports an error.

Once assigned, the variable is a convenient way to identify the object in later statements. Instead of repeating a long reference, you can write ws.Range("A1").Value = "Complete". The worksheet variable still refers to the same sheet unless your code assigns it a different worksheet or clears it.

Workbook and worksheet are different objects

A workbook is the Excel file as a whole. It can contain worksheets, chart sheets, and other content. A worksheet is one grid-based tab within a workbook. VBA exposes workbook objects through the Workbooks collection and worksheet objects through a workbook’s Worksheets collection.

Keeping the two levels separate helps prevent mistakes. First identify the workbook, then identify the worksheet inside it. The reference wb.Worksheets("Data") means the worksheet named Data in the workbook referred to by wb. By contrast, Worksheets("Data") without a workbook qualifier refers to a sheet in the active workbook, which may not be the file your macro is intended to change.

Use Worksheet and Worksheets when working with grid sheets. The broader Sheets collection can also contain chart sheets. A variable declared as Worksheet cannot refer to a chart sheet, so choosing the right object type makes your intent clearer.

Choose the right workbook reference

Three workbook references are especially common, and they are not interchangeable:

  • ThisWorkbook is the workbook containing the VBA code. If a macro is stored in the file it should edit, this is often the safest choice.
  • ActiveWorkbook is the workbook currently active in Excel. It can change when a user switches files or when a macro opens another workbook.
  • Workbooks("Report.xlsx") refers to an open workbook by its name. The name must match an item in Excel’s open-workbook collection.

For example, if a macro is stored in Personal.xlsb but should edit the report currently in front of the user, ActiveWorkbook may be appropriate. If the macro should always edit the workbook where its code is saved, use ThisWorkbook. A workbook’s name alone is not a guarantee that it is open; trying to look up a closed file in Workbooks raises an error.

When code opens a file, capture the workbook returned by Workbooks.Open. This avoids relying on which file happens to become active after the operation.

Set a workbook and worksheet step by step

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, use Developer > Visual Basic; the keyboard shortcut can depend on the keyboard and macOS settings. If the Developer tab is not visible on Windows, enable it through File > Options > Customize Ribbon and select Developer.

  1. In the Visual Basic Editor, choose Insert > Module to create a standard module.
  2. Declare variables using the object types you need: Workbook for a file and Worksheet for a grid sheet.
  3. Assign the workbook with Set, using the reference that matches your goal.
  4. Assign the worksheet from that workbook’s Worksheets collection, using its tab name or index.
  5. Use the variables to read or change cells, and check that the named sheet exists before relying on it.

This example refers to the workbook containing the macro and its Data worksheet. The procedure writes a value to cell A1.

Option Explicit

Sub SetWorkbookAndWorksheet()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Refer to the workbook that contains this VBA code.
    Set wb = ThisWorkbook

    ' Refer to the worksheet named Data in that workbook.
    Set ws = wb.Worksheets("Data")

    ' Write a value to the selected worksheet.
    ws.Range("A1").Value = "Ready"
End Sub

Before running a macro that changes workbook contents, save a copy of the workbook. VBA changes generally cannot be reversed with Ctrl+Z, so a backup is the simplest way to recover from an unintended edit. On Mac, the equivalent undo shortcut is Command+Z, but it does not undo macro actions either.

Use a worksheet variable safely

Qualify worksheet references through the workbook variable whenever the target matters. For example, wb.Worksheets("Data").Range("A1") is explicit, while Range("A1") depends on the active sheet. Assigning a worksheet to ws makes that explicit reference shorter without making it dependent on the user’s selection.

Worksheet names are text and must match the tab name, including spaces and punctuation. If the tab is named Monthly Data, use wb.Worksheets("Monthly Data"). A sheet’s position can also be used, as in wb.Worksheets(1), but position-based references may silently point to a different sheet after someone reorders the tabs. Names are generally easier to maintain when they are stable.

A With block is useful when several statements address the same worksheet. The leading period on each member reference means “use the object named after With.”

Option Explicit

Sub FormatDataSheet()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Set explicit workbook and worksheet references.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")

    ' Apply several changes to the same worksheet.
    With ws
        .Range("A1").Value = "Status"
        .Range("A1").Font.Bold = True
        .Columns("A").AutoFit
    End With
End Sub

Set a worksheet in another open workbook

To work with another workbook that is already open, assign it by name and then select a worksheet from it. The workbook name shown in Excel usually includes the file extension. If two open workbooks have the same name, or the workbook is not open, a name lookup may not identify the file you expect.

Option Explicit

Sub UseOpenWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' This workbook must already be open in Excel.
    Set wb = Workbooks("Quarterly.xlsx")
    Set ws = wb.Worksheets("Summary")

    ' Read a value from the specified sheet.
    Debug.Print ws.Range("B2").Value
End Sub

If the file is not open, open it and assign the returned object. Change the example path to a real file location before running it. Opening files can trigger security prompts or link updates depending on the file and Excel settings.

Option Explicit

Sub OpenAndReferenceWorkbook()
    Dim wb As Workbook
    Dim ws As Worksheet

    ' Open the file and retain its workbook object.
    Set wb = Workbooks.Open(Filename:="C:\Reports\Quarterly.xlsx")
    Set ws = wb.Worksheets("Summary")

    ' Place the workbook and sheet names in the Immediate window.
    Debug.Print wb.Name
    Debug.Print ws.Name
End Sub

This sample leaves the workbook open. If your procedure should close a workbook afterward, decide deliberately whether to save changes; closing with changes discarded can lose edits, while saving can overwrite a file. Do not add a close command until the intended outcome is clear.

Common errors and how to fix them

  • “Object variable or With block variable not set” (error 91): The object variable was declared but never successfully assigned, or it was set to Nothing. Check that the code reaches the Set line and that the assignment succeeds before using the variable.
  • “Subscript out of range” (error 9): A workbook or worksheet lookup usually uses a name that does not match an open workbook or existing tab. Check spelling, spaces, file extension, and whether the workbook is open.
  • Changes affect the wrong sheet: A reference such as Range("A1") or Worksheets("Data") without a workbook qualifier can depend on the active workbook or sheet. Use ws.Range("A1") or wb.Worksheets("Data").
  • “Object required” or a type mismatch: Confirm that the variable is declared as the correct object type. A chart sheet is not a worksheet, for example, and cannot be assigned to a Worksheet variable.
  • The code compiles but targets the wrong file: Reconsider whether the macro should use ThisWorkbook or ActiveWorkbook. Their meanings differ when code is stored in an add-in or Personal.xlsb.

Adding Option Explicit at the top of a module requires variables to be declared. It helps catch misspelled variable names, but it does not verify that a workbook or sheet exists. For macros that must handle missing tabs gracefully, use an error-handling routine to test for the sheet and respond with a clear message rather than allowing the procedure to stop unexpectedly.

What happens when you use Set Nothing?

Set ws = Nothing removes the variable’s reference to a worksheet object. It does not delete the sheet, clear its cells, or close the workbook. In most short procedures, VBA releases local object references when the procedure ends, so explicitly setting them to Nothing is usually unnecessary. It can still be useful when you want to make clear that a variable should no longer be used or when managing longer-lived references.

Do not confuse releasing a reference with closing a file. Closing a workbook is a separate operation, and it can prompt to save changes or discard them depending on the code and workbook state. Treat the workbook reference and the open file as related but distinct things.

Frequently Asked Questions

Do I need Set for a workbook or worksheet variable?
Yes. Workbook and worksheet variables hold object references, so assign them with Set. Ordinary values such as numbers and text are assigned without Set.

What is the difference between ThisWorkbook and ActiveWorkbook?
ThisWorkbook is the file containing the VBA code. ActiveWorkbook is the file currently active in Excel. Choose based on which workbook the macro should actually use.

How do I refer to a worksheet by name?
First set a workbook variable, then use its worksheet collection, such as Set ws = wb.Worksheets("Data"). The quoted name must match the sheet tab exactly.

Can I use Set to create a new worksheet?
No. Set assigns a reference to an object; it does not create one. To add a sheet, use the workbook’s Worksheets.Add method and assign the returned worksheet object to a variable.

Can I undo a VBA macro with Ctrl+Z?
No. Excel’s undo history generally does not reverse changes made by a macro. Save a backup copy before running code that changes data or formatting.

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.