Basic Error Handling with the On Error GoTo Syntax

3. Write the code to handle the error. If you want to return control of the macro to the line after the one that caused the error, use the statement Resume Next.

In your macro, just before the line that may likely cause the error, add a line reading On Error GoTo MyErrorHandler. Note that in this line, you do not include the colon after the label name.

Immediately after the line of code that you suspect will cause the error, add code to turn off the special error handler. This is very non-intuitive and tends to confuse people. The code to cancel any special error handling is On Error Goto 0. There is no label named zero. This line is a fictitious line that instructs Excel to go back to the normal state of displaying the End/Debug error message when an error is encountered. Do you see why it is important to cancel the error handling? In the following code, we've written a special error handler to handle the necessary action if the file has been moved or is missing. We definitely do not want this error handler invoked for another error later in the macro, such as a division by zero.

Sub HandleAnError()

Dim MyFile as Variant

' Set up a special error handler

On Error GoTo FileNotThere

Workbooks.Open Filename:="C:\NotHere.xls"

' If we get here, cancel the special error handler

On Error GoTo 0

MsgBox "The program is complete"

1 The macro is done. Use Exit sub, otherwise the macro ' execution WILL continue into the error handler Exit Sub

' Set up a name for the Error handler FileNotThere:

MyPrompt = "There was an error opening the file. It is possible the " MyPrompt = MyPrompt & " file has been moved. Click OK to browse for the " MyPrompt = MyPrompt & "file, or click Cancel to end the program" Ans = MsgBox(Prompt:=MyPrompt, VbMsgBoxStyle:=vbOKCancel) If Ans = vbCancel Then Exit Sub

1 The client clicked OK. Let him browse for the file MyFile = Application.GetOpenFilename If MyFile = False Then Exit Sub

1 What if the 2nd file is corrupt? We don't want to recursively throw ' the client back into this error handler. Just stop the program On Error GoTo 0 Workbooks.Open MyFile

' If we get here, then return the macro execution back to the original ' section of the macro, to the line after the one that caused the error. Resume Next

0 0

Post a comment