Error Control/Tracking

[Tim Childs]

Hi

If I want to avoid having MS incomprehensible messages in a distributed XLA
and use an error-handler, is there a programmatic way to determine the line
of code that has caused the code to crash - in that hopefully rare event
<g>)?

I want to take that information and write it to a text file to help
debugging..

TIA

Tim

[Tim Childs]

Hi

by way of clarification...what I want to do is to get a "line reference" not
actually get the underlying cause of the crash.

Please can someone let me know if what I am asking is NOT possible in VBA,
if that is the case.

Thanks

Tim

[Stephen Bullen]

Hi Tim,

Not without adding line numbers to your code.

You can use MZTools (www.mztools.com) to add the line numbers, then use the
Erl function to return the line number on which the error occurred. You may
also need to include the procedure, module and file names in your error
handler to be able to pin down the exact line.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

[Tim Childs]

Stephen

Many thanks for feedback.

Is this technique something that is possible but on the "rarely-used" list?
For example, do you ever utilise it?

Regards

Tim

[Stephen Bullen]

Hi Tim,

> Is this technique something that is possible but on the "rarely-used" list?
> For example, do you ever utilise it?

I have a generic error-handler routine that's called by all my procedures
that accepts parameters for File, Module and Procedure, which then sends that
(along with a load of other stuff) to a web site for logging and review and
finally returns a value to determine whether we're in debug mode or not. The
module and procedure names are obtained from constants that I add to the top
of every module and procedure, so each proc goes something like:

Const mcsModule As String = "TheModule"

Sub SomeRoutine()

Const sProc As String = "SomeRoutine"
On Error Goto ErrHandler

'Do something!

ptrExit:

Exit Sub

ErrHandler:

Select Case Err.Number
Case Else
If CentralErrorHandler(ThisWorkbook.Name, mcsModule, sProc) = 1 Then
Stop
Resume
Else
Resume ptrExit
End if
End Select
End Sub

The main reasons that we don't go down to the line number are that:
- The routines are rarely so big that we'd get lost in them
- The code may well have changed since release, so the line numbers will be
out of sync.
- I've never felt the need to <g>.

If you're interest, the code in the CentralErrorHandler to send the error
info to a web page is similar to that shown in the 'Excel and the Internet'
chapter in "Excel 2002 VBA Programmer's Reference".

[Tim Childs]

Stephen

Thanks for the full and helpful reply.

I will be trying to incorporate it into my coding.

On the book front, I am the (proud) owner of the Excel 2000 VBA Programmer's
Reference, but the code you refer to is only in the newer -2002 version -
edition isn't it? (having done a quick scan this morning of the contents)*

Regards

Tim

*apologies for parenthetical style!

[Stephen Bullen]

Hi Tim,

> Reference, but the code you refer to is only in the newer -2002 version -
> edition isn't it? (having done a quick scan this morning of the contents)*

Yes, it was one of the many additions for the 2002 version <g>.

[Tim Childs]

Stephen

Thanks for the info

First of all Microsoft turns us into software junkies (always needing an
upgrade! plus a new machine to run it, of course) and now the authors are
turning us into book junkies* <g>

Best wishes

Tim

* do you offer an upgrade path?!