Return to VBA Code Examples
In this Article
- Creating a Simple Custom Error Message
- Creating a Custom Error Message Depending on User Input
- Replacing the Custom Excel Error Message with a Custom Message
This tutorial will demonstrate how to raise a custom error in VBA.
Custom errors can be created in VBA code when the programmer wants to return a specific message to the user, rather than relying on the default error message box that will pop up, or when the user actually wants to show a custom error if a certain value is inputted into a variable or variables in the code.
Creating a Simple Custom Error Message
The Err. Raise method allows us to customize the error number and the error description in our code.
Sub TestRaiseError()
On Error GoTo eh
If Range("A1") <> "Fred" Then
Err.Raise vbObjectError + 1000, , "The text in the cell A1 should say Fred."
End If
Exit Sub
eh:
MsgBox "User Error: " & Err.Description
End SubWe need to raise a custom error number that we have documented ourself. We use the constant vbObjectError in addition to our custom number to ensure that we do not end up using any of the error numbers that are reserved by VBA for internal use.
Creating a Custom Error Message Depending on User Input
We can raise an error that will return a specific message – depending on what information is entered into the code.
First, we can create this function:
Function TestCustomError(x As Integer, y As Integer)
If y - x > 50 Then
Err.Raise vbObjectError + 50, "in My workbook", "The difference is too small"
ElseIf y - x < 50 Then
Err.Raise vbObjectError - 55, "in My workbook", "The difference is too large"
End If
End FunctionThen we can create this code to test the function:
Sub TestErrRaise()
On Error GoTo eh
TestCustomError 49, 100
Exit Sub
eh:
MsgBox ("User Error: " & vbCrLf & Err.Description & vbCrLf & Err.Source)
End SubAs the difference between the numbers 49 and 100 is greater than 50, the custom error description returned will be “The difference is too large“.
If we were to amend this line in the code:
TestCustomError 55, 100Then the custom error description returned would be “The difference is too small”.
If we were then to amend the line of code to read:
TestCustomError 50, 100Then no error would be returned by the function TestCustomError.
Replacing the Custom Excel Error Message with a Custom Message
You can use existing Excel error to create your own custom message to return to the user.
Take the example of the code below:
Sub CustomMessage()
Dim x As Integer, y As Integer
x = 100
y = 0
MsgBox x / y
End SubThis will result in the following error being returned:
However, we can customize the message “Division by zero” by altering the code as per the example below:
Sub CustomMessage()
On Error GoTo eh
Dim x As Integer, y As Integer
x = 100
y = 0
MsgBox x / y
Exit Sub
eh:
Err.Raise Err.Number, , "You cannot divide by zero - please amend your numbers!"
End Sub
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Learn More!
If VBA can’t execute a statement (command) then a run-time error occurs. By default Excel deals with these, so when a run-time error occurs, you’ll see a default error message like this:
But you can change this and instruct Excel to allow your code to deal with run-time errors.
NOTE : I’m going to use the terms sub, function and procedure interchangeably. For the purposes of this article they all mean the same thing – a chunk of code written to do a particular thing.
The On Error Statement
To instruct Excel what to do when an error occurs, you use the On Error statement. You can use On Error in four ways:
On Error GoTo 0 On Error Resume Next On Error GoTo [label] On Error GoTo -1
On Error GoTo 0
This is the default mode and is already turned on when you start writing your code. You don’t need to use an On Error GoTo 0 statement at the start of your VBA.
In this mode VBA displays the standard style error message box, and gives you the choice to Debug the code (enter VBA editor and use debugging tools) or End code execution.
You would use On Error GoTo 0 to turn default error handling back on if you have previously told VBA to deal with errors in some other way e.g. by using On Error Resume Next.
On Error Resume Next
On Error Resume Next tells VBA to continue executing statements immediately after the statement that generated the error.
On Error Resume Next allows your code to continue running even if an error occurs. Resume Next does not fix an error, it just ignores it. This can be good and bad.
The Good?
If you know that your code could generate an error, then using Resume Next can prevent an interruption in code execution.
For example, we want to create a file, but I want to make sure a file with the same name doesn’t already exist. To do this, I will attempt to delete the file, and of course if it doesn’t already exist, an error will occur.
I don’t care if an error occurs. If it does, the file doesn’t exist and that’s fine for what I want to do. So before I attempt to delete the file, I instruct VBA to ignore the error.
Sub DeleteFile()
Dim FilePath As String
FilePath = "d:\temp\somefile.csv"
On Error Resume Next
'Delete the file
Kill FilePath
'If the file doesn't exist the Kill statement
'generates an error.
'But I have assumed that is ok as it indicates that
'I am ok to create a new file with the same filename
'This is a bad assumption - see the next sub
'UnhandledError()
End Sub
The Bad
But hang on. What if the file I am trying to delete is read only? If it is I will get an error when I try to delete it. I’ve assumed that I will only get an error if the file isn’t there. So an error caused by trying to delete a read only file will get missed.
And If I then try to create a new file with the same name, or open it for writing data to it, I will generate more errors and they will be missed too.
Sub UnhandledError()
Dim FilePath As String
FilePath = "d:\temp\somefile.csv"
On Error Resume Next
'Delete the file
Kill FilePath
'But if the file is read-only I can't delete it
'and the Kill statement generates an error
'I can't now create a new file with the same name
'Trying to Open the file for writing data to it will
'also generate an error but it will be missed as
'we've told VBA to continue executing code if an
'error occurs
Open FilePath For Output As #1
Write #1, "Some data"
Close #1
End Sub
If you do use On Error Resume Next you should immediately turn default error handling back on (or turn on your custom error handler – see below)
Be careful when using On Error Resume Next. You are better off seeing if an error occurred by checking the Err object (see below). Doing this can tell you the error number and help you figure out exactly what happened.
On Error GoTo [LABEL]
If an error occurs, this transfers code execution to the line following the label. This is typically used to specify your own error handling code.
None of the code between the line generating the error and the label is executed.
Error Handlers
So you write your own error handling code and use On Error GoTo [LABEL] to instruct VBA to use your code to deal with errors.
You can place your error-handling code anywhere in a procedure, but typically it is placed at the end.
Your error handler should either fix the error and resume code execution, or terminate the routine gracefully.
Sub ErrorHandler()
Dim num As Integer
On Error GoTo ErrHandler
num = 1 / 0
MsgBox "This line is not executed"
Exit sub
ErrHandler:
MsgBox "Oops, an error has occured." & vbCrLf & vbCrLf & "Error Code : " & Err.Number & " , " & Err.Description
End Sub
As VBA will execute each line of code in turn going from top to bottom, if no error is generated then it will execute your error handling code when it gets to that point in your sub.
To prevent this happening, use an Exit Sub, Exit Function, or Exit Property statement before your error handling routine.
In the example above, if the value assigned to num was valid e.g. num = 1/1, then we don’t want the code beneath ErrHandler: executed. So just before the ErrHandler: label, I’ve used an Exit Sub statement.
Multiple Error Handlers
You can have more than one error handler in a routine, but only one of them can be active at any time.
You could have something like:
Sub MultipleErrorHandlers() On Error GoTo ErrHandler1 [some code] On Error GoTo ErrHandler2 [some code] Exit Sub ErrHandler1: [ErrHandler1 Code] Exit Sub ErrHandler2: [ErrHandler1 Code] Exit Sub End Sub
If an error occurs between On Error GoTo ErrHandler1 and On Error GoTo ErrHandler2 then the ErrHandler1 code is executed.
If an error occurs after On Error GoTo ErrHandler2 then the ErrHandler2 code is executed.
NOTE: Notice that at the end of each error handling routine is an Exit Sub statement. If I didn’t use these, when the ErrHandler1 code is finished executing, VBA could just continue on down to the next line and execute the ErrHandler2 code as well.
Strictly speaking I don’t need the Exit Sub at the end of the ErrHandler2 code, as it is the last line in the sub, but it is a good habit to get into.
Err object
When an error occurs the Err object contains information about the error like the error number and a description of the error.
As any given line of code can generate multiple errors it’s a good idea to examine the Err object to determine what you want to do in your code.
Err.Number gives you the error number, and Err.Description gives you a description of the error.
Sub CheckErrObject()
Dim FilePath As String
FilePath = "d:\temp\somefile.csv"
On Error GoTo ErrHandler
'Delete the file
Kill FilePath
Open FilePath For Output As #1
Write #1, "Some data"
Close #1
Exit Sub
ErrHandler:
Select Case Err.Number
Case 53 ' File doesn't exist
Err.Clear ' Clear the error
Case 75 ' File is Read Only
MsgBox "Error Number : " & Err.Number & vbCrLf & vbCrLf & Err.Description
Exit Sub
Case Else
' Code to handle other errors
End Select
Resume Next ' Continue executing code after line that generated error
End Sub
Resume
The Resume statement tells VBA to resume executing code at a specified point. Resume can only be used in an error handling routine, any other use will generate an error.
Resume takes three forms:
Resume Resume Next Resume [label]
Using just Resume causes execution to resume at the same line of code that caused the error. If you haven’t fixed the error, your code will begin an infinite loop as it switches between the line of code generating the error and the error handling routine.
If you look at the example sub Resume_Next() which is below, num = 1 / 0 causes a Divide by 0 error. I’ve instructed VBA to use my error handler, called ErrHandler.
In ErrHandler I’ve attempted to fix the error by assigning num the value 1. If I then used only Resume, all that would happen is that VBA would go back to num = 1 / 0 and another Divide by 0 error would be generated.
Instead, I use Resume Next to carry on executing code at the line after the one causing the error.
In doing so I have handled the error by assigning the value 1 to num, and execution will continue without another error at the line result = num / 1
Sub Resume_Next()
Dim num As Integer
Dim result As Integer
On Error GoTo ErrHandler
num = 1 / 0
result = num / 1
Exit Sub
ErrHandler:
num = 1
Resume Next
End Sub
Resume [label] passes code execution to the line with that label.
Sub Resume_Next()
Dim num As Integer
Dim result As Integer
On Error GoTo ErrHandler
num = 1 / 0
result = num / 1
MyLabel:
‘Code execution starts again from here
result = num * 1
Exit Sub
ErrHandler:
num = 1
Resume MyLabel
End Sub
Whenever you use Resume it clears the Err object.
Error Handling With Multiple Procedures
Every sub/function doesn’t have to have an error handler. If an error occurs, VBA will use the last On Error statement to determine what happens.
If an On Error statement has been used in a procedure and an error occurs in that procedure, then that error is handled as I’ve just described.
But if an error occurs in a sub that hasn’t used an On Error statement, VBA goes back through procedure calls until it finds an On Error directive.
Let’s look at an example with three subs.
SubOne() calls SubTwo(). SubTwo calls SubThree(), and has some code of its own to execute. SubThree() carries out a calculation.
Sub SubOne()
On Error GoTo ErrHandler
SubTwo
Exit Sub
ErrHandler:
MsgBox "Error caught in SubOne"
End Sub
Sub SubTwo()
SubThree
MsgBox "No errors here"
End Sub
Sub SubThree()
Dim num As Integer
num = 1 / 0
End Sub
SubOne() has an error handler routine, ErrHandler, and has instructed VBA to use it.
SubTwo() will display a message on screen after it’s call to SubThree() has finished.
However SubThree() generates a Divide by 0 error.
SubThree() hasn’t used an On Error statement to tell VBA what to do if an error occurs, so VBA goes back to SubTwo(). That also doesn’t have an On Error statement so VBA goes back to SubOne().
Here we have our error handler and the code in it is executed.
Note that the message «No errors here» in SubTwo() is not displayed because that line of code is not executed.
When SubThree() generated an error, code execution went back to the error handler in SubOne() and any code in SubTwo() after the call to SubThree() is missed out.
On Error GoTo -1
This resets the current error. It’s the equivalent of using Err.Clear.
You can see that in this sub, after the Divide By 0 error is generated, after On Error GoTo -1 is used, Err.Number is 0 (no error) and Err.Description is empty.
Sub GoToMinus1()
Dim num As Integer
On Error Resume Next
num = 1 / 0
'If error is Divide by Zero
If Err.Number = 11 Then
MsgBox "Error Code : " & Err.Number & " , " & Err.Description
On Error GoTo -1
MsgBox "Error Code : " & Err.Number & " , " & Err.Description
End If
End Sub
Sample Code
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Not all errors are bad. Many young programmers look at errors as something to be avoided at all costs. That’s a shame because there are many times when raising an error is the best way to convey intent. This technique works especially well with the global error-handling capabilities of vbWatchdog.
Something Borrowed
One of the best ways to improve as a programmer is to write code in different languages. The reason is that it gives you a chance to see a variety of ways to deal with common problems. Oftentimes, you can import the concepts from those other languages into VBA. I’ve done this in the past with Python’s doc tests feature.
This time I’m taking a page from C#. In C#, one can raise errors (exceptions, in C#) via the throw statement. In fact, Visual Studio will automatically generate such statements when it creates new methods. For example:
public void NewMethod()
{
throw new NotImplementedException();
}
Problems with Err.Raise
Of course, VBA has a way to do this using the Err.Raise method. It’s fine to use once in awhile, but I found myself avoiding using it because of the extra friction it introduced into my programming flow.
You see, Err.Raise requires passing an error number. As far as I can tell, this is mainly for providing a unique way to refer to errors. The error number must be a number within the range 513–65,535. Thus, according to the pigeonhole principle, we cannot be guaranteed a truly unique error number. Given a large enough project, it would be possible (even if highly unlikely) to exhaust the entire pool of error numbers.
That said, if you are using any sort of bug tracking database (you should), then you probably want to be able to keep track of the number of instances of a particular error. This is easier to do if you are assigning relatively unique error numbers. That’s easier said than done, though.
We could keep an Enum type with a different enum member for every kind of error. That introduces friction. I tried this early in my programming journey, but I didn’t consistently use it because it would break me out of my flow. Then there’s the question of how best to initialize the numbering of the Enum types. Does each module get its own Enum type? What about modules from our code library? How do we keep the error numbers unique among multiple projects?
Keeping track of unique error numbers in some sort of systematic way never worked for me. I could have made it work, but it was so annoying that I didn’t bother. I started just randomly typing numbers whenever I would call the method:
Err.Raise vbObjectError + 31257, , "Not implemented yet."
Err.Raise vbObjectError + 4412, , "Object not initialized."
Bug Database Integration Issues
Early on, I adopted FogBugz as a bug-tracking database. One of the features I like best is their BugzScout tool. It’s a REST API for submitting bug reports. The best part of this tool is that it will track the number of occurrences of a single bug based on the bug title. I used the Err.Description as the bug title. If it was a unique string, FogBugz would assign it a new case number. Otherwise, it would append it to an existing case and increment the occurrence count.
This worked great for most errors, but it had a fatal flaw. If the error message contained some sort of variable string, then the same error would generate many unique cases. For example, a common problem would be a missing file. The error message would be something like, «Cannot find file: ‘C:\Temp\Readme.txt'». The same exact error would generate a second case number with a message like, «Cannot find file: ‘C:\Temp\Readme (1).txt'». It was the same error, but FogBugz treated it as unique because of the differing string values.
To work around this issue, I adopted the naming convention of many Access errors:
?AccessError(2102)
The form name '|' is misspelled...
Builtin Access errors that support variables always surround those variables with single quotes. Thus, to avoid the problem of generating too many unique error cases, I started replacing the contents of instance-specific text with pipe characters before submitting the error message to FogBugz. (NOTE: The RegExReplace function below is a wrapper around the VBScript’s regex functionality.)
?RegExReplace("($|[^a-zA-Z])'[^']+'", "Can't load file: 'C:\Users\Mike\Temp.txt'. File not found.", "$1'|'")
Can't load file: '|'. File not found.
This little trick helped avoid bug case proliferation. If you are using a local RDBMS as a bug tracking database, you may have run into a similar problem. I liked this solution, so I wanted a way to incorporate it into my Err.Raise replacement function.
Throw in VBA
I tried many times to make Err.Raise work for me. It never did. I still liked the concept of throwing errors, but I needed a better way to achieve that goal. I needed a solution that met the following requirements:
- Easy to use («frictionless»)
- Automatically creates relatively unique error numbers
- Allows for instance-specific error information
- Generates a generic message stripped of instance-specific strings
Enter the Throw() function. Here’s the overview of how it works:
The procedure automatically generates an error number within the required range by calculating a simple hash of the generic error message. Instead of passing instance-specific strings in the error message, you would use placeholders like {0}, {1}, etc. The corresponding values would be passed as a ParamArray to the function.
To increase readability of the calling code, the error message may contain \t and \n escape sequences, which the Throw procedure will replace with Tab and Newline characters, respectively.
For example, the following line of code will generate the vbWatchdog screenshot below:
Throw "Could not find file {0} for account:\n\n{1}", "C:\Temp\MyFile.txt", "123456"
Here’s another example:
Throw "Not implemented yet"
Frictionless Integration
As you can see, the Throw() procedure meets all the requirements from our list above. The one major drawback to the approach is that the error ends up being thrown from the Throw() procedure itself and not from the calling procedure. This is not a big deal with vbWatchdog, since the entire call stack is available to us. We can simply pull the calling procedure off of the vbWatchdog live call stack.
Now that I have a frictionless way to throw errors in VBA, I use the technique regularly. I hope you’ll consider using this technique, as it’s an important line of defense when programming defensively.
Sample Code
Private Const mDO_NOT_REPORT_ERR_NUM As Long = 65535
'---------------------------------------------------------------------------------------
' Procedure : Throw
' Author : Mike
' Date : 7/24/2014 - 12/7/2016
' Purpose : Throws a new error, calculating a unique error number by hashing the Msg.
' Notes - Msg may contain one or more placeholders which will be substituted
' with contents of the Notes() array.
' - Contents of the Notes array will be enclosed in single quotes when they are
' added to show that they are custom for a particular instance.
' - The placeholders may contain quotes around them but it is not necessary.
' - Tabs and newlines can be passed using escape sequences, \t and \n
' Usage : Throw "Could not find file {0} for account:\n\n{1}", FName, AcctNum
' Results : Could not find file 'C:\Temp\MyFile.txt' for account:
'
' '123456'
'---------------------------------------------------------------------------------------
'
Public Sub Throw(Msg As String, ParamArray Notes() As Variant)
Dim s As String, i As Integer
s = Msg
s = Replace(s, "\t", vbTab)
s = Replace(s, "\n", vbNewLine)
For i = LBound(Notes()) To UBound(Notes())
Dim ReplTxt As String
ReplTxt = "'" & Notes(i) & "'"
s = Replace(s, "'{" & i & "}'", ReplTxt)
s = Replace(s, "{" & i & "}", ReplTxt)
Next i
Err.Raise ErrNumFromMsg(Msg), , s
End Sub
'---------------------------------------------------------------------------------------
' Procedure : Alert
' Author : Mike
' Date : 12/29/2015 - 8/18/2017
' Purpose : Throws a new error using a reserved error number to prevent FogBugz reporting.
' Notes - This is exactly the same as the above Throw() procedure, but it will not
' generate a FogBugz report.
' - Use this when you want to inform the user of an error and have it bubble up the
' call stack, but you're not interested in seeing it show up in a FogBugz error report.
' - These errors will still be logged to text files or local databases if that
' is set up; only the FogBugz reporting is affected.
'---------------------------------------------------------------------------------------
'
Public Sub Alert(Msg As String, ParamArray Notes() As Variant)
Dim s As String, i As Integer
s = Msg
s = Replace(s, "\t", vbTab)
s = Replace(s, "\n", vbNewLine)
For i = LBound(Notes()) To UBound(Notes())
Dim ReplTxt As String
ReplTxt = "'" & Notes(i) & "'"
s = Replace(s, "'{" & i & "}'", ReplTxt)
s = Replace(s, "{" & i & "}", ReplTxt)
Next i
'--== Uncomment for use with vbWatchdog ==--
'With ErrEx.LiveCallstack
' .FirstLevel
' If .NextLevel Then
' mAlertCaller = " (" & .ModuleName & "." & .ProcedureName & ")"
' Else
' mAlertCaller = vbNullString
' End If
'End With
'---------------------------------------------
Err.Raise mDO_NOT_REPORT_ERR_NUM, , s
End Sub
'---------------------------------------------------------------------------------------
' Procedure : ErrNumFromMsg
' Author : Mike
' Date : 9/22/2015
' Purpose : Converts a msg into a somewhat-unique hash. This is a public convenience
' function primarily made available for testing.
'---------------------------------------------------------------------------------------
'
Public Function ErrNumFromMsg(Msg As String) As Long
ErrNumFromMsg = vbObjectError + GenerateErrNumFromMsgHash(Msg)
End Function
'---------------------------------------------------------------------------------------
' Procedure : GenerateErrNumFromMsgHash
' Author : Mike
' Date : 7/24/2014
' Purpose : Given a string of text, reliably reproduces a number between 513–65535
' to use when generating user-defined errors.
' Notes - This is an overly simplistic hash method. It is likely rife with potential
' collisions. However, we are not too concerned with the occasional
' collision, so it seems like a good trade-off.
' Usage :
'>> GenerateErrNumFromMsgHash("Could not find file {0} for account {1}")
' 41945
'---------------------------------------------------------------------------------------
'
Private Function GenerateErrNumFromMsgHash(Msg As String) As Long
Const ValRange As Long = 65535 - 513
Const ShiftVal As Long = 513
Dim i As Long, Val As Long
For i = 1 To Len(Msg)
Val = (Val + (Asc(Mid(Msg, i, 1)) ^ 2)) Mod ValRange
Next i
GenerateErrNumFromMsgHash = Val + ShiftVal
If GenerateErrNumFromMsgHash = mDO_NOT_REPORT_ERR_NUM Then
'make sure we don't accidentally prevent a reportable message from being reported
GenerateErrNumFromMsgHash = mDO_NOT_REPORT_ERR_NUM - 1
End If
End Function
Image by Sarah Richter from Pixabay
Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
While writing Excel Macros we generally put more focus on the coding part and getting the desired result but during this process, we forget an important thing i.e. Error handling. Error handling is an important part of every code and VBA On Error Statement is an easy way for handling unexpected exceptions in Excel Macros.
A well-written macro is one that includes proper exception handling routines to catch and tackle every possible error. Error handling is important because in case of any unexpected exceptions your code doesn’t break. Even if any fatal unexpected error occurs in the code then also you should ensure that the code should terminate gracefully.
Definition of VBA On Error Statement:
On Error statement instructs VBA Compiler, what to do in case any runtime exception is thrown.
Syntax of On Error Statement:
Basically, there are three types of On Error statement:
- On Error Goto 0
- On Error Resume Next
- On Error Goto<label>:
On Error Goto 0
This is also called VBA default exception handling. When On Error Goto 0 is in effect, it is the same as having no error handler in the code. Here we are instructing the program to display the standard runtime message box with ‘Continue’, ‘End’, ‘Debug’, and ‘Help’ buttons.
This message box will give you four options:
a. Continue: This will ignore the exception and continue the code, only if it is possible to do so.
b. End: This will terminate the program.
c. Debug: This option will bring the program control back to the statement from where the exception has occurred. This helps you to debug the code.
d. Help: This button will open Microsoft MSDN help pages for that exception.
On Error Resume Next
It is the second form of the On Error statement. This statement tells the VBA program to ignore the error and resume the execution with the next line of code.
On Error Resume Next statement doesn’t fix the runtime errors but it simply means that program execution will continue from the line following the line that caused the error. However, it is the sole responsibility of the programmer to make sure that any handled error should not have any side effects (like uninitialized variables or null objects) on the program execution.
This can be ensured by using the VBA Err object. Err object in VBA comes into the picture whenever any runtime error occurs. The Err object preserves information about one exception at a time. When an exception occurs, the Err object is updated to include information about that exception.
For instance:
For example, I have a simple macro as follows:
Sub GetErr()
On Error Resume Next
N = 1 / 0 ' Line causing divide by zero exception
For i = 1 To N
'SomeSet of Statements
Next i
End Sub
Now as you can clearly see, that in this macro Line 3 causes an exception. But as we are using the On Error Resume Next statement so this line will be skipped and the control will flow to the next statement. But the next statement is a loop that is dependent on the value of ‘N’, and at this step ‘N’ is uninitialized so this will have a side effect on the whole flow of the program.
Now, have a look at the same program after exception handling:
Sub GetErr()
On Error Resume Next
N = 1 / 0 ' Line causing divide by zero exception
If Err.Number <> 0 Then
N = 2 ' Some minimum value of N if there is some exception in the code.
End If
For i = 1 To N
'SomeSet of Statements
Next i
End Sub
Now, here in this code, we are checking the Err. Number property, if it is not equal to zero that means there is some exception in the code. And hence we have set ‘N’ to its minimum value so that there are no side effects in the code due to uninitialized variables.
On Error Goto<label>:
This is the third form in which VBA On Error statement can be used. This statement tells the VBA to transfer the program control to the line followed by the label, in case any runtime errors are encountered. In such cases, all the statements between the exception line and the label will not be executed.
This method is more suitable for exiting the program gracefully if any fatal error occurs during the execution.
Example:
Below is a self-explanatory example of ‘On Error Goto<label>:’ where I have used the label name as ‘Error_handler’.
Sub GetErr()
On Error GoToError_handler:
N = 1 / 0 ' cause an error
MsgBox "This line will not be executed"
Exit Sub
Error_handler:
MsgBox "exception handler"
End Sub
In this code as soon as the exception occurs at Line 3, the program transfers the control to Line 6.
Notice that here I have used ‘Exit Sub’ just before the ‘Error_handler:’ label, this is done to ensure that the Error handler block of code doesn’t execute if there is no error. If you omit the ‘Exit Sub’ statement then the Error handler code block will always execute even if no exception is encountered.
So, this was all about the On Error statement in Excel VBA.