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:
ThisWorkbookis the workbook containing the VBA code. If a macro is stored in the file it should edit, this is often the safest choice.ActiveWorkbookis 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.
- In the Visual Basic Editor, choose Insert > Module to create a standard module.
- Declare variables using the object types you need:
Workbookfor a file andWorksheetfor a grid sheet. - Assign the workbook with
Set, using the reference that matches your goal. - Assign the worksheet from that workbook’s
Worksheetscollection, using its tab name or index. - 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 theSetline 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")orWorksheets("Data")without a workbook qualifier can depend on the active workbook or sheet. Usews.Range("A1")orwb.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
Worksheetvariable. - The code compiles but targets the wrong file: Reconsider whether the macro should use
ThisWorkbookorActiveWorkbook. 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
- Excel Data Validation Date Picker: Setup and Options
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Data Dictionary: How to Create and Use OneA VBA data dictionary is usually the Scripting.Dictionary object: a key-and-value store that lets a macro look up…
- Access VBA Match Function: What It Does and How to Use ItMicrosoft Access does not provide Excel’s worksheet MATCH function as a native VBA function. To find a value…
- VBA MsgBox: Create an OK or Decline Box?Yes. In VBA, use MsgBox with the vbOKCancel button style to show OK and Cancel, or vbYesNo to…
- VBA MsgBox Carriage Return: Add Line BreaksTo put a carriage return and line break in a VBA MsgBox, join the message parts with vbCrLf:…
- VBA InputBox New Line: Add Line Breaks to PromptsTo put a new line in a VBA InputBox prompt, join the prompt text with vbCrLf or vbNewLine.…
- Excel VBA Copy and Paste Formatting: Code and TipsTo copy only formatting with Excel VBA, copy the source range and use PasteSpecial with xlPasteFormats: sourceRange.Copy, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.