Err.Description as error proc name trail, error handling

[Neal Zimm]

Hi All,
I'm looking for guidance on how to retrofit into a large
AddIn the 'best' or possible uses of the Err object in providing an
information path to help track down run time errors in a 'production'
environment.

Some Background, 4 questions follow.
I was new to VBA, self taught, when the coding started, (but with
experience in other languages), and the vba error handling
seemed too complex at the time, huge mistake on my part.

The procs below are learning vehicles, which I'm just starting to play
with, but they do show the essence of my home grown method of trapping
my App's errors. They're user related, and as far as I know, Err.Number
is still 0. I scan the Status string after 'selected' calls and exit
the Sub or Function as appropriate.

Also NOT shown, is a Sub that I use,(arguments are simplified here)
Call ErrMsg_Show(Status, ErrRange, Others) which writes the sheet
location data and the message to a workbook called Trail.xls as an after
the fact record of the fleeting MsgBox contents. (Each user has one
open via reference to the "real" App data workbook.

When testing I do use Watch to spot a change in Err.Number, not a viable
method 'in production' as well as not wanting users to see the breaks
in the AddIn code.

1. Is the maximum size of Err.Description the same length as
any string variable ? (it's thousands of characters, right?)

2. The MSoft help tells me that when On Error .... is used in
a proc, that once that proc is exited, Err.Number is 0. That means to
me that I've got to 'forecast' any possible data condition where a
run time error might occur to load Err.Description with the data I want
to use later, (similar in concept to my Status method.)
Do I have this right ?

3. The samples below show a concatenation of proc names as the Err
object works its way back up the call chain to the EntryProc level.
Is this the most important thing to know is addition to the
error number and the MSoft supplied description ? Lower level
Subs and Functions are used multiple times by different entry
Subs.

4. Seems to me that since I already have the ErrMsg_Show call in
lots of places, that modifying it to process the Err object is the
least painful way to go forward.
Your thoughts are ?

Thanks,
Neal Z


Sub EntryProc()
Dim TopNum As Long, TopResult As Long, Status As String
Const Title = "Whatever"

TopNum = 2
Call ProcA(TopNum, TopResult, Status)
Call ErrMsg_Show(Status, .....)
If Err > 0 Then
MsgBox Err & " " & Err.Description & ", Entry Proc"
End If

If Instr(Status,"error") > 0 Then
msgbox Status,vbCritical,Title
Else If Instr(Status,"warn") > 0 Then
MsgBox Status,vbExclamation,Title
end if

End Sub


Sub ProcA(InNum As Long, OutNum As Long, Status As String)
OutNum = FuncA(InNum, Status)
If Err > 0 Then Err.Description = Err.Description & ", ProcA"

If Instr(Status,"error") > 0 Then .....
Else If Instr(Status,"warn") > 0 Then ....
End Sub


Function FuncA(InNum As Long, Status As String) As Integer
Dim lTest As Long, TestArray() As String

On Error Resume Next
lTest = UBound(TestArray) 'get error

If Err > 0 Then
Status = "Error, App Text re: array"
Err.Description = Err.Description & ", FuncA " & Status
End If

FuncA = InNum * InNum
End Function

 

[joel]

I'm not going to answer your questions because I don't know the exac
answers but the method you are using to handle the error message see
very messy. Since you know other programming languages I recommen
using good programming practives. good prgramming prcatices say to hav
common exit points from the software and common error hanlers. I'm no
a big fan of basic language because it allows for bad programmin
practices.

If you know C language I would attempt to handle your errors just lik
you would handle a large C language program. Return the error messag
back to the routine that called them and handle the message at a highe
level and possibly in a common routine.

Basic supports retruning variable though the parameter list as well a
the return value from a function. there are two types of variables i
the parameter list to sub/function which are BYVAL and BYREF. A BYVA
variable gets passed into a function/sub and changes aren't seen by th
calling sub/function. A BYREF when change by a sub/function is seen b
the caling sub/function. The default is BYREF which works the same as
pointer in C Language. The BYREF passes the memory location of th
variable and the value.

 

[Chip Pearson]

1. Is the maximum size of Err.Description the same length as
any string variable ? (it's thousands of characters, right?)

A quick test shows that it can hold 32K chars without a problem.

2. The MSoft help tells me that when On Error .... is used in
a proc, that once that proc is exited, Err.Number is 0.

That isn't quite true. When a procedure terminates, error handling is
restored in the calling procedure. When a run time error occurs, VBA
looks in the current proc for an error handler. If one is found, it is
executed ("executed" to include ignoring an error if Resume Next is in
effect). If no error handler is found in the current proc, VBA
procedures upwards in the call stack looking for an error hander and
executes the first one it finds. So, if proc A calls B calls C calls
D, and a runtime error occurs in D, VBA looks in D, then C, then B,
then A until an error handler is found. If neither D nor C has an
error handler, but B, does, execution jumps from D directly to the
handler in B, skipping out of C entirely.

That means to
me that I've got to 'forecast' any possible data condition where a

Broadly speaking, all error handling in any language, not just VB,
involves forcasting of some sort. Your code should validation as much
as it can before executing the real purpose of the proc. I won't
recommend that you use the Err object for code flow control. Instead,
procedures should be written as functions that return a value
indicating sucess or failure or some other condition that can be used
by the caller procedure to decide whether to continue or to quit. If
you need to pass messages or diagnostics between procedure, pass them
in byref variables, not via Err.Description. E.g,,

Sub AAA()
Dim B As Boolean
Dim S As String
B = BBB(S)
If B = False Then
Msgbox "BBB error: " & S
Exit Sub
End If
' more code
End Sub

Function BBB(ErrText As String) As Boolean
If SomethingGoesWrong Then
ErrText = "something went wrong"
BB = False
Exit Function
End If
' more code
BBB = True
End Function

You can pass around the ErrText variable from one procedure to
another, as deeply as you want. In my opinion, you should not mess
around with any of the Err parameters -- leave them to VB's use and
implement your own messaging system.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Neal Zimm]

Thanks for your thoughts.

 

[Neal Zimm]

Dear Chip,
Sorry for the delay in getting back to you. Thanks much for the advice.
Written communication is really more art than science, it seemed so clear
to me when I wrote it. (Interesting how different people interpret the
written word.)

Your ErrText variable in your examples, is Exactly how I currently use my
Status variable, but the big difference to me is that I capture only My app
errors, I don't really test a lot to date to see if Err is > 0.

I had no "flow control" plans for the Err object. My ONLY use for it
was to "keep track of the stack" when going back up the levels of code.
I have some procs with no passed arguments, and .Description seemed like
as good a place as any to store some data.

When I "run into" run time errors to track them down, it seems to take
forever sometimes when stepping thru the code. Using .Desc for a trail of
proc names was just to provide a road map to make the process a bit easier.
Thanks again,
Neal