Introduction | Example | Tutorial | Applications | Comments
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
Becoming a VBA expert isn't hard
Over 5000 members are improving their VBA skills for free with our email tutorials. Why don't you join them? Our experts share time-saving VBA tips and we'll give you access to our huge macro library - it's sure to speed up your macro development.
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
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
Sub RaiseSystemError() alpha = Sheets("INPUT").Range("A1") End Sub
Run the macro and you’ll get a system error message like this:
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:
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...
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. Sharing articles with others is how wellsr.com will continue to grow, so I genuinely appreciate it.
I hope you’ll reach out to me via my VBA Consulting page if you’re working on a macro, but you’re too busy to get it done.
If you’re ready to free up your time in the office, visit my Excel Add-ins page for some powerful automation products.