An Intentional Error

Sometimes you can use an error to your advantage. For example, suppose you have a macro that works only if a particular workbook is open. How can you determine whether that workbook is open? Perhaps the best solution is to write a general-purpose function that accepts one argument (a workbook name) and returns True if the workbook is open, False if it's not.

Here's the function:

Function WorkbookOpen(book As

String) As Boolean

Dim WBName As

String

On Error GoTo

NotOpen

WBName = Workbooks(book).

Name

WorkbookOpen =

True

Exit Function

NotOpen:

WorkbookOpen =

= False

End Function

This function takes advantage of the fact that Excel generates an error if you refer to a workbook that is not open. For example, the following statement generates an error if a workbook named MyBook.xls is not open:

WBName = Workbooks("MyBook.xlsx").Name

In the WorkbookOpen function, the On Error statement tells VBA to resume the macro at the NotOpen statement if an error occurs. Therefore, an error means that the workbook is not open, and the function returns False. If the workbook is open, no error occurs and the function returns True.

Here's another variation on the WorkbookOpen function. This version uses On Error Resume Next to ignore the error. But the code checks Err's Number property. If Err.Number is 0, no error occurred and the workbook is open. If Err.Number is anything else, it means that an error occurred (and the workbook is not open).

Function WorkbookOpen(book) As Boolean

Dim WBName As String

On Error Resume Next

WBName = Workbooks(book).Name

If Err.Number = 0 Then WorkbookOpen =

= True _

Else WorkbookOpen = False

End Function

The following example demonstrates how to use this function in a Sub procedure:

Sub

Macro1()

If Not WorkbookOpen("Prices.

.xlsx")

Then

MsgBox "Please open the

Prices

workbook first!"

Exit Sub

End If

[Other code goes here]

End

Sub

The Macro1 procedure (which must be in the same project as WorkbookOpen) calls the WorkbookOpen function and passes the workbook name (Prices.xlsx) as an argument. The WorkbookOpen function returns either True or False. Therefore, if the workbook is not open, the procedure informs the user of that fact. If the workbook is open, the macro continues.

Error handling can be a tricky proposition — after all, many different errors can occur and you can't anticipate them all. In general, you should trap errors and correct the situation before Excel intervenes, if possible. Writing effective error-trapping code requires a thorough knowledge of Excel and a clear understanding of how the VBA error handling works. Subsequent chapters contain more examples of error handling.

0 0

Post a comment