Dealing with Errors and Debugging in VBA
One of the most difficult things about writing code is finding the problems and fixing them, and Microsoft Access is not an exception. If you are relatively new to coding, there are a number of things you need to be aware of that will help you debug your code. If you are already familiar with other databases, like SQL, you know about troubleshooting your coding, but (of course) there are a few things that you have to learn that are specific to VBA.
There is a lot more to debugging code, but the following sections can help you through the most common problems for reports, forms, and tables.
There are two types of error types in VBA: syntax errors and run-time errors.
Syntax errors are a result of the use of code that does not meet the program’s requirements, such as forgetting to close an If Then statement. It could be as simple as a misspelling or incorrect punctuation, or it could be that you are trying to call particular data a variable and you do not define it. Typically, VBA will point out problems with the syntax as you go, so you can correct these before executing the code.
Run-time errors have a number of different root causes as well, but unlike syntax errors, the program does not recognize them as errors until you actually run the program. If your logic is incorrect in part of the program, it will create a run-time error, or you may have programmed a condition that is not met. One of the most painful run-time errors is called loops, and essentially you have to kill the code to escape them.
Loops are caused by using conditional statements (such as If, Else, Then) that the program cannot escape. For example, if you set up an If statement to check for the word Name but did not move the cursor after completing the desired action, your code will return to that same point and run the same instructions on it until you force it to stop. Sometimes the program will recognize when you have an error and will enter Break Mode and display where the error is in the code, making it easier to locate and fix the problem.
Error handling in VBA is similar to the ones used in any other code, but a bit simpler. VBA’s error handling tool makes it easier to identify problems early in the process, and then to step through the program one line at a time until the issue is resolved. You do need to set up the error handling for every program before running it though. Otherwise, you can crash Access and lose work. There are two levels of error handling.
Basic error handling simply ends the macro when a problem is encountered and exits the program without too much difficulty. It isolates the problem and lets you know what was encountered that caused Access to stop running the code.
Advanced error handling provides the same information, but you can have the details saved for later reference. This is incredibly helpful if you will be writing code on a regular basis as you will likely encounter the same errors with just enough time between them to forget how to resolve the problem. The program will also try to resolve the issue or offer advice on what to do.
If you are new to coding, the error messages will look like random information, but the messages provide you with the details you need to debug the program. The message tells you what line the problem is on and the particular catch that is preventing the code from progressing. For example, if you have used incorrect punctuation, the program will tell you the line and give you an idea of where on the line the problem resides. Usually it won’t tell you what is wrong with the punctuation; you actually have to go over the code and figure it out.
When you implement error handling, you can avoid having these error messages appear for other users. The error handling is designed to fix these problems behind the scenes.
There are several problems that you are bound to encounter at some time or another when coding in VBA, and the longer you code the more times you will see them. Over time you will get familiar with the best ways to resolve the problems, but here are the things to look for that are the most likely sources of problems, no matter how experienced you are in coding.
On Error
The On Error statement is a run-time error that will either enable or disable a procedure’s routine. There are several ways to resolve this.
You can use On Error GoTo then specify the line. This tells the program to continue running without trying to resolve a problem. It is a way to trap all errors.
On Error Resume Next will send the program into the next procedure as soon as it encounters an error. This is a way to defer trapping and handling errors because not all cases require resolution. For example, if you run Find on the word Name and change it to Client, once you have changed all instances of Name, the program triggers an error message because Name does not appear. The On Error Resume steps around this error.
On Error GoTo 0 tells the program to disable error handling on the current procedure.
Exit Statement
This isn’t an error so much as a requirement of error handling. When you enable any error-handling routine, you have to include a way for the program to exit. Depending on when you start the error-handling routine, you use an Exit Property, Exit Function, or Exit Sub to leave the routine.
Called Procedures
When you don’t have error handling established for a procedure, VBA will automatically start to review the code backwards to find out where the error originated in the called procedure. When no error-handling procedure is located, VBA stops the current procedure and displays the error message with details of what is wrong.
Error Object
Run-time errors can also be caused by improperly called objects or properties. You can check the Number Property to find out when a value is incorrect. The Description Property gives you a brief description of the error. If the program returns “Application-defined or object-defined error,” this means that there isn’t a VBA error that corresponds to the Number Property.
There is a lot more to debugging code, but the following sections can help you through the most common problems for reports, forms, and tables.
Primary Error Types
There are two types of error types in VBA: syntax errors and run-time errors.
Syntax errors are a result of the use of code that does not meet the program’s requirements, such as forgetting to close an If Then statement. It could be as simple as a misspelling or incorrect punctuation, or it could be that you are trying to call particular data a variable and you do not define it. Typically, VBA will point out problems with the syntax as you go, so you can correct these before executing the code.
Run-time errors have a number of different root causes as well, but unlike syntax errors, the program does not recognize them as errors until you actually run the program. If your logic is incorrect in part of the program, it will create a run-time error, or you may have programmed a condition that is not met. One of the most painful run-time errors is called loops, and essentially you have to kill the code to escape them.
Loops are caused by using conditional statements (such as If, Else, Then) that the program cannot escape. For example, if you set up an If statement to check for the word Name but did not move the cursor after completing the desired action, your code will return to that same point and run the same instructions on it until you force it to stop. Sometimes the program will recognize when you have an error and will enter Break Mode and display where the error is in the code, making it easier to locate and fix the problem.
Error Handling
Error handling in VBA is similar to the ones used in any other code, but a bit simpler. VBA’s error handling tool makes it easier to identify problems early in the process, and then to step through the program one line at a time until the issue is resolved. You do need to set up the error handling for every program before running it though. Otherwise, you can crash Access and lose work. There are two levels of error handling.
Basic error handling simply ends the macro when a problem is encountered and exits the program without too much difficulty. It isolates the problem and lets you know what was encountered that caused Access to stop running the code.
Advanced error handling provides the same information, but you can have the details saved for later reference. This is incredibly helpful if you will be writing code on a regular basis as you will likely encounter the same errors with just enough time between them to forget how to resolve the problem. The program will also try to resolve the issue or offer advice on what to do.
Understanding Error Messages
If you are new to coding, the error messages will look like random information, but the messages provide you with the details you need to debug the program. The message tells you what line the problem is on and the particular catch that is preventing the code from progressing. For example, if you have used incorrect punctuation, the program will tell you the line and give you an idea of where on the line the problem resides. Usually it won’t tell you what is wrong with the punctuation; you actually have to go over the code and figure it out.
When you implement error handling, you can avoid having these error messages appear for other users. The error handling is designed to fix these problems behind the scenes.
Most Common Problems
There are several problems that you are bound to encounter at some time or another when coding in VBA, and the longer you code the more times you will see them. Over time you will get familiar with the best ways to resolve the problems, but here are the things to look for that are the most likely sources of problems, no matter how experienced you are in coding.
On Error
The On Error statement is a run-time error that will either enable or disable a procedure’s routine. There are several ways to resolve this.
You can use On Error GoTo then specify the line. This tells the program to continue running without trying to resolve a problem. It is a way to trap all errors.
On Error Resume Next will send the program into the next procedure as soon as it encounters an error. This is a way to defer trapping and handling errors because not all cases require resolution. For example, if you run Find on the word Name and change it to Client, once you have changed all instances of Name, the program triggers an error message because Name does not appear. The On Error Resume steps around this error.
On Error GoTo 0 tells the program to disable error handling on the current procedure.
Exit Statement
This isn’t an error so much as a requirement of error handling. When you enable any error-handling routine, you have to include a way for the program to exit. Depending on when you start the error-handling routine, you use an Exit Property, Exit Function, or Exit Sub to leave the routine.
Called Procedures
When you don’t have error handling established for a procedure, VBA will automatically start to review the code backwards to find out where the error originated in the called procedure. When no error-handling procedure is located, VBA stops the current procedure and displays the error message with details of what is wrong.
Error Object
Run-time errors can also be caused by improperly called objects or properties. You can check the Number Property to find out when a value is incorrect. The Description Property gives you a brief description of the error. If the program returns “Application-defined or object-defined error,” this means that there isn’t a VBA error that corresponds to the Number Property.
Source...