In programming with VBA, you quickly learn that errors are a part of life. It’s not about luck; it’s about being prepared. When writing code, you can encounter various forms of errors, from syntax issues to logic mistakes, or even the pesky runtime errors that occur when you ask Excel to do the impossible. Take, for instance, a simple task like computing the square root of a number in a selected Excel cell.

I’ve encountered my fair share of errors while programming. Often, I find errors popping up when I least expect them. Let’s say I have a piece of code that’s supposed to calculate the square root of whatever value is currently in the selected cell in Excel. Looks straightforward, right? But if that cell doesn’t contain a numerical value, I’m met with an immediate and jarring run-time error. It’s a classic example of needing to anticipate and handle errors effectively.

Error Settings in VBA (Error Trapping)

When I’m getting down to business with VBA, tweaking error settings is a crucial step. In the VBA environment, under the menu Tools ➤ Options ➤ General ➤ Error Trapping, I come across three settings to handle unexpected situations.

Error Settings in VBA

  • Break on All Errors: This one is like keeping a strict watch. It doesn’t matter if I’ve written error handling code, this setting ensures that VBA halts on every single error, which can be a tad bit intrusive when I’m debugging.



  • Break in Class Module: It’s the middle ground. If my code is in a class module, say within a Userform, it halts right at the trouble spot. It’s great for pinpointing errors in those complex structures.



  • Break on Unhandled Errors: It’s the default, and quite the lifesaver. If I forget handling an error somewhere, it lets me know by halting the code, though it won’t point it out in objects like Userforms—more of a heads-up than a direct callout.


Selecting the right option shapes my debugging experience and helps me create sturdier, error-resistant applications.

Types of VBA Errors

1. Syntax Errors

Syntax errors, or language errors, happen when I write VBA code that doesn’t follow the required syntax. These are like the typos that might slip into my code. Luckily, VBA points these out with an error message—if “Auto Syntax Check” is turned on. I go to Tool ➤ Options and tick “Auto Syntax Check” to activate this feature, which then notifies me immediately if I make such an error. Otherwise, VBA simply highlights the problematic line without an error message.

Syntax Error Example

2. Compile Errors

Compile errors pop up when I attempt an invalid action in my code, like forgetting to close an IF statement with an END IF. When I run the code, VBA notifies me with a compilation error message.

For example:

  • Omitting Next after a For loop.
  • Missing End Select after a Select Case.
  • Forgetting to declare a variable with “Option Explicit” on.
  • Calling a non-existent Sub or Function.
Compile Error Example

3. Runtime Errors

These occur during the execution of the code, and they’ll bring things to a halt, showing me an error dialog box. Such a situation could arise if I reference a workbook in my code that’s been moved or deleted.

Runtime Error Example

An error description in the dialog box lets me know what went wrong. And if I choose to “Debug” instead of ending the process, VBA will highlight the line with an issue.

Runtime Error Detailed Example

4. Logical Error

A logical error is tricky because it’s not a blatant error, but more of a mistake in logic or process. Let’s say I chose an incorrect data type for a variable or botched my code’s logic. My code might still run, but the result won’t be what I expected. Rooting out logical errors can be tough; my strategy is to step through the code line by line to suss them out. This approach often unveils the misstep lurking quietly in my logic.

Using Debug Tools in VBA

1. Compile VBA Project

When I’ve finished writing code, I always make sure to check for syntax or compile errors. It’s simple; I go to the Visual Basic Editor, navigate to Debug on the toolbar, and then select Compile VBA Project. If there’s something wrong with my code, a message box pops up pointing out the issue, which is super helpful before running the entire script.

It’s important to note that the Compile option won’t catch runtime errors since they only occur when the code is actually running. But once the code is error-free, the Compile option will become unavailable as it’s greyed out.

Compile VBA Project

2. Run Each Line of Code One by One

Running code line by line is what I do next. I take my time with this; step through each line using the “Step In” button on the “Debug Toolbar” or by hitting F8. This method helps me spot not just syntax or compile issues but run-time errors as well. By doing so, I catch errors as they happen, letting me fix issues in the moment rather than after the fact.

Run Each Line of Code

Using the “On ERROR” Statement to Handle VBA Errors

1. On Error Resume Next

When I stumble upon errors during my VBA code execution, I employ the “On Error Resume Next” statement. It’s like telling VBA, “Hey, don’t stop, keep it moving.” If an error pops up, this line instructs VBA to ignore it and proceed to the next line. Here’s how it looks in action:

Sub myDivide()
    On Error Resume Next
    Range("A1").Value = 25 / 0 'This will cause a divide by zero error
    Range("A2").Value = 10 / 5  'This will work just fine
End Sub

Key Point: It must precede potential errors to work; otherwise, it won’t skip anything.

2. On Error GoTo 0

Sometimes, I like VBA to revert to its default ‘error freak out mode’, where it halts code and presents the error. By adding “On Error GoTo 0”, it’s back to the VBA standard: encounter an error, show it to me and stop. It’s particularly useful if I only want to ignore errors temporarily.

3. On Error GoTo [Label]

Creating an emergency exit in my code, that’s what “On Error GoTo [Label]” is like. It jumps to a designated spot straight away if an error occurs. Here’s a peek at how I set it up:

Sub MyProcedure()
    On Error GoTo ErrorHandler
    ' Code that might cause an error
    '...
Exit Sub

ErrorHandler:
    MsgBox "Oops! Something went wrong."
End Sub

I ensure there’s an “Exit Sub” before the ErrorHandler to prevent running the error code when there’s no need. Just add the actual label, replace “[Label]” with something like “ErrorHandler”, and VBA knows exactly where to jump during code hiccups.

4. On Error GoTo -1

Here’s an insider tip: VBA doesn’t forget errors until the procedure is done. But what if I need to reset the error state within the same procedure? “On Error GoTo -1” comes to the rescue. It cleans up VBA’s memory, and I can handle a new error afresh. It’s handy when I have more than one error to handle:

Sub AdvancedErrorHandler()
    On Error GoTo FirstError
    ' First block of code that might fail
    '...
    Exit Sub
    
FirstError:
    MsgBox "First hiccup dealt with."
    On Error GoTo -1 ' Clear the error.
    On Error GoTo SecondError ' Ready for the next issue.
    ' Second block of code that might fail
    '...
    Exit Sub
    
SecondError:
    MsgBox "Second hiccup dealt with."
End Sub

By the way, this “On Error GoTo -1” move, it’s like telling VBA, “Okay, let’s forget about that last stumble and get ready for a new one, if it comes.”

What Else Do I Need to Know to Handle Errors in VBA?

Err Object

In the trenches of VBA coding, I’ve found that the Err object is like my trusty sidekick, always there to tell me what’s up when things go haywire. Here’s a quick rundown on how I use this helpful guy to track down the culprits of code crashes:


  • Err.Number: This tells me the ID of the error. It’s almost like having a “Who am I?” tag for each error. So when something funky happens, I can match this number to a list of error IDs and voilà – I start getting clues.



  • Err.Description: A description? Yes, please! This is where Err supplements the number with some context. It’s like having someone whisper in my ear, “Psst, this is why everything just fell apart.”



  • Err.Source: Now this is where things get really interesting. Err.Source points to the application or object that caused the error. It’s kind of like having a homing beacon that leads me to where the chaos started.



  • Err.HelpContext & Err.HelpFile: These two are like having a mini guidebook at my fingertips. When I’m in a bind, they steer me to more information so I can figure out what happened and why.


Here’s an example of how I might use these properties:

Sub CalculateSquareRoot()
    On Error GoTo ErrorHandler
    Range("A1").Value = Sqr(Range("A1").Value)
    Exit Sub
ErrorHandler:
    MsgBox "Oops! Something went wrong in cell A1." & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description
End Sub

If this baby throws an error, I get a message box that doesn’t just scream “Error!”, but gives me the specifics so I can sort out the problem.

When it comes to methods, the Err object hands me a couple of nifty tools:


  • Err.Clear: I use Err.Clear to tidy up after handling an error. It wipes Err’s memory cleaner than a chalkboard after class, but doesn’t reset the error handling like “On Error GoTo -1” would.



  • Err.Raise: Sometimes, I need to trigger errors on purpose (for testing, I promise). That’s where Err.Raise comes in handy. By using this method, I can simulate real-world problems without waiting for them to naturally occur. To raise an error, I go with the syntax Err.Raise [number], [source], [description], [helpfile], [helpcontext].


Playing around with the Err object helps me keep my code robust and gives me a head’s up on any potential goofs. No more getting blindsided by errors!

Quick Tips on Error Handling

In the thick of coding, I like to keep my VBA scripts smooth and user-friendly. Here’s how I handle those pesky run-time errors:


  • Exit Gracefully: I always throw in an Exit Sub or Exit Function before my error-handling label. It’s like telling my code to skip the drama if everything’s cool.


    Sub MyProcedure()
        ' ... [code] ...
        Exit Sub
    ErrorHandler:
        ' ... [handle error] ...
    End Sub


  • Custom Error Messages: When things go sideways, I prefer to inform users with a message box instead of VBA’s default error scream. This way, they get the info in a calm and collected manner.



  • Prevent Program Termination: I use On Error GoTo [Label] to direct my code to an error-handling routine. This way, if my code trips, it doesn’t faceplant and crash the whole program.



  • Selective Ignorance: Sometimes I expect an error, and it’s all cool. That’s when I use On Error Resume Next to skip over a line. But hey, I use this sparingly – only when I’m certain it’s safe.



  • Log it: Best practice, I keep a log. When an error pops up, I jot it down with details. It helps with both debugging and creating a more stable application down the road.


My methods ensure reliability and a better experience for the end user. Remember, handling errors is more art than science, but it’s all about keeping the show running without losing your cool.

Related Tutorials

Similar Posts