Set Workbook variable

The correct way to program VBA is to explicitly assign which object you want to work with. In case of a Workbook this can be expressed in several ways which are discussed here. As a simple example, if you want to rename a worksheet, first you will have to tell which worksheet, let's assume the first, Sheets(1), which then begs the question in which workbook it is located.

Dim wb As Workbook: Set wb = 
Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
ws.Name = "Data"

As you can see in image below, Code VBA IntelliSense opens a menu with all possible assignment expressions to specify what object the object variable wb refers to. Here, each of the choices is given a short explanation.

In the image you see four groups of assignment expressions that can be used to set a Workbook. The first group contains the most used to create, open or select a file or data source. The second group concerns the often used ThisWorkbook and ActiveWorkbook methods. Next come Worksheet objects whose Parent property give access to their containing Workbook. Finally, you can reference a Workbook using the ProtectedViewWindow method, when applicable.

• Create a new Workbook
• Select open Workbook using Item
• Open an Excel Workbook file
• Create a Workbook with data from a database
• Create a Workbook from an xml file
• Use This Workbook - containing the code
• Use the Active Workbook
• The Workbook in which the Sheet is contained
• A Workbook associated with an ProtectedViewWindow object, to read only or Edit.
• Process items in Workbooks collection

Create new or open existing workbook

In many cases the workbook you want to act on is the workbook you just created. In it simplest form:

Dim wb As Workbook
Set wb = Workbooks.Add

Open existing Workbook

The other common use is a Workbook you just opened.

Dim strFilename As String: strFilename = "C:\temp\book24.xslx"
Dim wb As Workbook
Set wb = Workbooks.Open(Filename:=strFilename, Password:="hi123", UpdateLinks:=3)

If the macro and data are in the same excel file you can use ThisWorkbook. If not, you have more options: the workbook you just opened, the ActiveWorkbook, ...

Item in the Workbooks collection

You can specify which workbook using the name or index number. The index count starts with 1, giving the first workbook in the collection.. You can leave the word Item out because the VBA compiler in case of collections uses the Item by default.

Dim wb As Workbook
Set wb = Workbooks.Item(1)

The name of the active workbook appears in the Excel title bar. It is the filename plus extension in case of a saved workbook, e.g.

Set wb = Workbooks("Book1.xslx")

If a workbook is new and not yet saved, it will be assigned a temporary name.

Set wb = Workbooks("Book1")

ThisWorkbook

If your macro is only intended to work on the excel file that contains it, you would insert ThisWorkbook:

Dim wb As Workbook: Set wb = ThisWorkbook

ActiveWorkbook

The Active Workbook is the workbook in the active window (the window on top). ActiveWorkbook is also commonly used when you writing an add-in, e.g. in Personal.xslb.

Dim wb As Workbook: Set wb = ActiveWorkbook

Dim wb As Workbook: Set wb = ...
If wb Is Nothing Then
    MsgBox Prompt:="The Workbook is not available", Buttons:=vbOKOnly + vbInformation
    Exit Sub
End If

Process Workbooks collection

Iterating the collection returns in each round a new workbook variable implicitly set.

Dim wb As Workbook
For Each wb In Workbooks
    
Next wb

The Workbook in which the Sheet is contained

If you are working on a Sheet you can access the Workbook it belongs to using the Parent property:

Dim wb As Workbook
Set wb = ActiveSheet.Parent