Return to VBA Code Examples
This article will demonstrate how to use VBA to Try Catch Errors.
In most programming languages, you can use the syntax Try…Catch…End Try to prevent errors from occurring. The code literally tries to run and if it encounters and error, it jumps down to the catch line where the error can be documented and retuned to the user. VBA does not use the Try…Catch…End Try syntax but we can mimic this syntax by using the On Error GoTo syntax instead.
On Error GoTo
The On Error GoTo statement in VBA will force our code to move to a specific line of code if an error occurs.
Sub RenameSheet ()
On Error GoTo eh
ActiveSheet.Name = "Sheet1"
Exit Sub
eh:
MsgBox Err.Description
End SubIn the above example, we have put added an error trap where we direct the code to move to the error handler if an error occurs. We then try to rename the sheet to be Sheet1. If we are in Sheet2 for example, and try to rename it to Sheet1 BUT Sheet1 already exists, then the error will occur and the code will jump down to the error handler.
However, if there is no sheet 1 in the workbook, then the code will run perfectly and as there is an Exit Sub BEFORE the error handler, the error handler code will not be reached.
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!
- Use
On Error Resume Nextto Handle Errors in VBA - Use
On Error GoTo 0to Handle Errors in VBA - Use
On Error GoTo [Label]to Handle Errors in VBA
The Try-Catch method prevents program crashes when an internal error occurs in computer programming. It is useful to prevent system errors and let the smooth flow of code execution occur.
However, unlike other programming languages, VBA does not have the Try-Catch block. On the good side, it has workaround methods designed explicitly for error handling procedures.
In this article, error handling procedures in VBA will be discussed and demonstrated together with several samples that would help you implement error handling schemes in your VBA project.
Error handlers are an important part of your code. They are our go-to guy when the VBA compiler cannot handle things.
Listed below are the VBA error handling methods:
On Error Resume Next— will ignore any encountered error, and the code will continue to run.On Error GoTo 0— will stop the code on the line that causes the error and show a message box describing the error.On Error GoTo [Label]— you can specify what you want to do if your code has an error.
We will discuss all of these error-handling routines in detail below.
Use On Error Resume Next to Handle Errors in VBA
This error handler allows the compiler to ignore the error and execute the next code line. It is the frequently used error handler in VBA as it does not need any complicated code to implement.
However, when using this error handler, proceed with precaution since it ignores errors. The code execution might not execute as planned with these undetected errors.
On Error Resume Next is best to know what kind of errors you are likely to encounter. And then, if you think it is safe to ignore these errors, you can use this.
The code block below will output an error without error-handling routines because division by zero is not allowed mathematically.
Sub DivideNumbers()
Dim x,y,z as integer
x = 10/4
y = 15/0
z= 2/5
Debug.Print x & vbNewLine & y & vbNewLine & z
End Sub
DivideNumbers Output:
Error: ! Runtime Error '11':
Division by Zero
Using the On Error Resume Next error handler:
Sub DivideNumbers()
On Error Resume Next
Dim x, y, z As Integer
x = 10 / 4
y = 15 / 0
z = 2 / 5
Debug.Print x & vbNewLine & y & vbNewLine & z
End Sub
DivideNumbers Output:
The DivideNumbers output shows that the second line was skipped since a Division by Zero error occurred on that line.
Use On Error GoTo 0 to Handle Errors in VBA
The On Error GoTo 0 error handler resets the default error behavior of the compiler.
So why still use it? On Error GoTo 0 error handler addresses the risk of non-detection of errors induced by On Error Resume Next error handler. Thus, On Error GoTo 0 is typically used in conjunction with On Error Resume Next to enable error detection on the code section where no error should occur.
To have a better explanation, see the code block below:
Sub ErrorSub()
On Error Resume Next
'An error could happen in this area but will not be detected.
On Error Goto 0
'Once an Error occurs, an error message will appear.
End Sub
For a better example, see below code block.
Sub DivideNumbers()
On Error Resume Next
Dim x, y, z, a, b, c
x = 3 / 2
y = 0 / 0
z = 2 / 6
Debug.Print x & vbNewLine & y & vbNewLine & z
On Error GoTo 0
a = 8 / 7
b = 2 / 0
c = 2 / 7
Debug.Print a & vbNewLine & b & vbNewLine & c
End Sub
DivideNumbers Output:
Then an error occurred since the On Error Resume Next was negated by the On Error GoTo 0 on the second part of the code.
Error: ! Runtime Error '11':
Division by Zero
Use On Error GoTo [Label] to Handle Errors in VBA
The two error handlers above don’t handle errors; either they skipped or neglected the error. The third error handler, On Error GoTo [Label], will allow you to deal with the error and let you choose the action to be taken when an error occurs.
There are several best practices in using On Error GoTo [Label], one is to terminate the code execution when an error occurs, and the other is to correct the error, then retry, and many more. The example below will demonstrate the usage of On Error GoTo [Label].
The code block below will automatically terminate the code when an error occurs.
Sub DivideNumbertoNumber()
On Error GoTo ErrHandler
Dim i As Integer
For i = 5 To -5 Step -1
Debug.Print i / i
Next i
ErrHandler:
End Sub
DivideNumbertoNumber Output:
As observed, code execution stopped when i = 0 since i divided by i would mean 0 divided by 0, which would result in an overflow.
In the next example, we will demonstrate how to print in the Immediate Window if an error occurs. We can also use the Err.Description property to print the actual error.
Sub DivideNumbertoNumber()
On Error GoTo ErrHandler
Dim i As Integer
For i = 5 To -5 Step -1
Debug.Print i / i
Next i
Exit Sub
ErrHandler:
Debug.Print "An Error occured at i is equal to " & i & vbNewLine & "Error Type: " & Err.Description
End Sub
DivideNumbertoNumber Output:
1
1
1
1
1
An Error occured at i is equal to 0
Error Type: Overflow
Note that Exit Sub was placed before the ErrHandler to exit the subroutine before it reached the ErrHandler for the cases that error did not occur. If the Exit Sub were not inserted, the compiler would execute the ErrHandler even if there is no error.
Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
The code below is pretty self explanatory: just copy and paste it all into a module and run it, it provides a few use cases and many explanatory comments in the text. (It works but I’m interested to know what other people make of it and for any suggestions you might like to make.)
The most important facts to realise are:
-
When you use on error goto Label1 the procedure enters into a state of «I’m handling an error» as an exception has been raised. When it is in this state, if another «On Error Goto» label2 statement is executed it will NOT goto label2, but raises and error which is passed to the code that called the procedure.
-
You can stop a procedure being in the «I’m handling an error» state by clearing the exception (setting err to nothing so the err.number property becomes 0) by using
Err.clear or On Error Goto -1 ' Which I think is less clear!
(NOTE that On Error Goto 0 is different from the above)
Also important to note is that Err.Clear resets it to zero but it is actually equivalent to:
On Error Goto -1
On Error Goto 0
ie Err.Clear removes an «On Error Goto» that is currently in place. So therefore it is mostly best to use:
On Error Goto -1
as using Err.clear You would often need to write
Err.Clear
On Error Goto MyErrorHandlerLabel
I use the above techniques with various labels to simulate the sometimes useful functionality that Visual basic TRY CATCH blocks give, which I think have their place in writing readable code.
Admittedly this technique creates a few more lines of code than a nice VB try catch statement, but it’s not too messy and pretty easy to get your head around.
PS. Also of interest might be the procedure ManageErrSource which makes the Err.Source property store the procedure where the error occurred.
Option Compare Database
Option Explicit
Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine As Integer
Private Sub RememberThenClearTheErrorObject()
On Error Resume Next
' For demo purposes
Debug.Print "ERROR RAISED"
Debug.Print Err.Number
Debug.Print Err.Description
Debug.Print Err.Source
Debug.Print " "
' This function has to be declared in the same scope as the variables it refers to
RememberErrNumber = Err.Number
RememberErrDescription = Err.Description
RememberErrSource = Err.Source
RememberErrLine = Erl()
' Note that the next line will reset the error object to 0, the variables above are used to remember the values
' so that the same error can be re-raised
Err.Clear
' Err.Clear is used to clear the raised exception and set the err object to nothing (ie err.number to 0)
' If Err.Clear has not be used, then the next "On Error GoTo ALabel" that is used in this or the procedure that called it
' will actually NOT pass execution to the ALabel: label BUT the error is paseed to the procedure that called this procedure.
' Using Err.Clear (or "On Error GoTo -1 ") gets around this and facilitates the whole TRY CATCH block scenario I am using there.
' For demo purposes
Debug.Print "ERROR RAISED is now 0 "
Debug.Print Err.Number
Debug.Print Err.Description
Debug.Print Err.Source
Debug.Print " "
' For demo purposes
Debug.Print "REMEMBERED AS"
Debug.Print RememberErrNumber
Debug.Print RememberErrDescription
Debug.Print RememberErrSource
Debug.Print " "
End Sub
Private Sub ClearRememberedErrorObjectValues()
' This function has to be declared in the same scope as the variables it refers to
RememberErrNumber = 0
RememberErrDescription = ""
RememberErrSource = ""
RememberErrLine = 0
End Sub
Sub ExampleOfTryCatchBlockInVBA()
On Error GoTo HandleError
' -----------------------------------------------------
' SubProcedure1 has the example of a multiple line TRY block with a block of code executed in the event of an error
SubProcedure1
Exit Sub
HandleError:
Select Case Err.Number
Case 0
' This shold never happen as this code is an error handler!
' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
Case 111111
' You might want to do special error handling for some predicted error numbers
' perhaps resulting in a exit sub with no error or
' perhaps using the Err.raise below
Case Else
' Just the Err.raise below is used for all other errors
End Select
'
' I include the procedure ManageErrSource as an exmple of how Err.Source can be used to maintain a call stack of procedure names
' and store the name of the procedure that FIRST raised the error.
'
Err.Raise Err.Number _
, ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
, Err.Number & "-" & Err.Description
' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
' (When a break is active, by moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
Resume
End Sub
Sub SubProcedure1()
' -----------------------------------------------------
' Example of a multiple line TRY block with a Case statement used to CATCH the error
'
' It is sometimes better to NOT use this technique but to put the code in it's own procedure
' (ie I refer to the code below that is surrounded by the tag #OWNSUB) .
' However,sometimes using this technique makes code more readable or simpler!
'
Dim i As Integer
' This line puts in place the defualt error handler found at the very foot of the procedure
On Error GoTo HandleError
'
' Perhaps lots of statements and code here
'
' First an example with comments
' -----------------------------------------------------
' TRY BLOCK START
' This next line causes execution to "jump" to the "catch" block in the event an error is detected.
On Error GoTo CatchBlock1_Start
' #OWNSUB
tsub_WillNotRaiseError_JustPrintsOk
If vbYes = MsgBox("1. Do you want to raise an error in the try block? - (PRESS CTRL+BREAK now then choose YES, try no later.)", vbYesNo) Then
i = 100 / 0
End If
'
' Perhaps lots of statements and code here
'
' #OWNSUB
' TRY BLOCK END
' -----------------------------------------------------
' -----------------------------------------------------
' CATCH BLOCK START
CatchBlock1_Start:
If Err.Number = 0 Then
On Error GoTo HandleError
' Re-instates the procedure's generic error handler
' This is also done later, but I think putting it here reduces the likelyhood of a coder accidentally removing it.
Else
' WARNING: BE VERY CAREFUL with any code that is written here as
' the "On Error GoTo CatchBlock1_Start" is still in effect and therefore any errors that get raised could goto this label
' and cause and infinite loop.
' NOTE that a replacement "On Error Goto" cannot be executed until Err.clear is used, otherwise the "On Error Goto"
' will itself raise and error.
' THEREFORE KEEP THE CODE HERE VERY SIMPLE!
' RememberThenClearTheErrorObject should be the only code executed and this called procedure must be tight!
' This saves the details of the error in variables so that the "On Error GoTo HandleError" can be used
' to determine how the next Err.Raise used below is handled (and also how any unexpected implicitly raised errors are handled)
RememberThenClearTheErrorObject
On Error GoTo HandleError '#THISLINE#
If vbYes = MsgBox("2. Do you want to raise an error in the erro handler? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
i = 100 / 0
End If
Select Case RememberErrNumber
Case 0: ' No Error, do Nothing
Case 2517
Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
Case Else
' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
' NOTE this is giving an example of what woudl happen if the CatchBlock1_ErrorElse is not used below
If vbYes = MsgBox("3. Do you want to raise an error in the ELSE error handler? CatchBlock1_ErrorElse *HAS NOT* been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
i = 100 / 0
End If
On Error GoTo CatchBlock1_ErrorElse
' SOME COMPLEX ERROR HANDLING CODE - typically error logging, email, text file, messages etc..
' Because the error objects values have been stored in variables, you can use
' code here that might itself raise an error and CHANGE the values of the error object.
' You might want to surround the code with the commented out CatchBlock1_ErrorElse lines
' to ignore these errors and raise the remembered error. (or if calling a error handling module
' just use on error resume next).
' Without the CatchBlock1_ErrorElse lines any error raised in this "complex code" will be handled by the
' active error handler which was set by the "On Error GoTo HandleError" tagged as '#THISLINE#" above.
If vbYes = MsgBox("4. Do you want to raise an error in the ELSE error handler when CatchBlock1_ErrorElse HAS been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
i = 100 / 0
End If
CatchBlock1_ErrorElse:
On Error GoTo HandleError
' This line must be preceeded by an new "On error goto" for obvious reasons
Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
End Select
On Error GoTo HandleError
End If
' CATCH BLOCK END
' -----------------------------------------------------
On Error GoTo HandleError ' Unnecessary but used to delimt the catch block
'
' lots of code here perhaps
'
' -----------------------------------------------------
' Example 2
'
' In this example goto statements are used instead of the IF statement used in example 1
' and no explanitory comments are given (so you can see how simple it can look)
'
' -----------------------------------------------------
' TRY BLOCK START
On Error GoTo CatchBlock2_Start
tsub_WillNotRaiseError_JustPrintsOk
If vbYes = MsgBox("Do you want to raise an error? - (PRESS CTRL+BREAK now then choose YES)", vbYesNo) Then
i = 100 / 0
End If
'
' Perhaps lots of statements and code here
'
' TRY BLOCK END
' -----------------------------------------------------
GoTo CatchBlock2_End:
CatchBlock2_Start:
RememberThenClearTheErrorObject
On Error GoTo HandleError
Select Case RememberErrNumber
Case 0: ' No Error, do Nothing
Case 2517
Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
Case Else
' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
' In this case the unexpecetd erro will be handled by teh code that called this procedure
' This line must be preceeded by an new "On error goto" for obvious reasons
Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
End Select
On Error GoTo HandleError
End If
CatchBlock2_End:
' CATCH BLOCK END
' -----------------------------------------------------
On Error GoTo HandleError ' Unnecessary but used to delimt the catch block
'
' Here you could add lots of lines of vba statements that use the generic error handling that is after the HandleError: label
'
'
'
' You could of course, alway add more TRY CATCH blocks like the above
'
'
Exit Sub
HandleError:
Select Case Err.Number
Case 0
' This shold never happen as this code isan error handler!
' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
Case 111111
' You might watch to do special error handling for some predicted error numbers
' perhaps exit sub
' Perhaps using the Err.raise below
End Select
' ie Otherwise
'
' Note that I use the Err.Source to maintain a call stack of procedure names
'
Err.Raise Err.Number _
, ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
, Err.Number & "-" & Err.Description
' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
' (By moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
Resume
End Sub
Sub tsub_WillNotRaiseError_JustPrintsOk()
Static i As Integer
i = i + 1
Debug.Print "OK " & i
End Sub
Public Function ManageErrSource(MyClassName As String, ErrSource As String, ErrLine As Integer, ProcedureName As String) As String
' This function would normally be in a global error handling module
' On Error GoTo err_ManageErrSource
Const cnstblnRecordCallStack As Boolean = True
Select Case ErrSource
Case Application.VBE.ActiveVBProject.Name
' Err.Source is set to this value when a VB statement raises and error. eg In Access by defualt it is set to "Database"
ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
Case ""
' When writing code ouside of the error handling code, the coder can raise an error explicitly, often using a user error number.
' ie by using err.raise MyUserErrorNumber, "", "My Error descirption".
' The error raised by the coder will be handled by an error handler (typically at the foot of a procedure where it was raised), and
' it is this handler that calls the ManageErrSource function changing the Err.Source from "" to a meaningful value.
ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
Case Else
' This code is executed when ManageErrSource has already been called. The Err.Source will already have been set to hold the
' Details of where the error occurred.
' This option can be used to show the call stack, ie the names of the procdures that resulted in the prcedure with the error being called.
If cnstblnRecordCallStack Then
If InStr(1, ErrSource, ";") = 0 Then
ManageErrSource = ErrSource & ":: Called By: "
End If
ManageErrSource = ErrSource & ";" & ProcedureName & ":" & ErrLine
Else
ManageErrSource = ErrSource
End If
End Select
Exit Function
err_ManageErrSource:
Err.Raise Err.Number, "MyModuleName.err_ManageErrSource", Err.Description
Resume
End Function
Embark on a quest to master efficient programming with the mighty VBA Try-Catch mechanism! Brace yourself for an adventure where resilience meets elegance, and errors become mere stepping stones to success.
In this captivating exploration, we dive deep into the world of VBA Try-Catch, unraveling its secrets and unleashing its transformative power. Discover how this exceptional error-handling technique empowers you to anticipate and gracefully handle errors, ensuring your code remains robust and error-free.
Gone are the days of frantically searching for the source of bugs. With the Try-Catch mechanism as your shield, you’ll conquer the unpredictable and turn errors into opportunities for improvement. Watch in awe as your code gracefully recovers from unexpected hiccups, safeguarding your data and preserving your sanity.
But that’s not all—prepare to be amazed by a plethora of advanced techniques. Learn how to customize error messages, handle specific types of errors, and gracefully exit procedures when necessary. Harness the power of Try-Catch to create bulletproof code that stands tall even in the face of adversity.
Whether you’re a seasoned programmer or just venturing into the world of VBA, Try-Catch empowers you to elevate your programming skills to new heights. Embrace the elegance of error-handling and unlock a world of efficient programming.
So, are you ready to harness the full potential of VBA Try-Catch? Join us on this thrilling journey of mastering error-handling in VBA. Embrace resilience, turn errors into opportunities, and witness your code rise above challenges with grace. Get ready to revolutionize your programming approach and become a true master of efficiency!
Important disclosure: we’re proud affiliates of some tools mentioned in this guide. If you click an affiliate link and subsequently make a purchase, we will earn a small commission at no additional cost to you (you pay nothing extra). For more information, read our affiliate disclosure.
The Importance Of Error Handling In VBA
Just like how a comedian on stage plans a joke, only for it to fall flat — sometimes, in the world of VBA programming, things don’t go exactly as we script them. This, my dear reader, is when we get introduced to the realm of errors. Just as comedians need to improvise to recover from a failed punchline, so do VBA programmers, and that’s where error handling comes into play.
Why Errors In VBA Are like Unexpected Guests
Errors in VBA are like those unexpected guests who pop in unannounced, right when you’re enjoying a quiet evening alone. They’re unwelcome, they interrupt your flow, and they turn everything topsy-turvy.
But just like how you wouldn’t kick these guests out (well, usually), you can’t just ignore errors in VBA. It’s your responsibility to handle these mischievous deviants. After all, no code is perfect and bugs are just part of the process, just as unexpected guests are part of life (unfortunately).
Types Of Errors: Apples, Oranges, And Pears
In the diverse ecosystem of VBA, there are three main types of errors, or ‘unexpected guests’, if you prefer:
| Error Type | Description |
|---|---|
| Syntax Errors | The bane of every programmer’s existence, syntax errors occur when VBA can’t understand what you’re trying to tell it, sort of like how you feel when someone speaks to you in a language you don’t understand. |
| Runtime Errors | These are the sneaky ones, only showing their face when the program is running, like those minor wardrobe malfunctions that you don’t notice until you’re already at the party. |
| Logical Errors | These errors are the most sinister of all, as your code will run perfectly fine, but the result won’t be what you expect, like when you accidentally put salt instead of sugar in your coffee. |
The Savior: Error Handling
Imagine having a personal assistant who’s always on standby to step in when these unexpected guests arrive. This assistant knows exactly how to handle each guest, ensuring they cause as little disturbance as possible. In the world of VBA, this assistant goes by the name of «Error Handling».
Error handling is our knight in shining armor that helps us keep the disruption to a minimum when errors inevitably occur. It allows us to specify what should happen when an error crops up, thereby preventing our program from crashing unceremoniously.
Remember: To Err Is VBA, To Handle Is Divine
A well-tailored suit without a tie is like a well-written VBA code without error handling — it’s incomplete. Error handling is not an optional accessory; it’s a necessity. It not only safeguards your code from unexpected errors but also makes debugging a whole lot easier. In fact, it helps you catch those pesky logical errors that are otherwise like finding a needle in a haystack.
Error handling can be your best friend or your worst nightmare; it depends on how well you use it. So, buckle up and let’s dive deeper into how to harness the power of VBA Try-Catch in the upcoming sections.
Remember, the idea is to prepare for the worst while hoping for the best.
Understanding VBA Try-Catch
You see, Try-Catch is kind of like a game of tug of war. On one side, you have the «Try» block pulling with all its might, trying to execute a piece of code. On the other side, you have the «Catch» block ready to jump in and take over if the «Try» block loses its footing (aka an error occurs).
Unfortunately, VBA doesn’t use the same terms, but fear not! VBA’s equivalent of Try-Catch comes in the guise of «On Error Goto». Here, «On Error» is the siren that starts wailing when an error occurs, and «Goto» is the lifeboat that whisks you away to safety (the error-handling part of your code).
But wait, there’s more!
The Fine Nuances Of On Error Goto
VBA, like that eccentric uncle we all have, does things its own way. Instead of the straightforward Try-Catch, it provides two distinct flavors of error handling: «On Error Goto 0» and «On Error Resume Next». It’s like choosing between coffee and tea — they both help you stay awake, but they do it in slightly different ways.
Let’s take a closer look:
| Error Handling Type | Description |
|---|---|
| On Error Goto 0 | When VBA sees this, it behaves like a strict schoolteacher. If it spots any errors, it halts the execution right then and there, refusing to proceed further. |
| On Error Resume Next | This is when VBA turns into a laid-back surfer. If an error occurs, it just shrugs, says «whatever, dude», and moves on to the next line of code. |
VBA Try-Catch In Action: It’s Showtime!
Let’s bring this to life with a scenario. Imagine you’re running a bakery, and you have a VBA program that calculates the total price of an order. You input the quantity of each item and the price, and the code does the rest.
One fine day, a customer orders 10 cookies, but instead of entering ’10’ into the quantity field, you accidentally enter ‘Cookies’. Yep, you’ve just invited an error to the party.
Without Try-Catch (or «On Error Goto», as VBA prefers), your program would come crashing down, leaving you red-faced in front of the customer. Not an ideal situation, right?
But with Try-Catch, you could have a Catch block that handles the error gracefully, maybe by showing a polite error message suggesting that you’ve entered an invalid quantity. Crisis averted, and you look like a professional.
First things first, you need to set up your chess pieces. And in VBA, that means understanding the structure of the Try-Catch block. Here’s the skeleton:
On Error Goto ErrorHandler
'Your Code Here
Exit Sub
ErrorHandler:
'Do Something
End Sub
In the game of chess that is VBA programming, the ‘On Error Goto ErrorHandler’ line is your opening move. The moment VBA encounters an error in your code, it jumps to the ErrorHandler label. The ‘Exit Sub’ line, on the other hand, is like a magic portal that lets VBA escape the Subroutine if no error occurs, bypassing the ErrorHandler section.
The Offensive Move: On Error Resume Next
Now that we’ve got our pieces in place, let’s make our offensive move — using ‘On Error Resume Next’. This is like the bold knight move in chess, where you storm ahead, undeterred by the opponent’s pieces (or errors, in our case).
On Error Resume Next
'Your Code Here
If Err.Number <> 0 Then
'Handle Error
End If
In this scenario, if there’s an error, VBA doesn’t stop and throw a tantrum. Instead, it quietly records the error and proceeds to the next line of code, like a knight jumping over the enemy’s pieces.
The ‘If Err.Number <> 0 Then’ part is where we check if an error has occurred. If it has (i.e., Err.Number is not zero), we handle the error accordingly.
The Defensive Move: On Error Goto 0
Time for the defensive strategy — using ‘On Error Goto 0’. This is akin to placing your pawns strategically in chess, focusing on protection rather than offense.
On Error Goto 0
'Your Code Here
With ‘On Error Goto 0’, VBA adopts a no-nonsense attitude. If it encounters an error, it stops the code execution right there, not caring to proceed further. This strategy is particularly useful when you want to ensure that a certain section of your code is error-free.
Practical VBA Try-Catch: The King’s Gambit
Alright, enough with the chess analogies. Let’s see how we can apply Try-Catch in a real-life scenario.
Suppose you’re writing a VBA code to read data from a file. If the file doesn’t exist, VBA would usually throw an error and stop the execution. But with Try-Catch, you can handle this gracefully.
On Error Resume Next
Open "C:\SomeFolder\SomeFile.txt" For Input As #1
If Err.Number <> 0 Then
MsgBox "File not found. Please check the file path."
Exit Sub
End If
'Rest of Your Code
In this example, if the file doesn’t exist (and hence, an error occurs), VBA will display a message box saying «File not found. Please check the file path.», and then exit the Subroutine. If the file does exist, VBA will happily proceed with the rest of the code.
The Benefits Of Harnessing VBA Try-Catch
The first and foremost benefit of using Try-Catch in VBA is the ability to create robust code. With Try-Catch, your code becomes like a knight in shining armor, unflappable in the face of errors.
Picture this: you’ve written a VBA code that’s supposed to pull data from an external database. However, what if the database server is down? In normal circumstances, your code would encounter an error and stop execution. But with Try-Catch, you can handle such scenarios gracefully, maybe by displaying a user-friendly message or trying to connect to a backup server.
The Safety Net: Avoiding Program Crashes
Imagine you’re a tightrope walker, performing without a safety net. One misstep, and it’s game over. Similarly, a VBA program without error handling is like walking a tightrope without a safety net. One unhandled error, and the program crashes.
But introduce Try-Catch into the equation, and you’ve got yourself a safety net. Even if an error occurs, your program can recover and continue running, making it more reliable and professional.
The Informer: Detailed Error Information
Wouldn’t it be great if every time you encountered a problem, someone handed you a detailed report about what went wrong? That’s exactly what Try-Catch does in VBA. It provides you with detailed information about the error, including the error number and description, making it easier for you to diagnose and fix the issue.
For instance, suppose you’re trying to open a file that doesn’t exist. With Try-Catch, you can capture the exact error (like ‘File not found’) and act accordingly, maybe by prompting the user to check the file path.
VBA Try-Catch In The Professional World
Error handling is like a Swiss army knife in the world of programming. It’s a powerful tool that can cut through complex code and carve it into a beautiful, efficient, and user-friendly masterpiece. Now, just imagine the impression you’d make in your professional setting when your code behaves gracefully in the face of errors. Your colleagues might start to believe you’re some kind of programming wizard. 🧙♂️
Real-life Applications: An Array Of Possibilities
Let’s take a peek into some real-world scenarios where VBA Try-Catch is the star player.
Financial Analysts: They use VBA to automate complex financial models in Excel. Imagine running a massive financial model, only to have it crash due to a minor error. With Try-Catch, they can handle such errors effectively, ensuring that their financial models run smoothly and reliably.
Data Scientists: They often use VBA for automating data processing tasks. Try-Catch helps them deal with unexpected errors during data processing, like missing or inconsistent data.
Quality Controllers: They utilize VBA scripts for automated testing. Try-Catch allows them to capture errors during the test execution and log them for further analysis.
Teachers and Students: In academic settings, understanding Try-Catch is crucial for learning good programming practices and writing robust code. For students aspiring to become VBA experts, mastering Try-Catch is an absolute must.
From Amateur To Professional: The Try-Catch Way
Handling errors effectively can be the difference between an amateur and a professional programmer. When you start using Try-Catch in your VBA scripts, you’re not just fixing potential errors — you’re also making a strong statement about your professionalism and attention to detail. It’s like having a well-tailored suit in a world of jeans and T-shirts.
VBA Try-Catch In Action
Imagine you’re a business analyst working with an Excel workbook full of sales data. You’ve got a script that fetches a specific cell’s value. But alas, the cell is empty, and your code crashes. It’s like reaching into a bag of chips only to find it empty—disappointing and mildly infuriating. Let’s see how we can use Try-Catch to handle this situation.
On Error GoTo ErrorHandler
Dim sales As Variant
sales = Worksheets("Sheet1").Range("A1").Value
MsgBox sales
Exit Sub
ErrorHandler:
MsgBox "Oops! It seems like cell A1 is empty. Better fill it up!"
In this example, when Excel finds cell A1 empty, instead of crashing and creating a spectacle, it calmly displays a message box saying, «Oops! It seems like cell A1 is empty. Better fill it up!».
The Try-Catch Equivalent In VBA
If you’ve been around the programming block, you might be wondering, «Wait, where’s the ‘Try’ and ‘Catch’?» Well, in VBA, «On Error GoTo» is the ‘Try’, and the «ErrorHandler» is the ‘Catch’. It’s a bit like ordering a soda in a different country—you might not recognize the name, but the fizz is the same.
Try-Catch With Multiple Error Handlers
Life isn’t always black and white, and neither are errors in VBA. Sometimes you might have to handle different types of errors differently. Like dressing up for a fancy dinner party versus a beach outing—you wouldn’t want to mix those up. Let’s see how we can use multiple error handlers in such scenarios.
On Error GoTo ErrorHandler1
' Some code
On Error GoTo ErrorHandler2
' Some more code
On Error GoTo 0
Exit Sub
ErrorHandler1:
' Handle error type 1
Resume Next
ErrorHandler2:
' Handle error type 2
Resume Next
In this script, we handle different sections of the code with different error handlers. It’s like having a multi-tool instead of a simple knife.
Advanced Error Handling: A Step Further
When you’ve got some experience under your belt, you might want to try advanced error handling. It’s like going from driving a car to piloting a spaceship. One such method is using the Err object for more control and flexibility over error handling.
On Error Resume Next
' Some code
If Err.Number <> 0 Then
' Handle error
Err.Clear
End If
On Error GoTo 0
Here, the Err object allows us to handle the error based on its number. It’s like having a butler who tells you who’s at the door before you decide to open it.
By now, we hope that you’ve got a good grasp of Try-Catch in action. Remember, good error handling is like a well-made cup of coffee—it makes everything run smoothly. So, don’t just read about it, start implementing it in your VBA scripts and make your programming life a whole lot easier!
Is Try-Catch a built-in function in VBA?
Not exactly. VBA uses a slightly different syntax with «On Error GoTo» (acting as the Try) and error handlers (serving as the Catch).
How do I handle multiple types of errors in VBA?
You can handle multiple types of errors by using multiple error handlers. It’s like having different tools for different tasks. Each block of code can have its own error handler, letting you handle errors in diverse and flexible ways.
What does «On Error GoTo 0» do in VBA?
«On Error GoTo 0» is the equivalent of turning off error handling. It’s like telling your code, «Okay, enough with the special treatment, let’s go back to normal.» It’s a good practice to use this after your error handler to prevent unintended error suppression.