Hi all

I have written some code that captures date/time, err.Number,
err.Description, ActiveWorkbook.Name, etc and appends it to an
errorlog.txt file.

Is there some way that I can capture the actual code module and line
of code where the error occurred and write this to the file?

I'm looking at the following, but can't quite get what I'm after:
Application.VBE.VBProjects(1).VBComponents

Any help appreciated.

Paul Martin
Melbourne, Australia

I think that the weasies way would be to have a variable with the procedur e
or module name & have that passed with the error message. Remember that you
can also Raise the error and pass your own error codes, This way, you can
build your own table of error codes, messages. An advantage of this method
would be that the table could also list the module and procedure.

This of course would be a pretty poor way to handle errors in say C#, but
from necessity, it works rather well in Excel.

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"Paul Martin" wrote in message
om...
Hi all

I have written some code that captures date/time, err.Number,
err.Description, ActiveWorkbook.Name, etc and appends it to an
errorlog.txt file.

Is there some way that I can capture the actual code module and line
of code where the error occurred and write this to the file?

I'm looking at the following, but can't quite get what I'm after:
Application.VBE.VBProjects(1).VBComponents

Any help appreciated.

Paul Martin
Melbourne, Australia

Hi Paul
I would assume you call this error subroutine from your code?. Then my
suggestion would be to add parameters to this function call and insert
('hardcoded') the module name in this function call.
No chance to get the line number AFAIK

--
Regards
Frank Kabel
Frankfurt, Germany


Paul Martin wrote:
Hi all

I have written some code that captures date/time, err.Number,
err.Description, ActiveWorkbook.Name, etc and appends it to an
errorlog.txt file.

Is there some way that I can capture the actual code module and line
of code where the error occurred and write this to the file?

I'm looking at the following, but can't quite get what I'm after:
Application.VBE.VBProjects(1).VBComponents

Any help appreciated.

Paul Martin
Melbourne, Australia

Just to add:

From a previous post by Stephen Bullen:

If you number your lines, the Erl function will give the number of the
line:

Sub Test()

On Error Goto Err_Handler

1: For i = 10 To 0 Step -1
2: Debug.Print 1/i
3: Next

Exit Sub

Err_Handler:

Debug.Print "Error in line " & Erl

End Sub

I wrote a routine some time ago to automatically add line numbers to all
my code, prior to shipping it, but it's not what I'd call release-quality
<g

Regards

Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLTD.ie

--
Regards,
Tom Ogilvy

"Paul Martin" wrote in message
om...
Hi all

I have written some code that captures date/time, err.Number,
err.Description, ActiveWorkbook.Name, etc and appends it to an
errorlog.txt file.

Is there some way that I can capture the actual code module and line
of code where the error occurred and write this to the file?

I'm looking at the following, but can't quite get what I'm after:
Application.VBE.VBProjects(1).VBComponents

Any help appreciated.

Paul Martin
Melbourne, Australia

Thanks for your suggestions Patrick, Frank & Tom.

I think passing the procedure name as a string parameter sounds like a
good idea. I thought there might be an application variable I could
use, but hard-coding the name (though not preferable) will do the
trick.

I'd prefer not to use line numbering, but if I go down that path, Erl
sounds like the solution. I find it odd that a search for Erl in the
VBA Help only returns Err. Erl stands for Err line?

Thanks again

Paul Martin
Melbourne, Australia


"Tom Ogilvy" wrote in message ...
Just to add:

From a previous post by Stephen Bullen:

If you number your lines, the Erl function will give the number of the
line:

Sub Test()

On Error Goto Err_Handler

1: For i = 10 To 0 Step -1
2: Debug.Print 1/i
3: Next

Exit Sub

Err_Handler:

Debug.Print "Error in line " & Erl

End Sub

I wrote a routine some time ago to automatically add line numbers to all
my code, prior to shipping it, but it's not what I'd call release-quality
<g

Regards

Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLTD.ie

--
Regards,
Tom Ogilvy

"Paul Martin" wrote in message
om...
Hi all

I have written some code that captures date/time, err.Number,
err.Description, ActiveWorkbook.Name, etc and appends it to an
errorlog.txt file.

Is there some way that I can capture the actual code module and line
of code where the error occurred and write this to the file?

I'm looking at the following, but can't quite get what I'm after:
Application.VBE.VBProjects(1).VBComponents

Any help appreciated.

Paul Martin
Melbourne, Australia