Introduction | Example | Tutorial | Applications

Introduction - VBA Err.Raise

The VBA Err.Raise method can be used to raise custom errors and start the visual basic debugger. This is a neat way to handle errors instead of displaying instructional msgboxes.

Some people have a bunch of the default run-time error numbers memorized. I’m not one of those people - nor do I care to be - but if you are, you can pair Err.Raise with the vbObjectError constant to avoid conflicts with reserved system error numbers. I’ll explain why that’s important later in this tutorial.

Example - VBA Err.Raise

Raise an error when sheet doesn't exist

Sub RaiseCustomError()
Dim bSheetFound As Boolean
For Each Sheet In ActiveWorkbook.Worksheets
    If Sheet.Name = "INPUT" Then
        bSheetFound = True
        Exit For
    End If
Next Sheet

If bSheetFound = False Then
    Err.Raise Number:=vbObjectError + 513, _
              Description:="Unable to find sheet INPUT"
End If
alpha = Sheets("INPUT").Range("A1")
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

I'll take a free VBA Developer Kit

Tutorial - VBA Err.Raise

Custom Error Demonstration

The example macro above loops through all the sheets in the workbook and raises an error when a worksheet named INPUT isn’t found. Instead of telling the user the sheet doesn’t exist with a MsgBox, it brings up the following error dialog box:

VBA Err.Raise

So what would happen if we didn’t raise this custom error message? Copy and paste the following macro into a workbook without a sheet named INPUT.

Sub RaiseSystemError()
alpha = Sheets("INPUT").Range("A1")
End Sub

Run the macro and you’ll get a system error message like this:

VBA System Error Message

That message isn’t nearly as helpful as the first message, is it? It tells you an error occurred, but it’s not really clear without context why the error occurred.

To help you build your own custom error messages, let’s walk through some of the Err.Raise arguments.

VBA Err.Raise Arguments

The Err.Raise method accepts 5 arguments, the last 4 of which are optional:

Err.Raise(Number as Long, [Source], [Description], [HelpFile], [HelpContext])
   
Number Required number that you want to appear when your error message pops up.
Source [Optional] String stating where the error occurred in a format like Project.Class (ex: VBAProject.Class1). I don’t find this argument particularly useful for most projects so I just accept the default, which is the project name (ex: VBAProject).
Description [Optional] The string that actually describes the error encountered. This is the text that will appear on the screen when your error dialog box appears.
HelpFile [Optional] The path of a help file to help the user diagnose and debug your custom errors. You’ll rarely go through the time (and money) to make one of these so don’t worry about this argument.
HelpContext   [Optional] The topic in your HelpFile that provides help for your particular error number. Again, you’ll rarely go through the time to make one of these so don’t worry about this argument, either.


Regarding the Number argument, the help manual says the range 0 to 512 is reserved for system errors and valid numbers above 512 can be used for user-defined errors. To avoid conflicts, it recommends adding your number to the constant vbObjectError, like I did in the example above. Keep reading to find out why this important.

VBA Err.Raise Conflicts

The point I just made about using the vbObjectError in your Number argument to avoid conflicts with pre-defined system errors may not seem that important, but it can save some serious confusion down the road. Let me illustrate what I mean with an example.

Let’s say we rerun my example macro but instead of setting the Number variable to vbObjectError + 513, we change it to this:

Err.Raise Number:=11, _
              Description:="Unable to find sheet INPUT"

You’ll get an error that looks like this:

VBA Err.Raise Conflict

Seems harmless, right? Well, suppose someone ten years down the road saw this error and they Googled “Run-time error 11.” They would see that error 11 supposed to be a division by zero error and they’d be all confused! Well, maybe they wouldn’t be confused in this example, but if you forgot to put the optional description and you just arbitrarily raised error 11, they’d most certainly be confused.

Err.Raise Number:=11 'Whoops! I forgot my description...

Run-time Error 11 VBA

Application Ideas

I don’t raise custom errors often, but for small projects where my source code isn’t locked down for the end user, I can see some benefits to handling errors using the Err.Raise method instead of tediously trapping them and trying to get the user to solve them on the fly.

On Error GoTo statements can be confusing and users may not be able to trace where the errors actually occurred. Raising a custom error on the spot might just be another tool in your macro toolbox to help you and your end-users out. I’d love to hear what you think!

If you found this tutorial helpful, please share this article with your friends on Facebook and Twitter and subscribe using the form below. Sharing articles with others is how wellsr.com will continue to grow, so I genuinely appreciate it.