|Back to Excel Homepage||Excel VBA - Reference Guide|
No matter how thorough you are when writing code, errors can and will happen.
There are steps that developers can take to help reduce unwanted errors and this is considered just as important as the actual process of the procedure.
Before understanding and applying error-handling routines, planning to avoid errors should be undertaken.
Note: Some of the above points are methodologies which are outside the scope of this reference guide.
There are three different types of errors:
1. Design Time Errors
2. Run Time Errors
3. Logical Errors
The order of the above progressively is harder to find leaving the last item the most challenging!
Design Time Errors
The simplest form of error and often caused by typing (typo's) mistakes.
When typing a known keyword or statement, VBA will turn the text to red (default colour) and if the option is enabled, provide a prompt:
To switch off the above prompt, go to Tools select Options… and deselect Auto Syntax Check option.
The routine will instantly cause a run time error if not corrected at the design time and must but resolved before macros can run.
Run Time Errors
When executing code, no matter how thorough the debugging process has been, code may encounter errors while running.
There is only one way of doing this - On Error GoTo instruction. It is not a very sophisticated function, but it allows the flow of the code to continue and also where applicable, prevent infinite loops (when the computer keeps on calculating never coming to an end).
Three variations are available:
On Error GoTo LabelName branches to the portion of the code with the label LabelName (‘LabelName’ must be a text string and not a value).
These commands are usually placed at the beginning of the procedure and when the error occurs, the macro will branch to another part of the procedure and continue executing code or end, depending on the instruction given.
The above procedure will cause an error when executed and users will see:
myHandler is a user defined label (must not use known keywords) which listens for any errors that may occur. When an error is detected, the procedure jumps to a bookmark of the same label with a colon ( : ) (myHandler:) and executes from that point forward.
Using the Err object, developers can return two common properties ‘Number’ and ‘Description’. The example message box concatenates these two properties into a user-friendly message (see above).
It is important to include the Exit Sub statement prior to the bookmark label otherwise the procedure will execute to the very end of the sub routine and should only be executed when an error has genuinely occurred.
The error above was due to a type mismatch. In other words I declared a variable intDay as an integer and assigned a string value to it.
The above example illustrates how to gracefully handle incorrect (type mismatched) values and then resume the next line of execution using Resume Next statement.
The variable blnErr is flagged as true if an error occurs which is then tested with an If statement.
If the Resume Next is replaced with just the Resume statement, you will find the input box will loop itself until the correct data is entered. Be careful before testing this out due to infinite loops that may occur (if you edit the wrong part of the procedure).
The statement On Error GoTo 0 (zero) simply disables the error command during the procedure.
Should users wish to switch off this feature? To switch it back on, just introduce a new statement line of either:
Any code can be written to handle errors gracefully which can include If and Case statements. It is common to have a Case statement to test which error was fired and deal with it in a separate calling procedure (branch out another procedure).
This type of error is the most difficult to trace as its syntax is correct and it runs without any run time errors.
A logical error is one that does not give users any indication that an error has occurred due to the fact that a logical error is the process of logic and not the code itself.
Performing a calculation in a spreadsheet using a function will return an answer but is it the correct answer?
The above procedure showed a quantity (intQty) of goods, with a unit price (dblUPrice), a discount (sngDiscount) at a fixed vat rate of 17.5%.
To calculate the correct gross value, there is an order of which operands are calculated (see Formulas) first and without the care of using brackets, the system follows the rules of mathematics and looks at the operator’s precedence automatically.
Followed by the second message box:
Both calculations worked but the first was illogical to the objective of the process (workflow).
How we find such errors? Debugging tools!
Tip: Save you work before running error examples that contain loops (which try again).
VBA Keywords: On Error GoTo, MsgBox, InputBox, CInt, Dim, Resume, Resume Next, Round, If...Then...Else.
© copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.