The Complete Guide To Workbooks in Excel VBA

“We are drowning in information but starved for knowledge.” – John Naisbitt

© Haywiremedia | Dreamstime.com – Kids Reading Books In Fantasy Library Photo

 

This post provides a compete guide to using Workbooks in Excel VBA. If you are new to VBA then you will find it a good place to start your journey. If you have used VBA before you will see some little-known techniques that can dramatically improve your code. If you are here looking for a quick reference guide to using Workbooks  then the first section was written for you.
A Quick Reference to Workbooks in VBA

The following table provides a quick guide to accessing the workbook

Accessing the Workbook

 

The following table provides a quick how-to guide on the main workbooks tasks

A Quick Reference Guide to the Workbook

 

Getting Started with the Workbook

We can access a workbook using Workbooks(“MyVBA.xlsm”) where MyVBA.xlsm is the name of an open workbook. Workbooks(“MyVBA.xlsm”) is called a workbook object. This means it contains methods and properties we can use to manipulate a workbook. Anything you can do with a workbook in Excel you can do with a workbook object.

The following example shows you how to write to a cell on a worksheet. You will notice we had to specify the workbook, worksheet and range of cells.

Public Sub WriteToA1()

' Writes the value 100 to cell A1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("A1") = 100

End Sub

This example may look a little be confusing to a new user but actually it is quite simple. The first part up to the decimal point is the Workbook, the second part is the Worksheet and the third is the Range. Here are some more examples of writing to a cell

Public Sub WriteToMulti()

' Write the value 100 to cell A1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("A1") = 100

' Write the text "John" to cell B1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("B1") = "John"

' Write the value 100 to cell A1 of worksheet "Accounts" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Accounts").Range("A1") = 100

' Write the date to cell D3 of worksheet "Sheet2" in Book2.xlsm
Workbooks("Book2.xlsm").Worksheets("Sheet2").Range("D3") = "12\12\2016"

End Sub

You can see the simple pattern here. You can write to any cell in any worksheet from any workbook. It is just a matter of changing the workbook name, worksheet name and the range to suit your needs.

Take a look at the workbook part

Workbooks("Example.xlsx")

The Workbooks keyword refers to a collection of all open workbooks. Supplying the workbook name to the collection gives us access to that workbook. When we have the object we can use it to perform tasks with the workbook.

There are two important points to note:

If you are running two copies of Excel then Workbooks() only refers to Workbooks in the current copy.
If you get the error “Subscript out of Range” it means the workbook you specified is not open or you have spelled the name of the workbook incorrectly.

The following examples show what you can do once you have access to a workbook. (To try this example create two open workbooks called Test1.xlsm and Test2.xlsm.)

Public Sub WorkbookProperties()

' Prints the number of open workbooks
Debug.Print Workbooks.Count

' Prints the full workbook name
Debug.Print Workbooks("Test1.xlsm").FullName

' Displays the full workbook name in a message dialog
MsgBox Workbooks("Test1.xlsm").FullName

' Prints the number of worksheets in Test2.xlm.
Debug.Print Workbooks("Test2.xlsm").Worksheets.Count

' Prints the name of currently active sheet of Test2.xlm.
Debug.Print Workbooks("Test2.xlsm").ActiveSheet.Name

' Closes workbook called Test1.xlm.
Workbooks("Test1.xlm").Close

' Closes workbook called Test2.xlm. Will automatically save changes
Workbooks("Test2.xlm").Close saveChanges:=True

End Sub

 Note: In the code examples I use Debug.Print a lot. This function prints values to the Immediate  Window. To view this window select View->Immediate Window from the menu( Shortcut is Ctrl + G)

Using Index instead of the Workbook name

You can also access a workbook using the index number. The index refers to the order the Workbook was open or created. In the following example we use the Workbooks.Count property with the Index. Workbooks.Count contains the number of workbooks which are currently open.

' First workbook that was opened
Debug.Print Workbooks(1).Name

' Last workbook that was opened
Debug.Print Workbooks(Workbooks.Count).Name

In this example we used Workbooks.Count. This is the number of workbooks that are currently in the Workbooks collection.

Using the index is not really useful unless you have a specific need to know the order.

Using all Open Workbooks

If you want to access all the workbooks that are currently open the following example shows you how to do this using For Each.

Public Sub PrintWrkFileName()

' Prints out the full filename of all currently open workbooks
Dim wrk As Workbook
For Each wrk In Workbooks
Debug.Print wrk.FullName
Next wrk

End Sub

You can also use the Index to access all the open workbooks

Public Sub PrintWrkFileNameIdx()

' Prints out the full filename of all currently open workbooks
Dim i As Long
For i = 1 To Workbooks.Count
Debug.Print Workbooks(1).FullName
Next wrk

End Sub

For accessing workbooks either of these methods is fine. The For Each loop is generally preferred when you are accessing a large number of objects. In terms of open workbooks this is rarely an issue.

Opening a Workbook

So far we have dealt with workbooks that are already open. Of course, having to manually open a workbook before running a Macro, defeats the purpose of automating tasks. The following code opens the workbook “Book1.xlsm” in the “C:\Docs” folder.

Public Sub OpenWrk()

' Open the workbook and print the number of sheets it contains
Workbooks.Open ("C:\Docs\Book1.xlsm")

Debug.Print Workbooks("Book1.xlsm").Worksheets.Count

' Close the workbook without saving
Workbooks("Book1.xlsm").Close saveChanges:=False

End Sub

It is a good idea to check a workbook actually exists before you try to open it. This will prevent you getting errors. The Dir function allows you to easily do this .

Public Sub OpenWrkDir()

If Dir("C:\Docs\Book1.xlsm") = "" Then
' File does not exist - inform user
MsgBox "Could not open the workbook. Please check it exists"
Else
' open workbook and do something with it
Workbooks.Open("C:\Docs\Book1.xlsm").Open
End If

End Sub

 

 Using the File Dialog to Select the Workbook

The previous section shows you how to open a workbook with a given name. Sometimes you may want to allow the user to manually select the workbook using the standard Windows file dialog shown here

The Windows File Dialog

You can use the following function to open a workbook using the file dialog. The function returns the full file name if a file was selected. If the user cancels it displays a message and returns an empty string.

Public Function UserSelectWorkbook() As String

On Error GoTo ErrorHandler

Dim sWorkbookName As String

Dim FD As FileDialog
Set FD = Application.FileDialog(msoFileDialogFilePicker)

' Open the file dialog
With FD
' Set Dialog Title
.Title = "Please Select File"

' Add filter
.Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"

' Allow selection of one file only
.AllowMultiSelect = False

' Display dialog
.Show

If FD.SelectedItems.Count > 0 Then
UserSelectWorkbook = FD.SelectedItems(1)
Else
MsgBox "Selecting a file has been cancelled. "
UserSelectWorkbook = ""
End If
End With

' Clean up
Set FD = Nothing
Done:
Exit Function
ErrorHandler:
MsgBox "Error: " + Err.Description
End Function

When you call this function you have to check for the user cancelling the dialog. The following example shows you how to easily call the UserSelectWorkbook function and handle the case of the user cancelling

Public Sub TestUserSelect()

Dim userBook As Workbook, sFilename As String

' Call the UserSelectworkbook function
sFilename = UserSelectWorkbook()

' If the filename returns is blank the user cancelled
If sFilename <> "" Then
' Open workbook and do something with it
Set userBook = Workbooks.Open(sFilename)
End If

End Sub

You can customise the dialog by changing the Title, Filters and AllowMultiSelect in the UserSelectWorkbook function.

Using the ThisWorkbook keyword

There is an easier way to access the current workbook. You can use the keyword ThisWorkbook. It refers to the current workbook i.e. the workbook that contains the VBA code. If our code is in a workbook call MyVBA.xlsm then ThisWorkbook and Workbooks(“MyVBA.xlsm”) refer to the same thing.

Using ThisWorkbook is more useful than using Workbooks. When we use ThisWorkbook we do not need to worry about the name of the file. This gives us two advantages:

Changing the filename will not affect the code
Copying the code to another workbook will not require a code change

These may seem like very small advantages. The reality is your filenames will change all the time. And you will often use the same code in multiple workbooks. You don’t want your code to break every time you change a workbook name.

Look at the next example. If the filename changes to MyVBA2.xlsm then the second line will no longer work. You will have to change the filename in the code. However the first line will continue to work correctly without any change.

Public Sub WriteToCellUsingThis()

' Both lines do the same thing.
Debug.Print ThisWorkbook.FullName
Debug.Print Workbooks(“MyVBA.xlsm”).FullName

End Sub

 

Using the ActiveWorkbook object

Before we leave this section I want to let you in on a secret. Our good friend ThisWorkbook has an evil twin called ActiveWorkbook.

ActiveWorkbook refers to the workbook that is currently active. Seems perfectly innocent doesn’t it?  However any workbook can become the active one. By simply clicking on a workbook with your mouse you make it the active one. This makes it so easy to read and write using the wrong workbook.

Using ActiveWorkbook can also make the code very difficult to read. It may not be obvious from the code which workbook should be the active one. With ThisWorkbook there is no confusion.

Because of these reasons it is better to avoid using ActiveWorkbook unless your application is interested in the currently active workbook.

Examples of the Accessing Workbooks

The next example shows how to print the full name of a workbook using each of the different workbook access types

Public Sub WorkbooksUse()

' This is a workbook that is already open and called MyVBA.xlsm
Debug.Print Workbooks("MyVBA.xlsm").FullName

' The workbook that contains this code
Debug.Print ThisWorkbook.FullName

' The open workbook that was opened first
Debug.Print Workbooks(1).FullName

' The open workbook that was opened last
Debug.Print Workbooks(Workbooks.Count).FullName

' The workbook that is the currently active one
Debug.Print ActiveWorkbook.FullName

' A closed workbook called Book1.xlsm in folder C:\Docs
Workbooks.Open ("C:\Docs\Book1.xlsm")
Debug.Print Workbooks("Book1.xlsm").FullName
Workbooks("Book1.xlsm").Close

End Sub

 

Declaring a Workbook Object

The reason for declaring a workbook object is simply to give a workbook a nicer name. It makes your code easier to read and therefore less error prone. It is easier to see the advantage using an example

Public Sub OpenWrkObjects()

Dim wrk As Workbook
Set wrk = Workbooks.Open("C:\Docs\Book1.xlsm")

' Print number of sheets in each book
Debug.Print wrk.Worksheets.Count
Debug.Print wrk.Name

wrk.Close

End Sub

You can set a workbook object to be any of the workbook objects we saw in the previous section.

The following shows you the same code without a workbook object

Public Sub OpenWrkNoObjects()

Workbooks.Open ("C:\Docs\Book1.xlsm")

Debug.Print Workbooks("Book2.xlsm").Worksheets.Count
Debug.Print Workbooks("Book2.xlsm").Name

Workbooks("Book2.xlsm").Close

End Sub

In these examples the difference is not major. However, when you have a lot of code, using a object is useful particularly for worksheet and ranges where the names tend to be long e.g. thisWorkbook.Worksheets(“Sheet1″).Range(“A1″).

Another advantage is that you can name the object as wrkRead or wrkWrite and then it is very obvious what this workbook is being used for. And obvious code is good code!

Creating a New Workbook

To create a new workbook you use the Add function of the Workbooks Collection. This creates a new blank workbook. It is the same as selecting New Workbook from the Excel File menu.

When you create a new workbook you will generally want to Save it. The following code shows you how to do this.

Public Sub AddWordbook()

Dim wrk As Workbook
Set wrk = Workbooks.Add

' Save as xlsx. This is the default.
wrk.SaveAs "C:\Temp\Example.xlsx"

' Save as a Macro enabled workbook
wrk.SaveAs "C:\Temp\Example.xlsm", xlOpenXMLWorkbookMacroEnabled

End Sub

When you create a new workbook it normally contains three sheets. This is determined by the property Application.SheetsInNewWorkbook. If you want to have a different number of sheets in a new workbook then you can chang this property before you create the new workbook. The following example shows you how to create a new workbook with seven sheets.

Public Sub AddWordbookMultiSheets()

' Store SheetsInNewWorkbook value so we can reset it later
Dim sheetCnt As Long
sheetCnt = Application.SheetsInNewWorkbook

' Set sheets in a new workbook to be 7
Application.SheetsInNewWorkbook = 7

' Workbook will be created with 7 sheets
Dim wrk As Workbook
Set wrk = Workbooks.Add

' Display sheet count
Debug.Print "The number of sheets is " + CStr(wrk.Worksheets.Count)

' Reset to original value
Application.SheetsInNewWorkbook = sheetCnt

End Sub

 

Creating a Copy of a Workbook

There are two main ways to create a copy of a workbook. If the workbook is open you can use the SaveAs function. This is the same as the manual SaveAs. It will close and save the original file and leave the new one open.

The function SaveAs comes with twelve optional parameters. Most of the time you will only need the Filename one as shown in the following code example

Public Sub WorkbookSaveAs()
Workbooks("Book2.xlsm").SaveAs "C:\Docs\Example_Copy.xlsm"
End Sub

If you want to copy a workbook without opening it then you can use FileCopy as the following example demonstrates

Public Sub CopyWorkbook()
FileCopy "C:\Docs\Docs.xlsm", "C:\Docs\Example_Copy.xlsm"
End Sub

 

Using the With keyword

The With keyword makes the process of writing VBA code easier. It also makes your code easier to read. Using With means you only need to mention the object once.

The following example has two Subs. The first is normal code we have seen so far. The second uses the With keyword. You can see the code is much clearer in the second Sub. The keywords End With mark the finish of a section code using With.

' Not using the With keyword
Public Sub NoUsingWith()

Debug.Print Workbooks("Book2.xlsm").Worksheets.Count
Debug.Print Workbooks("Book2.xlsm").Name
Debug.Print Workbooks("Book2.xlsm").Worksheets(1).Range("A1")
Workbooks("Book2.xlsm").Close

End Sub

' Using With makes the code easier to read
Public Sub UsingWith()

With Workbooks("Book2.xlsm")
Debug.Print .Worksheets.Count
Debug.Print .Name
Debug.Print .Worksheets(1).Range("A1")
.Close
End With

End Sub

The With keyword can be used with any object such as Workbooks, Worksheets, Ranges etc.

Summary

The following is a brief summary of the main points of this post

If you are referencing the workbook containing the code then use ThisWorkbook for the Workbook.
If you are referencing an open workbook other than the one with the code then use Workbooks(“Example.xlsx”) for the Workbook.
If you are referencing a closed workbook then use use Set Wrk = Workbooks.Open(“C:\Folder\Example.xlsx”) to open the workbook.
If you want to allow the user to select a file using Windows File Dialog then use the  UserSelectWorkbook function provided above.
To create a copy of an open workbook use the SaveAs property with a filename.
To create a copy of a workbook without opening use the FileCopy function.
To reduce the number of time you have to type an object name in your code use the With keyword.
To make, the purpose of your code clearer,  avoid long winded names like ThisWorkbook.Worksheets(“Sheet1″).Range(“A1″) use Workbook Objects e.g. Dim wrk as Workbook.
To run through all open Workbooks use For Each wrk in Workbooks where Wrk is a workbook object.
When referencing Workbooks avoid using ActiveWorkbook and Workbooks(Index) as their reference to a particular workbook is temporary.

 

Conclusion

This was an in-depth post about a very important element of VBA.  I hope you found it beneficial. Excel is great at providing many ways to perform similar actions but the downside is it can lead to confusion at times.

To get the most benefit from this post I recommend you try out the examples. Create some workbooks and play around with the code. Make changes to the code and see how the changes affect the outcome.

Practice is the best way to learn VBA.

If you found this post useful then feel free to share it with others.  You may also want to check out The Complete Guide to Worksheets in Excel VBA