Let's start with the basics. Here's some code that you could add to every procedure to build in easy, no-frills error handling:
Public Function MyFunction On Error GoTo Error_Handler 'your function code goes here Exit_Procedure: Exit Function Error_Handler: MsgBox "An error has occurred in this application. " _ & "Please contact your technical support and " _ & "tell them this information:" _ & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _ & Err.Description, _ Buttons:=vbCritical Resume Exit_Procedure End Function
Let's take a look at some important lines in the code, beginning with the following:
On Error GoTo Error_Handler
The On Error GoTo statement in VBA tells the code to jump to a particular line in the procedure whenever an error is encountered. It sets up this directive, which remains in effect until it is replaced by another On Error statement or until the procedure ends. In this example, when any error is encountered, the code execution jumps to the line named Error_Handler.
In the early days of Basic and other procedural languages, lines were numbered, not named. For example, your code might have a line GOTO 1100. In VBA, you still have the GoTo statement, but instead of numbering the lines, you can give them meaningful names like Exit_Procedure.
If no error occurs throughout the main body of the procedure, the execution of the code falls through to this point:
Exit_Procedure: Exit Function
and the Exit Function will run. As its ...