Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
For example, the following function is used for checking whether a workbook is open:
Function BookOpen(Bk As String) As Boolean
Dim T As Excel.Workbook
Err.Clear
On Error Resume Next
Set T = Application.Workbooks(Bk)
BookOpen = Not T Is Nothing
Err.Clear
On Error GoTo 0
End Function
Are these two Err.Clear
statements necessary?
none of the uses is appropriate, because On Error
resets the last error, so Err.Clear
is redundant.
It's appropriate after actually handling a failed statement.
Function BookOpen(Bk As String) As Boolean
Dim T As Excel.Workbook
On Error Resume Next
Set T = Application.Workbooks(Bk) ' this can fail...
' so handle a possible failure
If Err.Number <> 0 Then
MsgBox "The workbook named """ & Bk & """ does not exist."
Err.Clear
End If
BookOpen = Not T Is Nothing
End Function
If you have On Error Resume Next
in effect, the program will continue after an error as if nothing had happened. There is no exception thrown, there is no warning, this is not structured error handling (i.e. it's nothing like try
/catch
blocks). Your program might end up in a very weird state if you don't do rigorous error checks.
This means you must check errors after. every. statement. that. can. fail. Be prepared to write a lot of If Err.Number <> 0 Then
checks. Note that this is harder to get right than it seems.
Better is: Avoid long sections of code that have On Error Resume Next
in effect like the plague. Break up operations into smaller functions/subs that do only one thing instead of writing a big function that does it all but can fail halfway through.
In short: Err.Clear
makes your program behave predictably after a failed statement in an On Error Resume Next
block. It marks the error as handled. That's its purpose.
Of course in your sample it's easy to avoid error handling by using the commonly accepted way of checking whether a workbook (i.e. member of a collection) exists.
Function BookOpen(Bk As String) As Boolean
Dim wb As Variant
BookOpen = False ' not really necessary, VB inits Booleans to False anyway
For Each wb In Application.Workbooks
If LCase(wb.Name) = LCase(Bk) Then
BookOpen = True
Exit For
End If
End Function
–
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.