VBA Workbook – A Guide to Work with Workbooks in VBA

In Excel, a workbook is one of the most important of all the Excel Objects, and it is also essential to understand how to use and refer to workbooks while writing VBA codes.

In this tutorial, we will explore all the things that you need to know. But, the first thing you need to understand are objects that are involved in using workbooks in VBA.

Objects you need to know:

• Workbooks Object
• Workbook Object

Both of these objects sound the same, but there’s a core difference between both of them.

Workbooks Object

In VBA, the workbooks object represents the collection of the workbooks that are open in Microsoft Excel. Imagine you have ten workbooks open at the same time. And you want to refer to the one workbook out of them. In that case, you need to use the workbook object to refer to that one workbook using its name.

Workbook Object

In VBA, the workbook object represents one single workbook from the entire collection of workbooks open at present in Microsoft Excel. The best way to understand this is to think about declaring a variable as a workbook that you want to use to refer to a particular workbook in the code.

Useful Links: Add Developer Tab | Visual Basic Editor | Run a Macro | Personal Macro Workbook

Refer to a Workbook in VBA

To work with workbooks in VBA, the first thing that you need to know is how to refer to a workbook in a macro. Here’s the happy thing: there are multiple ways to refer to a workbook. And ahead, we will explore each one of them.

1. By Name

The easiest way to refer to a workbook is to use its name. Let’s say you want to activate the workbook Book1.xlsx, in that case, the code that you need to use should be like the following:

Referring to a workbook with its name is quite simple, you need to specify the name, and that’s it. But here’s one thing that you need to take care of: if a workbook is not saved, then you need to use only the name. And if saved, then you need to use the name along with the extension.

2. By Number

When you open a workbook, Excel gives an index number to that workbook, and you can use that number to refer to a workbook. The workbook that you have opened at first will have the index number “1” and the second will have “2” and so on.

This method might seem less real to you as it’s hard to know which workbook is on which index number. But there’s one situation where this method is quite useful to use, and that’s looping through all the open workbooks.

3. By ThisWorkbook

This workbook is a property that helps you to refer to the workbook where you are writing the code. Let’s say you are writing the code in “Book1” and use the ThisWorkbook to save the workbook. Now even when you change the name of the workbook, you won’t need to change the code.

The above code counts the number of sheets in the workbook where this code is written and shows a message box with the result.

4. By ActiveWorkbook

If you want to refer to a workbook that is active, then you need to use the “ActiveWorkbook” property. The best use of this property is when you are sure which workbook is activated now. Or you have already activated the workbook that you want to work.

The above code activates the workbook “Book1” first and then uses the active workbook property to save and close the active workbook.

Access all the Methods and Properties

In VBA, whenever you refer to an object, VBA allows you to access the properties and methods that come with that object. In the same way, the workbook object comes with properties and methods. To access them, you need to define the workbook first and then enter a dot.

The moment you type a dot (.), it shows the list of properties and methods. Now, you must have a question in your mind about how to identify which one is a property and which one is a method.

Here’s the trick. If you look closely, you can identify a moving green brick and grey hand before each name on the list. So, all the properties have that grey hand before the name and methods have a moving green brick.

For example to use a Method with Workbook

Imagine you want to close a workbook (which is a method), you need to type or select “Close” from the list.

After that, you need to enter starting parentheses to get IntelliSense to know the arguments you need to define.

With the close method, there are three arguments that you need to define, and as you can see, all these arguments are optional, and you can skip them if you want. But some methods don’t have arguments (for example: activate)

For example to use a Property with Workbook

Imagine you want to count the sheets from the workbook “book1” In that case, you need to use the “Sheets” property and then the further count property of it.

In the above code, as I said, you have book1 defined, and then the sheet property refers to all the sheets, and then the count property to count them. And when you run this code, it shows you a message box with the result.

Using “WITH” Statement with Workbook

In VBA, there’s a “With” statement that can help you work with a workbook while writing a macro efficiently. Let’s see the below example where you have three different code lines with the same workbook, i.e., ActiveWorkbook.

With the “WITH statement”, you can refer to the active workbook a single time, and use all the properties and methods that you have in the code.

• First of all, you need to start with the starting statement “With ActiveWorkbook” and end the statement with “End With”.

• After that, you need to write the code between this statement that you have in the above example.

As you can see in the above code we have referred to the ActiveWorkbook one using the WITH statement, and then all the properties and methods need to be used.

Sub vba_activeworkbook_with_statement()
With ActiveWorkbook
    .Sheets.Add Count:=5
    .Charts.Visible = False
    .SaveAs ("C:UsersDellDesktopmyFolderbook2.xlsx")
End With
End Sub

Let me tell you a simple real-life example to make you understand everything. Imagine you ask me to go to room 215 to get the water bottle, and when I come back, you again send me to room 215 to get a pen, and then again send me back to get a laptop. Now here’s the thing: All the things that you told me to get are in room 215. So better if you sent me to room 215 and told me to get all three things at once.

Read: With – End With

Declaring a Variable as a Workbook

Sometimes you need to declare a variable as a workbook to use it further in the code. Well, this doesn’t require anything special for you to do.

1. Use the DIM Statement (Declare).
2. Write the name of the Variable.
3. Define the type of the variable as Workbook.

Dealing with Errors

When you work with a workbook(s) object in VBA, there are chances that you need to deal with errors as well. Take an example of the “Run-time Error 9: Subscript out of Range” error. This error can occur for various reasons.

• The workbook that you are trying to refer to is not opened.
• Might be you have misspelled the name.
• The workbook you are referring to is not yet saved and you are using the extension along with the name.
• If you are using the index number to refer to a workbook and the number you have used is greater than the total number of workbooks open.