IN THIS CHAPTER

Creating a new workbook from scratch

Saving a workbook when a particular cell is changed

Saving a workbook before closing

Protecting an existing workbook on close

Unprotecting an existing workbook on open

Opening a workbook to a specific tab

Opening a specific workbook defined by the user

Determining whether a workbook is already open

Determining whether a workbook exists in a directory

A workbook is not just an Excel file; it's also an object in Excel's object model (a programming hierarchy that exposes parts of Excel to VBA).

This means that you can reference workbooks through VBA to do cool things such as automatically create new workbooks, prevent users from closing workbooks, and automatically back up workbooks.

In this chapter, you explore a few of the more useful workbook-related macros.

Installing Macros

You can install the macros in this chapter in three places depending on what kind of macro it is and how available you want the macro to be. Event macros are put into the module of the object the event is tied to. Non-event macros go into a standard module and can be in the workbook in which they are meant to run or in a workbook called the Personal Macro Workbook if you want to be able to run them on any workbook.

Creating a New Workbook from Scratch

You may sometimes want or need to create a new workbook in an automated way. For example, you may need to copy data from a table and paste it into a newly created workbook. The following macro copies a range of cells from the active sheet and pastes the data into a new workbook.

This macro is relatively intuitive as you read through the lines of the code.

Sub CopyToNewWorkbook()

'Step 1: Copy the data.
Sheets("Example 1").Range("B4:C15").Copy

'Step 2: Create a new workbook.
Workbooks.Add

'Step 3: Paste the data.
ActiveSheet.Paste Destination:=Range("A1")

'Step 4: Turn off application alerts.
Application.DisplayAlerts = False

'Step 5: Save the newly created workbook.
ActiveWorkbook.SaveAs _
Filename:="C:MyNewBook.xlsx"

'Step 6: Turn application alerts back on.
Application.DisplayAlerts = True

End Sub

1. Copies the data on the “Example 1” sheet ranging from cells B4 to C15.

   The thing to note here is that you are specifying both the sheet and the range by name. This practice is best when you are working with multiple workbooks that have multiple worksheets. Because the workbook isn’t explicity named, the macro uses the active workbook. You may wish to name the workbook so the code always uses the same one.

2. The Add method of the Workbook object creates a new workbook.

   When you add a workbook, the new workbook immediately gains focus, becoming the active workbook. This is the same behavior you would see if you were to add a workbook manually (File ⇒   New ⇒   Blank Document).

3. The Paste method sends the data you copied to cell A1 of the new workbook. Pay attention to the fact that the code refers to the ActiveSheet object.

4. The DisplayAlerts method is set to False, effectively turning off Excel’s warnings. You do this because in the next step of the code, you save the newly created workbook. You may run this macro multiple times, in which case Excel attempts to save the file multiple times.

   What happens when you try save a workbook multiple times? That’s right — Excel warns you that there is already a file out there with that name and then asks if you want to overwrite the previously existing file. Because your goal is to automate the creation of the new workbook, you want to suppress that warning.

5. Saves the file by using the SaveAs method. Note that you are entering the full path of the save location, including the final filename.
6. Turns the applications alerts on again. If you don’t, Excel continues to suppress all warnings for the life of the current session.

Saving a Workbook when a Particular Cell Is Changed

Sometimes, you may be working on data that is so sensitive that you need to save your workbook every time a particular cell or range of cells is changed. This macro allows you to define the range of cells that, when changed, forces the workbook to save.

In the example shown in Figure 4-1, you want the workbook to save when an edit is made to any of the cells in the range C5:C16.

The secret to this code is the Intersect method. Because you don’t want to save the worksheet when any old cell changes, you use the Intersect method to determine whether the target cell (the cell that changed) intersects with the range you have specified to be the trigger range (C5:C16 in this case).

The Intersect method returns one of two things: either a Range object that defines the intersection between the two given ranges, or nothing. So in essence, you need to throw the target cell against the Intersect method to check for a value of Nothing. At that point, you can make the decision whether to save the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

'Step 1: Does the changed range intersect?
If Not Intersect(Target, Range("C5:C16")) Is Nothing Then

'Step 2: If there is an intersection, save the workbook.
ActiveWorkbook.Save

'Step 3: Close out the If statement.
End If

End Sub

1. Checks to see whether the target cell (the cell that has changed) is in the range specified by the Intersect method. If it's not in the range, Intersect returns Nothing (a special keyword in VBA meaning there is no object). If it’s in the range, the Is Nothing comparison returns False and the Not operator changes False to True.
2. If there is an intersection, employs the Save method of the active workbook, overwriting the previous version.
3. Closes the If statement. Every time you use an If-Then block you must close it with End If.

This is an event macro, so you must install it in the worksheet's module, as shown in Figure 4-2. (See the “Installing Macros” section at the beginning of this chapter for how.)

Saving a Workbook before Closing

This macro is an excellent way to protect users from inadvertently closing their file before saving. When implemented, this macro ensures that Excel automatically saves before closing the workbook.

Excel normally warns users who are attempting to close an unsaved workbook, giving them an option to save before closing. However, many users may blow past the warning and inadvertently click No, telling Excel to close without saving. With this macro, you are protecting against this by automatically saving before close.

This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The crux of the code is simple — it asks the user whether they really wants to close the workbook (see Figure 4-3). The macro then evaluates whether the user clicked OK or Cancel.

The evaluation is done with a Select Case statement. The Select Case statement is an alternative to the If…Then…Else statement, allowing you to perform condition checks in your macros. The basic construct of a Select Case statement is simple:

Select Case <some expression to check>
Case Is = <some value>
<do something>
Case Is = <some other value>
<do something else>
Case Is = <some 3rd value>
<do some 3rd thing>
End Select

With a Select Case statement, you can perform many conditional checks. In this case, you are simply checking for OK or Cancel. Take a look at the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Activate the message box and start the check.
Select Case MsgBox("Save and close?", vbOKCancel)

'Step 2: Cancel button pressed, cancel the close.
Case Is = vbCancel
Cancel = True

'Step 3: OK button pressed, save the workbook and close.
Case Is = vbOK
ActiveWorkbook.Save

'Step 4: Close your Select Case statement.
End Select

End Sub

1. Shows the message box as the condition check for the Select Case statement. Here, the vbOKCancel argument displays an OK and Cancel buttons in the message.
2. If the user clicks Cancel in the message box, the Workbook_Close event is canceled. This is done by passing True to the Cancel Boolean. (This effectively cancels the event's action, preventing the workbook from closing.)
3. If the user clicks the OK button in the message box, workbook is saved. And because Cancel isn't set to True, Excel continues with the close.
4. Closes the Select Case statement. Every time you start a Select Case, you must close it with a corresponding End Select.

To install this macro, you need to copy and paste it into the Workbook_BeforeClose event Code pane, as shown in Figure 4-4. (See the “Installing Macros” section at the beginning of this chapter.) Placing the macro there allows it to run each time you try to close the workbook.

Protecting a Worksheet on Workbook Close

Sometimes you need to send your workbook into the world with specific worksheets protected. If you find that you’re constantly protecting and unprotecting sheets before distributing your workbooks, this macro can help you.

This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The macro automatically protects the specified sheet with the given password, and then saves the workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Protect the sheet with a password.
Sheets("Sheet1").Protect Password:="RED"

'Step 2: Save the workbook.
ActiveWorkbook.Save

End Sub

1. Protects the sheet you want — Sheet1, in this case — and provides the password argument, Password:=RED. This defines the password needed to remove the protection.

   This password argument is completely optional. If you omit this altogether, the sheet is still protected, but you won’t need a password to unprotect it.

   Also, Excel passwords are case-sensitive, so pay attention to the capitalization you are using.

2. Saves the workbook. If you don’t save the workbook, the sheet protection you just applied won’t be in effect the next time the workbook is opened.

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event Code window, as shown in Figure 4-5. (See the first section in this chapter, “Installing Macros.”) Placing the macro here allows it to run each time you try to close the workbook.

Note that you can protect additional sheets by adding additional statements before the ActiveWorkbook.Save statement.

Unprotecting a Worksheet

If you’ve distributed workbooks with protected sheets, you likely get the workbooks back with the sheets still protected. While you may want those sheets protected for your users, you may need to unprotect the worksheets before continuing your portion of the work. If you find that you are continuously unprotecting worksheets, this macro may be just the ticket.

You can run this macro to automatically unprotect the specified sheet with the given password.

Sub UnprotectSheet()

'Step 1: Unprotect the sheet with a password.
Sheets("Sheet1").Unprotect Password:="RED"

End Sub

The macro explicitly names the sheet you want to unprotect — Sheet1, in this case. Then it passes the password required to unprotect the sheet. Be aware that Excel passwords are case-sensitive, so pay attention to the exact password and capitalization that you are using.

To implement this macro, you can copy and paste it into a standard module, as shown in Figure 4-6. (See the “Installing Macros” section earlier in this chapter for details.)

You can unprotect additional sheets by adding additional statements.

Opening a Workbook to a Specific Tab

In some situations, you may want a specific worksheet to be visible whenever your workbook is opened. With this macro, if a user is working with your workbook, they can't go astray because the workbook starts on the exact worksheet it needs to.

In the example shown in Figure 4-7, you want the workbook to go immediately to the sheet called Start Here.

This macro uses the workbook’s Open event to start the workbook on the specified sheet when the workbook is opened.

Private Sub Workbook_Open()

'Step 1: Select the specified sheet.
Sheets("Start Here").Select

End Sub

The macro explicitly names the sheet the workbook should jump to when it's opened.

To implement this macro, you need to copy and paste it into the Workbook_Open event code window, as shown in Figure 4-8. (See the earlier section, “Installing Macros” for more information.) Placing the macro here allows it to run each time the workbook opens.

Opening a Specific Workbook Defined by the User

This macro uses the GetOpenFilename method to open a dialog box, allowing you to browse for and open the Excel file of your choosing.

This macro opens the dialog box shown in Figure 4-9, allowing the user to browse for and open an Excel file.

Here's how this macro works:

Sub OpenAWorkbook()

'Step 1: Define a variant variable.
Dim FName As Variant

'Step 2: GetOpenFilename Method activates dialog box.
FName = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks,*.xl*", _
Title:="Choose a Workbook to Open", _
MultiSelect:=False)

'Step 3: If a file was chosen, open it!
If FName <> False Then
Workbooks.Open Filename:=FName
End If

End Sub

1. Declares a variant variable that holds the filename that the user chooses. FName is the name of the variable.

2. The GetOpenFilename method calls a dialog box that allows you to browse and select the necessary file.

   The GetOpenFilename method supports a few parameters. The FileFilter parameter specifies the type of file you are looking for. The Title parameter changes the title that appears at the top of the dialog box. The MultiSelect parameter limits the selection to one file when False or allows multiple files when True.

3. If the user selects a file from the dialog box, the FName variable is filled with the name of the file they chose. If the user clicks Cancel, the variable is set to False. If it's not False, the Open method of the Workbooks object opens the file.

To implement this macro, you can copy and paste it into a standard module. (See the earlier section, “Installing Macros,” for details.)

Determining Whether a Workbook Is Already Open

The previous macro automatically opened a workbook based on the user’s selection. As you think about automatically opening workbooks, you must consider what may happen if you attempt to open a book that is already open. In the non-VBA world, Excel attempts to open the file again, with the message shown in Figure 4-10 warning that Excel can't have two workbooks open with the same name. You can protect against such an occurrence by checking whether a given file is already open before trying to open it again.

The first thing to notice about this macro is that it’s a function, not a Sub procedure. With a function, you can pass a value to the procedure that calls the function. In this case, the function passes either True or False, depending on whether the workbook is open.

The gist of this code is simple: You are testing a given filename to see whether it can be assigned to an object variable. Only opened workbooks can be assigned to an object variable. When you try to assign a closed workbook to the variable, an error occurs.

So if the given workbook can be assigned, the workbook is open; if an error occurs, the workbook is closed.

Function FileIsOpen(TargetWorkbook As String) As Boolean

'Step 1: Declare variables.
Dim TestBook As Workbook

'Step 2: Tell Excel to resume on error.
On Error Resume Next

'Step 3: Try to assign the target workbook to TestBook.
Set TestBook = Workbooks(TargetWorkbook)

'Step 4: If no error occurred then workbook is already open.
If Err.Number = 0 Then
FileIsOpen = True
Else
FileIsOpen = False
End If

End Function

1. Declares a Workbook variable to hold the open workbook if it exists. TestBook is the name of the variable.
2. Tells Excel that an error may be running this code. In the event of an error, the error is ignored and the macro continues running the rest of the code. Without this line, the code would simply stop when an error occurs.
3. Assigns the given workbook to the TestBook variable. The workbook you’re trying to assign is itself a string variable called TargetWorkbook. TargetWorkbook is passed to the function in the function declarations (see the first line of the code). This structure eliminates the need to hard-code a workbook name, allowing you to pass it as a variable instead.
4. If no workbooks are open with that name, an error occurs. Excel ignores the error because of the statement in Step 2, but it does keep track that an error occurred, even if it was ignored. The Err object has a Number property that holds the error number. If it's zero, there was no error so a workbook with that name was open. If it's anything except zero, assigning the workbook to the variable failed. The function's name is set to True or False depending on the error number.

The following macro demonstrates how to implement this function. Here, you are using the same macro you saw in the previous section, “Opening a Specific Workbook Defined by the User,” but this time, you are calling the new FileIsOpenTest function to make sure the user cannot open an already opened file.

Sub Macro1()

'Step 1: Define a string variable.
Dim FName As Variant
Dim FNFileOnly As String
'Step 2: GetOpenFilename method activates dialog box.
FName = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks,*.xl*", _
Title:="Choose a Workbook to Open", _
MultiSelect:=False)

'Step 3: Open the chosen file if not already opened.
If FName <> False Then
FNFileOnly = Dir(FName)
If FileIsOpen(FNFileOnly) = True Then
Workbooks(FNFileOnly).Activate
Else
Workbooks.Open Filename:=FName
End If
End If

End Sub

This macro uses the GetOpenFilename method to allow the user to browse for a file. If the user clicks Cancel, the If statement that starts Step 3 skips over the rest of the statements. If the user selects a file, the Dir function is used to get only the filename and not the path. Then the function is called to determine whether the file is open. If it is, the workbook stays closed. If not, it's opened.

To implement this macro, you can copy and paste both pieces of code into a standard module. (See the earlier “Installing Macros” section at the beginning of this chapter.)

Determining Whether a Workbook Exists in a Directory

You may have a process that manipulates a file somewhere on your PC. For example, you may need to open an existing workbook to add new data to it on a daily basis. In these cases, you may need to test to see whether the file you need to manipulate actually exists. This macro allows you to pass a file path to evaluate whether the file is there.

The first thing to notice about this macro is that it is a function, not a sub procedure. Making this macro a function enables you to return True or False to the procedure that calls it.

The preceding example used the Dir function to strip away the path and leave only the filename. That was possible because you could be sure the file exists — the user just selected it. You can also use Dir to determine whether the file exists:

Function FileExists(FPath As String) As Boolean

'Step 1: Declare your variables.
Dim FName As String

'Step 2: Use the Dir function to get the filename.
FName = Dir(FPath)

'Step 3: If file exists, return True else False.
If Len(FName) <> 0 Then
FileExists = True
Else
FileExists = False
End If

End Function

1. Declares a string variable to hold the filename that returned from the Dir function. FName is the name of the string variable.
2. Attempts to set the FName variable. Passes the FPath variable to the Dir function. This FPath variable is passed via the function declarations (see the first line of the code). This structure prevents you from having to hard-code a file path, passing it as a variable instead.
3. If the file exists, FName is the name of the file without the path. If it doesn't exist, FName is a zero length string. Check the length of the string with the Len function to determine whether Dir found the file.

The following macro demonstrates how to use this function:

Sub Macro1()

If FileExists("C:TempMyNewBook.xlsx") = True Then
MsgBox "File exists."
Else
MsgBox "File does not exist."
End If

End Sub

To implement this macro, you can copy and paste both pieces of code into a standard module. (See the earlier “Installing Macros” section at the beginning of this chapter.)

Closing All Workbooks at Once

One of the more annoying things in Excel occurs when you try to close many workbooks at once. For each workbook you have opened, you need to reference the workbook, close it, and confirm you want to save changes. There is no easy way to close them all down at one time. This little macro takes care of that annoyance.

This macro uses the Workbooks collection to loop through all the opened workbooks. As the macro loops through each workbook, it saves and closes them.

Sub Macro1()

'Step 1: Declare your variables.
Dim wb As Workbook

'Step 2: Loop through workbooks, save and close.
For Each wb In Workbooks
wb.Close SaveChanges:=True
Next wb

End Sub

1. Declares a Workbook object variable. This allows you to enumerate through all the open workbooks, capturing their names as you go.
2. The For Each keywords loops through the open workbooks, saving and closing them. If you don’t want to save them, change the SaveChanges argument from True to False.

The best place to store this macro is in your Personal Macro Workbook. (See the earlier “Installing Macros” section at the beginning of this chapter.) This way, the macro is always available to you. The Personal Macro Workbook is loaded whenever you start Excel. In the VBE Project Explorer, it’s named personal.xlsb.

Printing All Workbooks in a Directory

If you need to print from multiple workbooks in a directory, you can use this macro.

This code uses the Dir function to iterate through all the .xlsx files in a given directory, capturing each filename. Then you open each file, print it, and close the file.

Sub Macro1()

'Step 1: Declare your variables.
Dim MyFiles As String

'Step 2: Specify a target directory.
MyFiles = Dir("C:Temp*.xlsx")
Do While Len(MyFiles) > 0

'Step 3: Open Workbooks one by one.
Workbooks.Open "C:Temp" & MyFiles
ActiveWorkbook.Sheets("Sheet1").PrintOut Copies:=1
ActiveWorkbook.Close SaveChanges:=False
'Step 4: Next file in the directory.
MyFiles = Dir
Loop

End Sub

1. Declares the MyFiles string variable that captures each filename in the enumeration.
2. The Dir function specifies the directory and file type you are looking for. Note that the code here is looking for *.xlsx. The asterisk means that any number of characters can be in the filename as long as the last five are .xlsx. You can use the ? wildcard to specify any single character. For example, Dir(C:TempBudget2021??.xlsx) would find Budget202101.xlsx but not Budget202006.xlsx or Budget2021Jan.xlsx.
3. Opens the file and prints one copy of the first sheet. You may want to change which sheet prints. You can also change the number of copies to print. Because Dir only returns the filename, you have to repeat the path you used from the first time you called Dir in Step 2.
4. When you call Dir with no arguments, it looks for the next file in the directly using the file mask you specified the last time you used Dir. In this case, it's looking for the next file ending in .xlsx. If there are no more files, Dir returns an empty string and the Do While statement exits the loop.

To implement this macro, you can copy and paste it into a standard module. (See the earlier “Installing Macros” section at the beginning of this chapter.)

Preventing the Workbook from Closing Until a Cell Is Populated

There are times when you don’t want a user closing a workbook without entering a specific piece of data. In these situations, it would be useful to deny the user the ability to close the workbook until the target cell is filled in (see Figure 4-11). This scenario is where this macro comes in.

This code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. This macro checks to see whether the target cell (cell C7, in this case) is empty. If it is empty, the close process is cancelled. If C7 is not empty, the workbook saves and closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Step 1: Check to see if Cell C7 is blank.
If IsEmpty(Sheets("Sheet1").Range("C7").Value) Then
'Step 2: Blank: Cancel the close and tell the user.
Cancel = True
MsgBox "Cell C7 cannot be blank"

'Step 3: Not blank; save and close.
Else
ActiveWorkbook.Close SaveChanges:=True
End If

End Sub

1. Checks to see whether C7 is blank. The IsEmpty function returns True if the cell is blank.
2. If the cell is blank, sets the Cancel variable to True so Excel doesn't continue with the event's normal action (closing the workbook). Then a message box notifies the user of his stupidity (well, it's not quite that harsh, really).
3. If cell C7 is not blank, the workbook saves and closes.

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event Code pane, as shown in Figure 4-12. (See the earlier section, “Installing Macros,” for how to do this.) Placing the macro here allows it to run each time you try to close the workbook.

Creating a Backup of the Current Workbook with Today’s Date

Backing up your work is important. Now you can have a macro do it for you. This simple macro saves your workbook to a new file with today’s date as part of the name.

The trick to this macro is piecing together the new filename. The new filename has three pieces: the path, the original filename, and today’s date.

The path is captured by using the Path property of the ThisWorkbook object. Use the Name property of ThisWorkbook to get the name. Today's date is provided by the Date function.

You’re formatting the date (Format(Date, "yyyy-mm-dd")). This is because by default, the Date function returns mm/dd/yyyy. You need to use hyphens rather than forward slashes because the forward slashes would cause the file save to fail. (Windows does not allow forward slashes in filenames.) You also want to have the year first so your files sort properly.

The last piece of the new filename is the original filename. You use the Name property of the ThisWorkbook object to capture that:

Sub CreateBackup()

'Step 1: Save workbook with new filename.
ThisWorkbook.SaveCopyAs _
Filename:=ThisWorkbook.Path & "" & _
Format(Date, "yyyy-mm-dd") & Space(1) & _
ThisWorkbook.Name

End Sub

In the one and only step, the macro builds a new filename and uses the SaveCopyAs method to save the file.

To implement this macro, you can copy and paste it into a standard module. (See the earlier section, “Installing Macros,” for more information.)