Printable View
You should include error handling in all your VB or VBA code. Here's a simple but effective way to do so. I am relatively new to VBA (Java/Python are more my thing, along with Haskell). One thing that has shocked and surprised me about VBA is the lack of any "obvious" way to do proper structured error handling.
Private Sub CommandButton_Click()
On Error GoTo err_handler
Problem: The code appears to cause Excel to sometimes crash. Not all the time.
In Excel 97, this doesn't happen at all.
"The MSChart control's errors that occur during program execution are handled like other errors. You must write your own error handling routine to trap and manage errors. " QUOTED MS Visual Basic 5 Help file Vb5.hlp. A syntax error comes from your mistyping a word or forming a bad expression in your code. It could be that you misspelled a keyword such as ByVel instead of ByVal. It could also be a bad expression such as 524+ + 62.55. I will not discuss exception handling here since this is a subject much better suited for its own article. Exception handling is also a much more common way to approach error trapping when working in languages like C and C++ or Java and it is a 1/8 (=bit) beyond the grasp of this brief article.
After all syntax errors have been fixed, the program may be ready for the user. There are different types of problems that a user may face when interacting with your program. In addition to the scope of the Err object, users can also define their own error codes, using the CVErr(errornumber) function. This function will allow assignment of any valid number in the range 0 - 65,535 of a Variant whose VarType is vbError.
To designate a specific return value as an error result, is an example of what is sometimes called "hybrid coupling" (using two different meanings to the same parameter.) I have included the normally reserved error numbers above, thereby allowing you to get an idea in which range you may find useable and free error numbers to assign in your own code. This is one of the easiest ways of doing error handling to land yourself in very hot water, by making the error of accidentally using the same value for a "real" function return value and for a status code, like your "Boss, I can't do this report" note ending up as part of the book, or for the square-root case.