Excel error dialogue still pops up even with error handling

Question

I seem to be having a unique problem that I have not had before and cannot find an answer through google.

I wrote a little subroutine to simply open a specific workbook that contains the word "Control" in it.

Problem: despite having an error handler that works, I get a pop-up box from excel saying it could not open a workbook.

Here is the code:

Sub OpenControlSheet(index As Integer)
Dim strFilePath As String
Dim strFileName As String

On Error GoTo ErrorHandler

strPlan = arrPlanPeriods(index)

ResumePoint:
strFilePath = "S:\Misc. Budget\" & strPlan & "\" & "*Control*"
strFileName = Dir(strFilePath)
ResumePoint2:
Set wb = Workbooks.Open("S:\Misc. Budget\" & strPlan & "\" & strFileName)

ErrorHandler:
If Err.Number <> 0 Then
    'Do stuff
End if

End sub

Basically what the error handler does is manipulate the file path until the correct name is found. For example, if "92 Pol" did not work, then try "92 Poll." But before trying the new path I always get the following error dialogue:

"Could not open 'S:\Misc. Budget\92 Pol'"

Then it goes through the error handler and opens the correct workbook.

I thought if it goes through an error handler then I should not have any error dialogue boxes pop up. Any idea why this might be happening?

Answer 1

If an error is raised inside the error handling part, it is not trapped by any error handling code, and produces a standard VBA error message.

Sub errors()
On Error GoTo ErrorHandler
  x = 1 / 0
ErrorHandler:
  x = 1 / 0 ' Error message will pop up here
End Sub

Now, using a goto statement (I'm just assuming you did something like this, as you didn't show that part of the code) will not get you - logically - out of the error handler. Take this example:

Sub errors()
start:
  On Error GoTo ErrorHandler
    x = 1 / 0 ' Error will pop up here ON THE SECOND GO
ErrorHandler:
  GoTo start
End Sub

An error message will pop up, even though you can see On Error GoTo ErrorHandler being called again. No, it is not going to be an infinite loop!

On the other hand, using the Resume keyword takes you logically out of the error handling part:

Sub errors()
  On Error GoTo ErrorHandler
    x = 1 / 0

TryAgain:
  On Error GoTo TotalFailure
  x = 1 / 0

ErrorHandler:
  Resume TryAgain

TotalFailure:
  MsgBox "Nyekk."

End Sub

So, make sure that no error can happen in your error handling code part. VBA is pretty lame in handling errors.

Try stepping through my examples with F8.

Also, this setting can cause some strange behaviour:

Answer 2

There are 2 ways to resolve your problem.
The first one is use

On Error Resume Next

On Error Resume Next will execute next line of code even if the error is occur, Then you can check the error using

If Err.Number <> 0 Then

The example code is below:

Sub OpenControlSheet(index)
Dim strFilePath As String
Dim strFileName As String

' Add this line above the code 
On Error Resume Next

strPlan = arrPlanPeriods(index)

strFilePath = "S:\Misc. Budget\" & strPlan & "\" & "*Control*"
strFileName = Dir(strFilePath)

' You will go through Workbooks.Open() even if error is occured
Set wb = Workbooks.Open("S:\Misc. Budget\" & strPlan & "\" & strFileName)
If Err.Number <> 0 Then
    'Do stuff
End If
End Sub

Second method is check that the file is exists or not before open the file.
As you use

strFileName = Dir(strFilePath)

This will return file name if it exists and it will return empty string string if the file or path is not exists.

Example code:

Sub OpenControlSheet(index)
Dim strFilePath As String
Dim strFileName As String

strPlan = arrPlanPeriods(index)

strFilePath = "S:\Misc. Budget\" & strPlan & "\" & "*Control*"
strFileName = Dir(strFilePath)

If strFileName <> "" Then
    ' Path/File exists
    Set wb = Workbooks.Open("S:\Misc. Budget\" & strPlan & "\" & strFileName)
Else
    ' Path/File not exists
    ' Do stuff
End If
End Sub

Hope this make you happy.