Excel VBA Dim As Workbook: Declare and Use It

VBA & macros · Updated November 21, 2026

In Excel VBA, declare a workbook variable with Dim wb As Workbook, then assign it to an open or newly created workbook with Set. The declaration creates a variable that can refer to an Excel workbook; it does not open or create a workbook by itself.

What Dim wb As Workbook means

Dim declares a variable, wb is the variable name, and As Workbook gives it the Workbook object type from Excel’s object model. Once assigned, the variable can refer to a workbook and let your code work with its worksheets, cells, names, and other workbook-level features.

For example, a workbook variable lets you write wb.Worksheets("Sheet1").Range("A1").Value instead of repeatedly identifying the workbook through a changing active-window state. The variable is a reference to the workbook object, not a copy of the file’s contents. Changing the workbook through that reference changes the actual open workbook.

In Excel’s built-in VBA editor, the Excel object model is available directly. In another host application, such as Access, a reference to the Microsoft Excel Object Library may be needed to use the Workbook type directly. The exact library version shown depends on the installed Excel version.

How to assign a workbook variable

VBA object variables are assigned with Set. A typical pattern is to declare the variable, assign it to an existing workbook, then use it:

Sub OpenReport()
    Dim reportWb As Workbook
    Dim filePath As String

    ' Update this path for the file you want to open.
    filePath = "C:\Reports\April.xlsx"
    Set reportWb = Workbooks.Open(Filename:=filePath, ReadOnly:=True)

    Debug.Print reportWb.Name
End Sub

Before running a macro that opens, changes, or saves files, save a separate copy of any important workbook. Macro actions generally cannot be reversed with Ctrl+Z. This example opens the specified file as read-only, so the code can inspect it without saving changes to that opened copy. The file path must point to a real file; if it does not, Excel raises an error.

Workbooks.Open returns a Workbook object, which is why its result can be assigned to reportWb. The Workbooks collection represents workbooks currently open in that Excel instance. A variable assigned to one of them remains a convenient reference even if the user activates another workbook afterward.

Choose between ThisWorkbook and ActiveWorkbook

Two common expressions for assigning a workbook are ThisWorkbook and ActiveWorkbook, but they mean different things. ThisWorkbook is the workbook that contains the running VBA project. If a macro is stored in a report file, it refers to that report file even when the user has another workbook in front.

ActiveWorkbook is the workbook in the active Excel window at that moment. It may be a different file from the one containing the macro, and it can change when the user switches windows or when code opens another workbook. Choosing the wrong one is a common cause of a macro editing the wrong file.

For code that should change the workbook containing the macro, use an explicit assignment such as Set wb = ThisWorkbook. For code that intentionally acts on the workbook the user has selected, Set wb = ActiveWorkbook may be appropriate, but check that it is not Nothing before using it. In add-ins, the workbook containing the VBA project is often the add-in itself, so ThisWorkbook may not be the user’s data file.

Open, create, and select workbooks

There are several ways to give a Workbook variable a value. Choose the one that matches the task rather than relying on whichever workbook happens to be active:

  • Reference the macro’s own file: Set wb = ThisWorkbook.
  • Reference the active file: Set wb = ActiveWorkbook, after checking that a workbook is active.
  • Reference a specific open file: use Set wb = Workbooks("Sales.xlsx"). The name must match an open workbook’s name, including its extension where applicable.
  • Open a file: assign the result of Workbooks.Open to the variable, as in the earlier example.
  • Create a workbook: assign the result of Workbooks.Add.

This example creates a new workbook and writes a value to its first worksheet:

Sub CreateDraft()
    Dim draftWb As Workbook

    ' This macro creates and edits a workbook; save a copy of important work first.
    Set draftWb = Workbooks.Add
    draftWb.Worksheets(1).Range("A1").Value = "Draft"
End Sub

Workbooks.Add creates a new workbook, but this example does not save it to a file. Saving requires an explicit SaveAs call and a suitable path and file format. Avoid adding a save step with a fixed path unless you know that the destination is correct and understand whether an existing file could be overwritten.

Workbook variables, scope, and lifetime

Where you place a declaration determines where the variable can be used. A variable declared inside a procedure, as in the examples, is local to that procedure. It is usually the clearest choice when the workbook reference is needed only while that macro runs. When the procedure ends, the local variable goes out of scope; the workbook itself does not automatically close.

A declaration at the top of a module, outside any procedure, can be shared by procedures in that module when declared with the appropriate access. A Public declaration in a standard module can make a variable available more widely in the VBA project. Broader scope can make code harder to understand because a later procedure may depend on a reference assigned somewhere else. Prefer local variables unless sharing the reference is genuinely useful.

Use Option Explicit at the top of a module to require variables to be declared. This catches misspellings such as using reportWB in one place and reprotWb in another. VBA is not case-sensitive, but it still treats different spellings as different names. Also, declaring a variable does not ensure that it has been assigned: before assignment, an object variable is Nothing.

It is usually unnecessary to set a local workbook variable to Nothing at the end of a short procedure. Doing so removes that particular reference; it does not close the workbook if Excel or another variable still holds it open. Close a workbook with its Close method when that is what the task requires, and decide deliberately whether to save changes.

Early binding and late binding

Dim wb As Workbook is early binding: VBA knows the variable’s Excel object type while compiling the code. The editor can offer properties and methods through IntelliSense, and VBA can check many object-related issues before the macro runs. This is the normal choice for macros written and run inside Excel.

Late binding uses the generic Object type instead, such as Dim wb As Object. It can be useful when automating Excel from another Office application and avoiding a compile-time dependency on a particular Excel Object Library reference. The trade-off is less editor assistance and fewer checks at compile time. It also does not remove the need for Excel to be installed and available when the code tries to automate it.

Late binding is not a better version of As Workbook for ordinary Excel macros. If the code runs in Excel and targets Excel workbooks, declaring the specific Workbook type normally makes the intent clearer and helps catch mistakes.

Common errors and safer coding habits

A frequent compile-time error is “User-defined type not defined” on As Workbook. In Excel’s own VBA project, confirm that the code is in the Excel VBA editor. If the code is hosted by another Office program, check the VBA editor’s Tools > References list for the appropriate Microsoft Excel Object Library. A missing or broken reference can also cause related compile problems.

A runtime error often means the code tried to use a workbook that was not assigned, was not open, or was identified by an incorrect name. For example, Set wb = Workbooks("Sales.xlsx") fails if no open workbook has that name. When the workbook might be absent, use error handling or check the open-workbook collection before proceeding. Do not suppress all errors indiscriminately: that can hide a wrong file name or a genuine problem with the workbook.

Another common mistake is writing wb = Workbooks.Open(...) without Set. Since the result is an object, the assignment needs Set. By contrast, assigning a property value, such as wb.Name to a String variable, is an ordinary value assignment and does not use Set.

It is also risky to rely on Select and Activate when a variable can identify the intended workbook directly. For example, qualify worksheet and range references through the workbook variable. That makes the target explicit and reduces the chance that code acts on the wrong active sheet. On Windows, open the VBA editor with Alt+F11; on Mac, the shortcut is commonly Option+F11, though keyboard settings and function-key behavior can vary. Excel for the web does not run VBA macros; use the desktop Excel application. VBA is available in desktop Excel for Windows and Mac, although some features and file-system details differ between platforms.

Frequently Asked Questions

Does Dim wb As Workbook open a workbook?

No. It only declares an object variable. Assign it to an existing workbook, call Workbooks.Open, or create one with Workbooks.Add to make the variable refer to a workbook.

Why do I need Set with a Workbook variable?

A Workbook is an object, and VBA uses Set to assign object references. For example, use Set wb = ThisWorkbook; do not use Set when assigning ordinary values such as text or a number.

What is the difference between Workbook and Worksheet?

A Workbook represents an Excel file that is open in the application. A Worksheet represents one sheet within a workbook. Declare them with their respective types, such as Dim wb As Workbook and Dim ws As Worksheet.

Can I declare a workbook variable as Dim wb As Object?

Yes. That is a late-bound object variable and can be useful in some cross-application automation. For macros running in Excel, As Workbook is generally clearer and provides better editor support.

Does Set wb = ThisWorkbook refer to the file I am looking at?

It refers to the workbook that contains the running VBA code, which may not be the active file. Use ActiveWorkbook only when you specifically mean the workbook in the active Excel window.

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.