Microsoft Office Tutorials and References
In Depth Information
Error-Handling Techniques
the Exit Sub statement before the label. This statement prevents the error-handling code from
being executed if no error occurs. If this statement is omitted, the error message is displayed
even if an error does not occur.
Sometimes, you can take advantage of an error to get information. The example that follows
simply checks whether a particular workbook is open. It doesn’t use any error handling.
Sub CheckForFile1()
Dim FileName As String
Dim FileExists As Boolean
Dim book As Workbook
FileName = “BUDGET.XLSX”
FileExists = False
‘ Cycle through all open workbooks
For Each book In Workbooks
If UCase(book.Name) = FileName Then FileExists = True
Next book
‘ Display appropriate message
If FileExists Then
MsgBox FileName & “ is open.”
Else
MsgBox FileName & “ is not open.”
End If
End Sub
Here, a For Each-Next loop cycles through all objects in the Workbooks collection. If the
workbook is open, the FileExists variable is set to True . Finally, a message is displayed that
tells the user whether the workbook is open.
You can rewrite the preceding routine to use error handling to determine whether the file is
open. In the example that follows, the On Error Resume Next statement causes VBA to
ignore any errors. The next instruction attempts to reference the workbook by assigning the
workbook to an object variable (by using the Set keyword). If the workbook isn’t open, an error
occurs. The If-Then-Else structure checks the value property of Err and displays the
appropriate message. This procedure uses no looping, so it’s slightly more efficient.
Sub CheckForFile()
Dim FileName As String
Dim x As Workbook
FileName = “BUDGET.XLSX”
On Error Resume Next
Set x = Workbooks(FileName)
If Err = 0 Then
MsgBox FileName & “ is open.”
Else
MsgBox FileName & “ is not open.”
End If
On Error GoTo 0
End Sub
 
Search JabSto ::




Custom Search