Unexpected Error Occured. Please Try Again. If This Issue Continues
"Arrest, Retry, Fail?" – MS-DOS error message circa 1986
This post provides a consummate guide to VBA Error Handing. If y'all are looking for a quick summary then check out the quick guide table in the first section.
If yous are looking for a particular topic on VBA Error Handing then check out the table of contents below(if information technology's not visible click on the post header).
If yous are new to VBA Error Handling, and then you can read the post from start to end as it is laid out in logical order.
Contents
- 1 A Quick Guide to Error Handing
- 2 The Webinar
- 3 Download the Error Handling Library
- 4 Introduction
- 5 VBA Errors
- 5.1 Syntax Errors
- 5.2 Compilation Errors
- five.2.1 Using Debug->Compile
- 5.ii.2 Debug->Compile Error Summary
- v.2.3 Debug->Compile Usage
- 5.three Runtime Errors
- 5.3.i Expected Versus Unexpected Errors
- five.4 Runtime Errors that are not VBA Errors
- 6 The On Mistake Statement
- 6.i On Error GoTo 0
- 6.two On Error Resume Adjacent
- half-dozen.3 On Error GoTo [label]
- vi.4 On Error GoTo -1
- 6.5 Using On Mistake
- seven Resume Next
- viii The Err Object
- 8.i Getting the Line Number
- 8.2 Using Err.Raise
- viii.3 Using Err.Clear
- 9 Logging
- 10 Other Error Related Items
- ten.1 Error Function
- 10.ii Error Statement
- 11 A Simple Error Treatment Strategy
- xi.1 The Basic Implementation
- 12 A Complete Error Handling Strategy
- 12.1 An Example of using this strategy
- 13 Mistake Handling in a Nutshell
- fourteen What's Next?
A Quick Guide to Error Handing
| Item | Description |
|---|---|
| On Mistake Goto 0 | When error occurs, the code stops and displays the mistake. |
| On Error Goto -1 | Clears the current error setting and reverts to the default. |
| On Error Resume Next | Ignores the error and continues on. |
| On Error Goto [Label] | Goes to a specific label when an error occurs. This allows us to handle the error. |
| Err Object | When an fault occurs the error information is stored here. |
| Err.Number | The number of the error. (Merely useful if y'all need to check a specific error occurred.) |
| Err.Description | Contains the error text. |
| Err.Source | You can populate this when you use Err.Enhance. |
| Err.Raise | A office that allows you to generate your own error. |
| Error Part | Returns the fault text from an error number. Obsolete. |
| Fault Statement | Simulates an error. Use Err.Enhance instead. |
The Webinar
Members of the Webinar Archives tin can admission the webinar for this article by clicking on the image below.
(Notation: Archive members have access to the webinar archive.)
Download the Error Handling Library
Introduction
Error Handling refers to code that is written to handle errors which occur when your application is running. These errors are normally acquired by something outside your command like a missing file, database beingness unavailable, data being invalid etc.
If nosotros retrieve an fault is likely to occur at some point, it is good do to write specific code to handle the mistake if it occurs and deal with information technology.
For all other errors, nosotros use generic lawmaking to bargain with them. This is where the VBA mistake handling statement comes into play. They allow our application to deal gracefully with whatsoever errors nosotros weren't expecting.
To understand error handling we must first sympathise the different types of errors in VBA.
VBA Errors
There are three types of errors in VBA:
- Syntax
- Compilation
- Runtime
Nosotros use error treatment to bargain with runtime errors. Let's take a look at each of these fault types so that it is articulate what a runtime error is.
Syntax Errors
If you lot accept used VBA for whatever length of time y'all volition have seen a syntax error. When you type a line and press return, VBA will evaluate the syntax and if it is non correct it will display an fault message.
For case if you type If and forget the Then keyword, VBA will brandish the following fault message
Some examples of syntax errors are
' then is missing If a > b ' equals is missing after i For i ii To seven ' missing right parenthesis b = left("ABCD",1
Syntax errors relate to 1 line merely. They occur when the syntax of ane line is incorrect.
Note: You lot can turn off the Syntax error dialog by going to Tools->Options and checking off "Auto Syntax Check". The line will still appear crimson if there is an error but the dialog will not appear.
Compilation Errors
Compilation errors occur over more one line. The syntax is correct on a single line but is incorrect when all the project code is taken into account.
Examples of compilation errors are:
- If statement without respective Finish If statement
- For without Next
- Select without End Select
- Calling a Sub or Function that does not exist
- Calling a Sub or Function with the wrong parameters
- Giving a Sub or Part the same name as a module
- Variables non declared(Option Explicit must be present at the top of the module)
The following screenshot shows a compilation error that occurs when a For loop has no matching Next statement.
Using Debug->Compile
To notice compilation errors, nosotros utilise Debug->Compile VBA Projection from the Visual Bones menu.
When you select Debug->Compile, VBA displays the starting time error information technology comes across.
When this error is stock-still, you tin can run Compile once again and VBA will then find the next error.
Debug->Compile will also include syntax errors in it's search which is very useful.
If in that location are no errors left and you run Debug->Compile , it may appear that nothing happened. However, "Compile" will exist grayed out in the Debug menu. This means your awarding has no compilation errors at the current time.
Debug->Compile Error Summary
- Debug->Compile finds compilation(project wide) errors.
- It will also find syntax errors.
- Information technology finds one error each time you use it.
- When there are no compilation errors left the Compile choice will appear grayed out in the menu.
Debug->Compile Usage
Yous should always use Debug->Compile earlier y'all run your code. This ensures that your lawmaking has no compilation errors when you run information technology.
If you do non run Debug->Compile then VBA may find compile errors when it runs. These should non be confused with Runtime errors.
Runtime Errors
Runtime errors occur when your application is running. They are normally outside of your control but can exist acquired past errors in your code.
For example, imagine your application reads from an external workbook. If this file gets deleted then VBA will display an error when your code tries to open information technology.
Other examples of runtime errors are
- a database not being bachelor
- the user entering invalid data
- a cell containing text instead of a number
Every bit we take seen, the purpose of mistake handling is to deal with runtime errors when they occur.
Expected Versus Unexpected Errors
When nosotros remember a runtime mistake could occur nosotros put code in place to handle it. For instance, nosotros would normally put code in place to deal with a file not being found.
The post-obit code checks if the file exists before it tries to open up information technology. If the file does non exist and so a user friendly bulletin is displayed and the code exits the sub.
' https://excelmacromastery.com/ Sub OpenFile() Dim sFile As String sFile = "C:\docs\data.xlsx" ' Use Dir to check if file exists If Dir(sFile) = "" And so ' if file does not exist display bulletin MsgBox "Could not find the file " & sFile Exit Sub End If ' Code will only attain here if file exists Workbooks.Open sFile Finish Sub
When we think an error is probable to occur at some bespeak, it is adept practice to add code to handle the state of affairs. We normally refer to these errors as expected errors.
If we don't have specific code to handle an error information technology is considered an unexpected mistake. We use the VBA error handling statements to handle the unexpected errors.
Runtime Errors that are not VBA Errors
Before we await at the VBA Handling at that place is ane type of error we must mention. Some runtime errors are non considered errors past VBA but only by the user.
Let me explain this with an example. Imagine yous accept an awarding that requires yous to add the values in the variables a and b
result = a + b
Let'south say yous mistakenly use an asterisk instead of the plus sign
result = a * b
This is not a VBA error. Your code syntax is perfectly legal. However, from your requirements point of view it is an fault.
These errors cannot be dealt with using error treatment as they obviously won't generate any error. You tin can bargain with these errors using Unit Testing and Assertions. I take an in-depth postal service almost using VBA assertions – see How to Brand Your Code BulletProof.
The On Error Statement
As we have seen there are 2 ways to treat runtime errors
- Expected errors – write specific code to handle them.
- Unexpected errors – use VBA error treatment statements to handle them.
The VBA On Mistake argument is used for mistake handling. This statement performs some activity when an error occurs during runtime.
In that location are iv different ways to use this statement
- On Fault GoTo 0 – the code stops at the line with the error and displays a message.
- On Error Resume Next – the code moves to adjacent line. No error bulletin is displayed.
- On Error GoTo [label] – the code moves to a specific line or label. No mistake message is displayed. This is the ane we use for error handling.
- On Error GoTo -1 – clears the current error.
Allow'south await at each of these statements in turn.
On Fault GoTo 0
This is the default beliefs of VBA. In other words, if you don't use On Error then this is the behavior you will see.
When an fault occurs, VBA stops on the line with the error and displays the mistake message. The application requires user intervention with the code before information technology can continue. This could be fixing the error or restarting the application. In this scenario no error handling takes place.
Allow'south look at an example. In the following code, we have non used any On Error line so VBA volition use the On Error GoTo 0 behavior by default.
' https://excelmacromastery.com/ Sub UsingDefault() Dim x Every bit Long, y Equally Long 10 = 6 y = 6 / 0 x = 7 Stop Sub
The second assignment line results in a divide past zero mistake. When we run this lawmaking we volition get the error message shown in the screenshot below
When the fault appears y'all can choose End or Debug
If y'all select Stop then the awarding only stops.
If you select Debug the awarding stops on the error line equally the screenshot below shows
This behaviour is fine when you are writing VBA code every bit it shows you the verbal line with the error.
This beliefs is unsuitable for an application that you are given to a user. These errors look unprofessional and they make the application look unstable.
An error like this is essentially the application crashing. The user cannot continue on without restarting the awarding. They may non use it at all until you gear up the error for them.
By using On Error GoTo [label] nosotros can give the user a more controlled error message. It also prevents the application stopping. We tin get the awarding to perform in a predefined manner.
On Fault Resume Next
Using On Error Resume Next tells VBA to ignore the error and continue on.
There are specific occasions when this is useful. Nearly of the fourth dimension y'all should avoid using it.
If we add Resume Next to our example Sub then VBA will ignore the divide past zero mistake
' https://excelmacromastery.com/ Sub UsingResumeNext() On Fault Resume Next Dim x Every bit Long, y Every bit Long x = 6 y = six / 0 x = 7 Terminate Sub
It is not a expert thought to practice this. If yous ignore the error, then the behavior tin exist unpredictable. The mistake can touch the awarding in multiple means.You could end up with invalid data. The problem is that you aren't aware that something went incorrect because you take suppressed the error.
The code below is an example of where using Resume Adjacent is valid
' https://excelmacromastery.com/ Sub SendMail() On Mistake Resume Next ' Requires Reference: ' Microsoft Outlook fifteen.0 Object Library Dim Outlook As Outlook.Application Ready Outlook = New Outlook.Application If Outlook Is Nothing So MsgBox "Cannot create Microsoft Outlook session." _ & " The email will not be sent." Exit Sub End If Cease Sub
In this code we are checking to see if Microsoft Outlook is available on a computer. All we want to know is if it is bachelor or not. We are not interested in the specific error.
In the code to a higher place, we continue on if there is an mistake. Then in the next line we bank check the value of the Outlook variable. If in that location has been an mistake then the value of this variable volition be set to Naught.
This is an instance of when Resume could be useful. The point is that even though we use Resume nosotros are still checking for the mistake. The vast majority of the fourth dimension yous will not need to use Resume.
On Mistake GoTo [label]
This is how we use Fault Handling in VBA. Information technology is the equivalent of the Endeavour and Take hold of functionality yous see in languages such as C# and Java.
When an error occurs yous send the error to a specific label. Information technology is normally at the bottom of the sub.
Let's apply this to the sub we have been using
' https://excelmacromastery.com/ Sub UsingGotoLine() On Error GoTo eh Dim x Equally Long, y Equally Long x = six y = 6 / 0 x = 7 Done: Exit Sub eh: MsgBox "The following mistake occurred: " & Err.Description End Sub
The screenshot below shows what happens when an error occurs
VBA jumps to the eh label because we specified this in the On Error Goto line.
Note 1: The label we apply in the On…GoTo statement, must exist in the current Sub/Office. If not you will go a compilation error.
Notation 2: When an error occurs when using On Error GoTo [label], the error handling returns to the default behaviour i.e. The code will cease on the line with the error and display the error message. See the side by side section for more data about this.
On Fault GoTo -1
This statement is unlike than the other iii. Information technology is used to clear the electric current error rather than setting a detail behaviour.
When an error occurs using On Mistake GoTo [label], the error treatment behaviour returns to the default behaviour i.e. "On Error GoTo 0". That means that if some other error occurs the lawmaking will stop on the current line.
This behaviour only applies to the current sub. In one case we exit the sub, the error will be cleared automatically.
Take a look at the code beneath. The first error volition cause the lawmaking to bound to the eh label. The second error will cease on the line with the 1034 error.
' https://excelmacromastery.com/ Sub TwoErrors() On Mistake Goto eh ' generate "Type mismatch" error Error (xiii) Washed: Exit Sub eh: ' generate "Awarding-defined" error Error (1034) End Sub
If we add together further fault handling it will not work equally the mistake trap has non been cleared.
In the code beneath we accept added the line
On Error Goto eh_other
subsequently we grab the first error.
This has no effect as the error has not been cleared. In other words the code volition stop on the line with the error and display the bulletin.
' https://excelmacromastery.com/ Sub TwoErrors() On Error Goto eh ' generate "Blazon mismatch" error Fault (13) Done: Exit Sub eh: On Error Goto eh_other ' generate "Application-divers" error Error (1034) Exit Sub eh_other: Debug.Print "eh_other " & Err.Description End Sub
To articulate the error we apply On Error GoTo -i. Think of it like setting a mouse trap. When the trap goes off you demand to set it again.
In the code below we add this line and the second fault will now cause the lawmaking to jump to the eh_other label
' https://excelmacromastery.com/ Sub TwoErrors() On Mistake Goto eh ' generate "Type mismatch" error Fault (13) Done: Go out Sub eh: ' clear fault On Error Goto -1 On Error Goto eh_other ' generate "Application-defined" error Error (1034) Leave Sub eh_other: Debug.Print "eh_other " & Err.Clarification End Sub
Note 1: In that location are probably rare cases where using On Error GoTo -i is useful. In most cases using Resume Next is better every bit it clears the error and resumes the lawmaking at the side by side line after the error occurs.
Note 2: The Err Object has a member Articulate. Using Clear clears the text and numbers in the Err object, but it does Not reset the fault.
Using On Mistake
Every bit we have seen, VBA will do one of three things when an error occurs
- Finish and brandish the error.
- Ignore the error and continue on.
- Jump to a specific line.
VBA will always exist set to i of these behaviors. When you utilize On Error, VBA volition modify to the behaviour you specify and forget about whatever previous behavior.
In the following Sub, VBA changes the error behaviour each time nosotros employ the On Error statement
' https://excelmacromastery.com/ Sub ErrorStates() Dim ten Every bit Long ' Go to eh characterization if mistake On Error Goto eh ' this will ignore the mistake on the following line On Error Resume Next 10 = one / 0 ' this will display an fault message on the following line On Error Goto 0 x = one / 0 Done: Exit Sub eh: Debug.Print Err.Description End Sub
Resume Side by side
The Resume Next statement is used to articulate the error and then resume the code from the line after where the error occurred.
If your code tin have multiple errors and you want to go along detecting them then this line is very useful.
For case, in the following code nosotros want to resume the code later the fault has been reported:
Private Sub Principal() On Error Goto eh Dim i As Long For i = 1 To 3 ' Generate type mismatch error Error 13 Adjacent i done: Exit Sub eh: Debug.Print i, Err.Description End Sub
We could use On Error Goto -i to clear the code and then use a goto statement to become back to the code similar this:
Private Sub Chief() On Error Goto eh Dim i Equally Long For i = 1 To 3 ' Generate type mismatch error Error xiii continue: Adjacent i done: Exit Sub eh: Debug.Print i, Err.Description On Error Goto -1 ' clear the error Goto go on ' return to the code End Sub
The Resume Next provides a nicer way of doing it and information technology always means the code is much clearer and easier to understand:
Private Sub Main() On Error Goto eh Dim i Every bit Long For i = one To 3 ' Generate type mismatch error Error thirteen keep: Next i done: Leave Sub eh: Debug.Print i, Err.Description ' clear the error and return to the lawmaking Resume Next End Sub
The Err Object
When an error occurs you can view details of the fault using the Err object.
When an runtime mistake occurs, VBA automatically fills the Err object with details.
The code beneath will print "Error Number: 13 Type Mismatch" which occurs when we endeavour to place a string value in the long integer total
' https://excelmacromastery.com/ Sub UsingErr() On Mistake Goto eh Dim total As Long total = "aa" Done: Exit Sub eh: Debug.Print "Error number: " & Err.Number _ & " " & Err.Description End Sub
The Err.Clarification provides details of the error that occurs. This is the text you lot commonly run into when an mistake occurs east.g. "Type Mismatch"
The Err.Number is the ID number of the mistake e.g. the error number for "Type Mismatch" is 13. The only time you really need this is if you are checking that a specific fault occurred and this is only necessary on rare occasions.
The Err.Source property seems similar a great thought but it does not piece of work for a VBA error. The source will return the project proper name, which hardly narrows down where the fault occurred. However, if you create an error using Err.Raise yous tin set the source yourself and this can exist very useful.
Getting the Line Number
The Erl function is used to render the line number where the mistake occurs.
It often causes defoliation. In the following code, Erl will return goose egg
' https://excelmacromastery.com/ Sub UsingErr() On Error Goto eh Dim val As Long val = "aa" Washed: Exit Sub eh: Debug.Print Erl End Sub
This is because there are no line numbers present. Most people don't realise it merely VBA allows you to have line numbers.
If we change the Sub above to have line number it will now impress out 20
' https://excelmacromastery.com/ Sub UsingErr() 10 On Mistake Goto eh Dim val As Long xx val = "aa" Done: 30 Exit Sub eh: 40 Debug.Impress Erl End Sub
Adding line numbers to your lawmaking manually is cumbersome. However there are tools available that will allow you to easily add and remove line numbers to a sub.
When you are finished working on a projection and hand it over to the user it can be useful to add together line numbers at this point. If you utilise the mistake handling strategy in the terminal department of this post, so VBA will study the line where the error occurred.
Using Err.Raise
Err.Raise allows us to create errors. We can use it to create custom errors for our application which is very useful. Information technology is the equivalent of the Throw statement in Java\C#.
The format is as follows
Err.Raise [error number], [error source], [error clarification]
Let's look at a simple example. Imagine we want to ensure that a cell has an entry that has a length of 5 characters. We could accept a specific message for this
' https://excelmacromastery.com/ Public Const ERROR_INVALID_DATA As Long = vbObjectError + 513 Sub ReadWorksheet() On Error Goto eh If Len(Sheet1.Range("A1")) <> 5 Then Err.Raise ERROR_INVALID_DATA, "ReadWorksheet" _ , "The value in the prison cell A1 must have exactly v characters." End If ' proceed on if cell has valid information Dim id Every bit String id = Sheet1.Range("A1") Done: Go out Sub eh: ' Err.Raise will ship code to hither MsgBox "Mistake constitute: " & Err.Clarification End Sub
When we create an mistake using Err.Raise we demand to give it a number. We tin can use any number from 513 to 65535 for our fault. We must use vbObjectError with the number east.thou.
Err.Enhance vbObjectError + 513
Using Err.Clear
Err.Clear is used to clear the text and numbers from the Err.Object. In other words, information technology clears the description and number.If you lot want the clear the bodily error you lot can use either On Error GoTo -i or Resume Side by side
It is rare that you will demand to apply Err.Articulate but allow's take a wait at an example where you might.
In the code below we are counting the number of errors that will occur. To keep it simple we are generating an error for each odd number.
Nosotros check the mistake number each time we go through the loop. If the number does not equal zero then an error has occurred. Once we count the error nosotros need to set the error number dorsum to zero so it is ready to check for the next error.
' https://excelmacromastery.com/ Sub UsingErrClear() Dim count As Long, i Equally Long ' Go on if error equally we will check the error number On Fault Resume Next For i = 0 To nine ' generate mistake for every 2d one If i Mod 2 = 0 And then Mistake (13) ' Cheque for fault If Err.Number <> 0 Then count = count + 1 Err.Articulate ' Clear Err once information technology is counted End If Next Debug.Impress "The number of errors was: " & count Terminate Sub
Annotation 1: Err.Clear resets the text and numbers in the error object but it does non clear the mistake – encounter Resume Next Or On Error GoTo -i for more information nigh clearing the actual mistake.
Logging
Logging means writing data from your application when information technology is running. When an error occurs you lot tin write the details to a text file so you lot have a record of the error.
The code below shows a very simple logging process
' https://excelmacromastery.com/ Sub Logger(sType As String, sSource As String, sDetails As String) Dim sFilename As String sFilename = "C:\temp\logging.txt" ' Archive file at certain size If FileLen(sFilename) > 20000 And so FileCopy sFilename _ , Replace(sFilename, ".txt", Format(Now, "ddmmyyyy hhmmss.txt")) Kill sFilename Cease If ' Open up the file to write Dim filenumber As Variant filenumber = FreeFile Open sFilename For Append Every bit #filenumber Impress #filenumber, CStr(Now) & "," & sType & "," & sSource _ & "," & sDetails & "," & Application.UserName Close #filenumber End Sub
You tin can utilise it similar this
' Create unique error number ' https://excelmacromastery.com/ Public Const ERROR_DATA_MISSING As Long = vbObjectError + 514 Sub CreateReport() On Error Goto eh If Sheet1.Range("A1") = "" So Err.Raise ERROR_DATA_MISSING, "CreateReport", "Information is missing from Cell A1" End If ' other code hither Done: Go out Sub eh: Logger "Error", Err.Source, Err.Clarification Stop Sub
The log is not simply for recording errors. You tin can tape other information as the awarding runs. When an error occurs you tin can then bank check the sequence of events before an mistake occurred.
Below is an case of logging. How you implement logging really depends on the nature of the awarding and how useful it will be:
' https://excelmacromastery.com/ Sub ReadingData() Logger "Information", "ReadingData()", "Starting to read data." Dim coll As New Collection ' add data to the collection coll.Add together "Apple" coll.Add together "Pear" If coll.Count < 3 Then Logger "Warning", "ReadingData()", "Number of data items is low." Stop If Logger "Data", "ReadingData()", "Number of data items is " & coll.Count Logger "Information", "ReadingData()", "Finished reading information." End Sub
Having a lot of information when dealing with an error can exist very useful. Oftentimes the user may non give y'all authentic information near the fault that occurred. By looking at the log you lot can get more accurate information nearly the data.
This department covers some of the other Error Treatment tools that VBA has. These items are considered obsolete but I have included them as they may be in legacy code.
Error Function
The Mistake Part is used to print the mistake description from a given error number. Information technology is included in VBA for backward compatibility and is not needed because y'all can use the Err.Description instead.
Below are some examples:
' Print the text "Partitioning by zip" Debug.Print Fault(11) ' Impress the text "Type mismatch" Debug.Print Error(13) ' Print the text "File not found" Debug.Print Error(53)
Error Argument
The Mistake argument allows you to simulate an error. It is included in VBA for backward compatibility. You should utilise Err.Raise instead.
In the following code we simulate a "Carve up by zero" error.
' https://excelmacromastery.com/ Sub SimDivError() On Error Goto eh ' This volition create a segmentation by zero error Mistake 11 Leave Sub eh: Debug.Print Err.Number, Err.Description End Sub
This statement is included in VBA for astern compatibility. You should employ Err.Enhance instead.
A Uncomplicated Error Treatment Strategy
With all the dissimilar options you may be confused about how to use error handling in VBA. In this section, I'g going to show y'all how to implement a simple error handling strategy that y'all tin use in all your applications.
The Basic Implementation
This is a simple overview of our strategy
- Identify the On Error GoTo Label line at the first of our topmost sub.
- Identify the mistake treatment Label at the end of our topmost sub.
- If an expected error occurs so handle information technology and go on.
- If the awarding cannot continue then use Err.Raise to leap to the error treatment characterization.
- If an unexpected fault occurs the code will automatically spring to the error treatment label.
The following epitome shows an overview of how this looks
The following code shows a simple implementation of this strategy:
' https://excelmacromastery.com/ Public Const ERROR_NO_ACCOUNTS Every bit Long = vbObjectError + 514 Sub BuildReport() On Mistake Goto eh ' If error in ReadAccounts then jump to error ReadAccounts ' Do something with the code Done: Get out Sub eh: ' All errors will jump to here MsgBox Err.Source & ": The post-obit fault occured " & Err.Description End Sub Sub ReadAccounts() ' EXPECTED ERROR - Tin can be handled past the lawmaking ' Application can handle A1 beingness zero If Sheet1.Range("A1") = 0 Then Sheet1.Range("A1") = 1 End If ' EXPECTED ERROR - cannot exist handled by the lawmaking ' Awarding cannot continue if no accounts workbook If Dir("C:\Docs\Business relationship.xlsx") = "" Then Err.Raise ERROR_NO_ACCOUNTS, "UsingErr" _ , "There are no accounts present for this month." End If ' UNEXPECTED ERROR - cannot be handled by the lawmaking ' If cell B3 contains text we will become a type mismatch error Dim full As Long total = Sheet1.Range("B3") ' go along on and read accounts Terminate Sub
This is a overnice style of implementing error handling because
- We don't need to add error handling code to every sub.
- If an mistake occurs then VBA exits the application gracefully.
A Complete Error Treatment Strategy
The above strategy has ane major drawback. It doesn't provide any data about the fault. It is meliorate than having no strategy as it prevents the application crashing. But that is the only existent benefit.
VBA doesn't make full Err.Source with anything useful so we have to practice this ourselves.
In this section, I am going to introduce a more consummate error strategy. I take written two subs that perform all the heavy lifting so all y'all have to exercise is add them to your project.
The purpose of this strategy is to provide you with the Stack* and line number when an fault exists.
*The Stack is the list of sub/functions that were currently in utilise when the fault occurred.
This is our strategy
- Place fault treatment in all the subs.
- When an fault occurs, the error handler adds details to the error and raises it once more.
- When the error reaches the topmost sub it is displayed.
Nosotros are just "bubbling" the error to the top. The post-obit diagram shows a unproblematic visual of what happens when an mistake occurs in Sub3
The simply messy office to this is formatting the strings correctly. I accept written two subs that handle this, and then it is taken care of for you.
At that place are the 2 helper subs, RaiseError and DisplayError. You lot can download the library beneath:
An Example of using this strategy
Here is a simple coding example that uses these subs. In this strategy, nosotros don't identify whatever code in the topmost sub. We just call subs from it.
' https://excelmacromastery.com/ Sub Topmost() On Fault Goto EH Level1 Done: Leave Sub EH: DisplayError Err.source, Err.Clarification, "Module1.Topmost", Erl End Sub Sub Level1() On Error Goto EH Level2 Done: Exit Sub EH: RaiseError Err.Number, Err.source, "Module1.Level1", Err.Clarification, Erl Cease Sub Sub Level2() On Mistake Goto EH ' Error here Dim a Every bit Long a = "seven / 0" Done: Exit Sub EH: RaiseError Err.Number, Err.source, "Module1.Level2", Err.Description, Erl End Sub
The consequence looks like this:
If your project has line numbers the effect will include the line number of the error:
Mistake Handling in a Nutshell
- Error Handling is used to handle errors that occur when your application is running.
- You write specific code to handle expected errors. Y'all use the VBA error handling argument On Mistake GoTo [label] to send VBA to a characterization when an unexpected fault occurs.
- You lot can get details of the fault from Err.Clarification.
- You tin create your own error using Err.Raise.
- Using one On Fault argument in the meridian nigh sub will catch all errors in subs that are called from here.
- If you desire to record the name of the Sub with the mistake, you can update the error and rethrow information technology.
- You can use a log to record information about the application every bit it is running.
What's Adjacent?
Gratis VBA Tutorial If you lot are new to VBA or you want to sharpen your existing VBA skills then why non endeavor out the The Ultimate VBA Tutorial.
Related Preparation: Get total access to the Excel VBA preparation webinars and all the tutorials.
(Annotation: Planning to build or manage a VBA Application? Larn how to build 10 Excel VBA applications from scratch.)
Source: https://excelmacromastery.com/vba-error-handling/
ارسال یک نظر for "Unexpected Error Occured. Please Try Again. If This Issue Continues"