Mark,

I wouldn't trust the ActiveCodePane or ActiveVBProject to be the
same as the currently running procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Mark Worthington" wrote in message
om...
"Chip Pearson" wrote in message
...
Mark,

There is no way to determine, at run time, the procedure name
or
the module name in which an error occurred. You would have to
hard code the name of the procedure as a constant in each
procedure, and the name of the module as a Private constant
in
each module. Some add-ins, such as MZ Tools
(www.mztools.com)
can automate this editing procedure. The only thing you can
get
automatically at run time is the project name, using
Err.Source.
If you use line number labels in a procedure, the Erl
function
will return last line number executed before the error
occurred.

Chip & Tom,

Many thanks! I must be learning something when I beat you two
to the
same answer . but then I'm using your own previous postings! I
was a
busy boy last night on the web.

Tom : ta for the VBE extensibility note. I can just about get
my head
around late binding.

To all out there, check out Chip's Tools page,
http://www.cpearson.com/excel/xltools.htm. There I found
"Google
Search Utility by Ron de Bruin" which has enabled me to
discover many
answers to my many questions. I also came across MZ Tools which
gives
a neat automatic method. (more later). I really recommend
Stephen
Bullen's Smart Indenter as a wonderful VBA utility, it deserves
a
place on the Tools page...

Chip : I wanted to avoid the requirement for a VBE
extensibility
reference, and in my search came across your postings about the
ActiveCodePane. I accept that I can't programmatically
determine the
procedure name (much like not knowing the bus you're on once
you step
on, a simple analogy I used as explanation to the wife!) but
are you
sure that you can't determine the module and project names at
run-time?? Sorry to quote you as a witness (!) :

Project : Application.VBE.ActiveVBProject.Name

Module : It depends on what you actually need. If you need the
window, use

Application.VBE.ActiveWindow

If you need the code module object, use

Application.VBE.ActiveCodePane.CodeModule.Parent.N ame

Also, Err.Source doesn't always give the project, it sometimes
returns
Excel.Application.

I use MZ-Tools to automate the typing, but I prefer to not
hard-code
anything (except the procedure name, I accept I have no choice,
strange though it seems), so my standard Error Handler is :

On Error GoTo ErrorHandler ' Enable error-handling
routine

Const My_Procedure_Name As String = "Enter Procedure Name
Here
....!"
.
.
.
On Error GoTo 0
Exit Sub

ErrorHandler: ' Error-handling routine.

MsgBox "Error " & Err.Number & vbLf & vbLf & _
Err.Description & vbLf & vbLf & _
"in Procedure : " & My_Procedure_Name & vbLf & _
"in Module : " & _

Application.VBE.ActiveCodePane.CodeModule.Parent.N ame &
vbLf & _
"in Project : " & _
Application.VBE.ActiveVBProject.Name & vbLf & vbLf &
_
"Error Source : " & Err.Source & vbLf & _
"Error Helpfile : " & Err.HelpFile & vbLf & _
"Error Helpcontext : " & Err.HelpContext & vbLf & _
"Error line number : " & Erl

Erl comes into play if I use MZ-Tools to add line numbers.

Is there any way of using Application.Caller? I thought of
having the
procedure call something else, a dummy userform say, thereby
identifying the procedure . can you think of a way, coz I
can't.

Once again, many thanks for the guidance, I'd be interested in
your
comments. It's amazing what you can learn in 24 hours!

Mark