In Excel VBA, use Dim ws As Worksheet to declare a variable that can refer to a worksheet; then use Set ws = ThisWorkbook.Worksheets(“Data”) to assign a particular sheet to it. Dim reserves and types the variable, but it does not select or create a worksheet by itself.
What Dim Means in VBA
Dim is a VBA statement for declaring a variable. In this case, ws is the variable name and Worksheet is its data type: the variable is intended to hold a reference to an Excel worksheet object. You can choose another valid variable name, such as reportSheet or sourceWs. A descriptive name can make longer procedures easier to read.
Declaring a variable is useful because you can refer to the same sheet repeatedly without writing its full workbook-and-sheet reference every time. The variable can then be used with worksheet properties and methods, such as Name, Cells, Range, and UsedRange. It also helps VBA check that you are using a value of the expected kind.
A common point of confusion is that Dim does not add a sheet to the workbook, find a sheet by name, or make that sheet active. It only declares the variable. A worksheet object must be assigned separately with Set before you use it. This distinction matters because an unassigned object variable does not yet refer to an actual sheet.
Declare the Variable and Assign a Worksheet
For an object variable, VBA uses the Set statement to make the assignment. A basic procedure looks like this:
Option Explicit
Sub ShowDataSheetName()
Dim ws As Worksheet
' Assign the sheet named Data in the workbook containing this code.
Set ws = ThisWorkbook.Worksheets("Data")
MsgBox ws.Name
End Sub
Here, ThisWorkbook means the workbook that contains the VBA project, and Worksheets(“Data”) identifies the worksheet by its tab name. Once the assignment succeeds, ws.Name returns the sheet’s name, so the message box displays Data. If that workbook has no worksheet with that name, the assignment produces a run-time error rather than silently creating a sheet.
Use Option Explicit at the top of a module to require variable declarations. This catches many misspellings: without it, a typo in a variable name can be treated as a new, empty Variant variable instead of an obvious coding mistake. In the Visual Basic Editor, you can set new modules to include it by choosing Tools > Options and selecting Require Variable Declaration.
Why Set Is Required
Worksheet is an object type, not a simple value such as a number or a string. VBA therefore uses Set to assign an object reference. For example, a statement assigning a worksheet to ws begins with Set. By contrast, assigning a text value to a cell uses an ordinary assignment statement, such as ws.Range(“A1”).Value = “Reviewed”; the Range property supplies a cell object, and its Value property receives text.
It is possible to declare a worksheet variable and never assign it. If code then tries to use a property such as ws.Name, VBA generally raises run-time error 91, “Object variable or With block variable not set.” The fix is usually to check that the Set statement ran successfully and that it is reached before the variable is used. Setting an object variable to Nothing explicitly releases its reference, but this is rarely necessary for a short procedure that ends normally.
Choose the Right Workbook Reference
Correctly identifying the workbook is just as important as identifying the sheet. Excel exposes several workbook references, and they do not mean the same thing:
- ThisWorkbook is the workbook containing the running VBA code. It is often the clearest choice for a macro stored in the workbook it should edit.
- ActiveWorkbook is the workbook currently active in Excel. It can differ from ThisWorkbook, for example when a macro is stored in the Personal Macro Workbook or an add-in.
- ActiveSheet is the currently active sheet or chart sheet. It may not be the sheet the procedure is intended to use, and it is not guaranteed to be a worksheet.
If a macro should operate on a particular open workbook, declare a Workbook variable, assign the intended workbook to it, and then access its Worksheets collection. If the user can open several workbooks, avoid assuming that ActiveWorkbook will always be the correct one. A macro that runs from an add-in, for instance, may need ActiveWorkbook because ThisWorkbook points to the add-in itself; the intended choice depends on where the code lives and what it should change.
Use the Worksheet Variable in a Procedure
After assignment, the variable can make repeated references shorter and easier to audit. This example puts a heading in cell A1 on the Data worksheet in the workbook containing the code:
Option Explicit
Sub AddDataHeading()
Dim ws As Worksheet
' Point to the intended worksheet before using it.
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A1").Value = "Reviewed"
ws.Range("A2").Value = Date
End Sub
In this example, ws.Range(“A1”) and ws.Range(“A2”) are both unambiguously on the assigned worksheet. Without ws, a reference like Range(“A1”) can depend on whichever sheet is active at the time, which can lead to changes in the wrong place. Object qualification is a practical way to make macros more predictable.
Before running a macro that changes a workbook, save a copy of the file. VBA macro actions generally cannot be reversed with Ctrl+Z, so a backup gives you a recovery option if the code changes an unintended cell or sheet.
Worksheet Names, Sheet Tabs, and Code Names
Excel’s Worksheets collection contains worksheet tabs, while the broader Sheets collection can also include chart sheets. Use a variable declared As Worksheet when you intend to work with a worksheet containing cells. If you use Sheets(“Summary”) and that tab is a chart sheet, assigning it to a Worksheet variable can fail because a chart sheet is not a worksheet.
The text in Worksheets(“Data”) is the worksheet’s tab name, which users can change. A worksheet also has a VBA code name, shown in the Visual Basic Editor’s Project Explorer and Properties window. A reference using the code name can survive a change to the visible tab name, but code names are project-specific and may be less obvious to someone maintaining the workbook. For general-purpose code, using the tab name is often easier to understand; for a fixed sheet in a controlled workbook, a code name can be convenient.
Do not confuse a worksheet’s Name property with its CodeName property. Name is the visible tab name. CodeName is its VBA identifier. Assigning a worksheet to ws lets you use either property to inspect the sheet, but most readers expect Worksheets(“Data”) to refer to the tab named Data.
Common Errors and How to Avoid Them
- “Subscript out of range”: The workbook does not contain the requested sheet under that exact tab name, or the code is looking in a different workbook. Check spelling, spaces, punctuation, and whether the reference should be ThisWorkbook or ActiveWorkbook.
- “Object variable not set”: The variable was declared but not assigned, or the Set statement was skipped. Assign it before accessing its properties.
- Wrong sheet changed: The code relied on ActiveSheet or an unqualified Range reference. Assign a specific worksheet and qualify cell references through that variable.
- Type mismatch: The selected item may be a chart sheet rather than a worksheet, or the object reference is otherwise incompatible with the declared type. Use Worksheets for cell-based sheets and Sheets only when chart sheets are intentionally included.
- Variable name conflicts or typos: Use a clear name and Option Explicit. VBA does not care about capitalization, but a misspelled name is still a different identifier.
For code that must handle a missing sheet gracefully, you can add error handling, but do not use broad error suppression as a substitute for checking the workbook and tab name. A simple first step is to confirm the sheet exists in the workbook you expect, then run the macro again. More elaborate procedures can search the Worksheets collection and report a useful message if the target is absent.
Where to Enter and Run the VBA
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module, paste a procedure into the module, and run it with the cursor inside the procedure by pressing F5, or return to Excel and use Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On a Mac, use Developer > Visual Basic to open the editor; the keyboard shortcut can depend on the keyboard and function-key settings, so the ribbon command is a reliable route. Excel for the web can open workbooks containing VBA but cannot create, edit, or run VBA macros. Use the desktop Excel application for those tasks. Macro security settings may also prevent a macro from running; only enable macros in files you trust.
Frequently Asked Questions
What is the correct syntax for Dim worksheet in VBA?
For a worksheet object variable, declare it as Dim ws As Worksheet. The name ws is only an example; you can choose a different clear identifier. You must then assign an actual worksheet with a Set statement before using the variable.
Can I write Dim Worksheet As Worksheet?
That may be accepted as a variable declaration, but it is confusing because Worksheet is also the object’s type name. Prefer a distinct variable name such as ws or reportSheet so the code is easier to read and less prone to confusion.
Do I need Set for a worksheet variable?
Yes. A worksheet is an object, so assign it with Set, for example Set ws = ThisWorkbook.Worksheets(“Data”). Dim declares the variable; Set connects it to the sheet object.
What is the difference between ThisWorkbook and ActiveWorkbook?
ThisWorkbook is the file containing the VBA code. ActiveWorkbook is the workbook currently active in Excel. They can be different, so choose the reference that matches the workbook the macro is supposed to use.
Can I use Dim ws As Worksheet in Excel for the web?
You can store a workbook that contains VBA in Excel for the web, but the web app does not run or edit VBA macros. Open the workbook in the desktop Excel application to work with the Visual Basic Editor and run the macro.
Related
- Power Query Union Tables: How to Append Queries
- Data Validation: Definition, Rules, and Examples
- Excel Data Validation Date Picker: Setup and Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Consolidate Excel Files into One WorkbookTo consolidate matching Excel files into one worksheet, place the macro below in the workbook you want to…
- VBA Array Length: Count Elements and Find BoundsIn VBA, calculate an array’s length as UBound(array) - LBound(array) + 1. The expression works for a one-dimensional…
- VBA Code to Copy and Paste Values in ExcelTo copy a range’s results without its formulas, use VBA’s PasteSpecial with xlPasteValues; for a fast transfer between…
- Get Workbook Path in VBA: FullName vs PathTo get a workbook’s folder in VBA, read its Path property; to get the complete location including the…
- VBA Range.Intersect: Find Overlapping CellsIn VBA, use Application.Intersect(range1, range2) to find the cells shared by two or more ranges. It returns a…
- Google Sheets Macros: How to Create, Run, and Edit ThemYes. You can create macros in Google Sheets using its built-in recorder: in a desktop web browser, choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.