When writing code in Excel VBA it is a good practice to first check for some conditions before taking certain actions. For example, when we write code to copy data to or from a range in an Excel file, it is important that we first check if the file is open. We can include a statement that opens the file in case it is closed.
In this tutorial we will look at 2 methods we can use to apply Excel VBA in checking whether a workbook is open or closed.
Method 1: Use a User Defined Function (UDF)
Excel VBA does not have an in-built function for checking whether files are open or not, but we can create a UDF to do the task. The UDF returns TRUE if the file is open and FALSE if it is not open.
Create the UDF
We use the following steps to create the UDF:
- In the active worksheet press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, click Developer >> Code >> Visual Basic.
- In the Project Window right-click the ThisWorkbook object and select Insert >> Module on the shortcut menu>
Alternatively, we click Insert >> Module on the menu bar.
- In the inserted module, type the following function procedure:
|
1 2 3 4 5 6 |
Function IsWorkBookOpen(Name As String) As Boolean Dim xlWb As Workbook On Error Resume Next Set xlWb = Application.Workbooks.Item(Name) IsWorkBookOpen = (Not xlWb Is Nothing) End Function |
- Save the function procedure and save the workbook as a macro-enabled workbook.
Explanation of the User-Defined Function
|
1 |
Function IsWorkBookOpen(Name As String) As Boolean. |
The function returns a value of Boolean data type. This means that it returns either a TRUE or FALSE value. The function takes one argument of the String data type.
One Workbook object variable is declared.
|
1 |
On Error Resume Next. |
If an error occurs, this statement instructs VBA to continue executing the statements immediately after the statement that caused the error.
The workbook to be checked is assigned to the workbook variable.
|
1 |
IsWorkBookOpen = (Not xlWb Is Nothing). |
This statement checks if the workbook variable has a workbook object and returns TRUE if it has one or FALSE if it is empty.
The TRUE or FALSE value is assigned to the IsWorkBookOpen variable and this is the value that is returned by the function.
Use the UDF
We can now use our UDF by calling it in a subroutine by using the following steps:
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a new module and type in the Sub procedure below:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub checkIfFileOpen() Dim xlCheck As Boolean Dim myWB As String myWB = InputBox(Prompt:="Enter the name of the workbook.") xlCheck = IsWorkBookOpen(myWB) If xlCheck Then MsgBox "The file is open", vbInformation Else MsgBox "The file is not open", vbInformation End If End Sub |
- Save the Sub procedure and save the workbook as a macro-enabled workbook.
- Place the cursor anywhere in the Sub procedure and press F5 to run the code.
- In the Input box that pops up, enter the name of the workbook that we want to check if it is open or closed and click OK. In this case, we have entered the name Suppliers of a closed workbook.
A message box pops up displaying the message that the workbook is not open.
When we open the Suppliers workbook and run the procedure again, the message box pops displaying the message that the file is open.
Explanation of the Sub procedure
- Two variables are declared, one of Boolean data type and the other of String data type.
- The InputBox function prompts for the name of the workbook.
- The name of the workbook is assigned to the myWB variable.
- The value in the myWB variable is passed to the IsWorkbookOpen function which returns TRUE if the file is open or FALSE if it is not open. Either of these values is assigned to the xlCheck variable.
- If the value in the xlCheck variable is TRUE, the MsgBox function sends a message box to the screen which displays the message The file is open. If the value in the xlCheck variable is FALSE, the MsgBox function sends to the screen a message box that displays the information The file is not open.
Method 2: Use a Sub procedure
We can also use a Sub procedure to check if a workbook is open or closed in Excel by using the following steps:
- Press Alt + F11 to open the Visual Basic Editor and insert a new module.
- In the module type the following Sub procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub checkWB() Dim WB As Workbook Dim myWB As String myWB = InputBox(Prompt:="Enter the workbook name.") For Each WB In Workbooks If WB.Name = myWB Then WB.Activate MsgBox "Workbook Is Open!" Exit Sub End If Next WB MsgBox "Workbook is not open" End Sub |
- Save the Sub procedure and save the workbook as a macro-enabled workbook.
- Place the cursor anywhere in the procedure and press F5 to run the procedure.
- In the Input box that pops up, enter the name of the workbook that we want to check if it is open or closed and click OK. In this case, we have entered the name Suppliers.xlsx of a closed workbook.
The message box displaying the information that the workbook is not open pops up on the screen.
If we open the Suppliers workbook and run the procedure again, the message box that displays the information that the workbook is open pops up on the screen.
Explanation of the subroutine
- A workbook variable and a string variable are declared.
- The InputBox function prompts the user for the name of the file that is to be checked.
- The input from the user is assigned to the myWB variable.
- For Each Next construct loops through all the open workbooks checking the value in the myWB variable against each of the names of the open workbooks collection.
- If a match is found, the relevant open workbook is activated and the MsgBox function sends a message box to the screen displaying the message Workbook is Open!, and the Subroutine is exited.
- If a match is not found, the MsgBox function sends a message box to the screen that displays the information that the Workbook is not open and the subroutine ends.
Conclusion
In this tutorial, we have explored two methods that we can use to check if a file is open or closed in Excel.
The first method involves the use of a User Defined Function and the second method applies a subroutine.