error handling question
 

I have numerous macros. I have originally added error handling for each one.
I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Maybe something like this:

Sub AAA()
Dim err_ As ErrObject
On Error GoTo ErrHandler

j = 1 / 0

Exit Sub
ErrHandler:
Set err_ = Err
Debug.Print err_.Number, Err.Description
MyHandler err_
End Sub

Sub MyHandler(err_ As ErrObject)
Debug.Print err_.Number, Err.Description
MsgBox err_.Number & ": " & err_.Description
End Sub


--
Regards,
Tom Ogilvy


"ADK" wrote:

I have numerous macros. I have originally added error handling for each one.
I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Err.Number will get reset when you call the error procedure. You should pass
the error number and description to your error handler proc. E.g., change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Not sure this helps. If I understand this correctly, I would place this in
each routine/macro ....which I had this already before, but trying to
eliminate the redundant code....especially if I decide to change how the log
is written ...I don't have to change it in every routine

How would I set A1 of sheet4 to Err.Number (no matter what sheet I am on)???
and
set B1 of sheet4 to Err.Description (no matter what sheet I am on)???

Then in the module I could call up the values of those cells and write them
to the log file.


"Tom Ogilvy" wrote in message
...
Maybe something like this:

Sub AAA()
Dim err_ As ErrObject
On Error GoTo ErrHandler

j = 1 / 0

Exit Sub
ErrHandler:
Set err_ = Err
Debug.Print err_.Number, Err.Description
MyHandler err_
End Sub

Sub MyHandler(err_ As ErrObject)
Debug.Print err_.Number, Err.Description
MsgBox err_.Number & ": " & err_.Description
End Sub


--
Regards,
Tom Ogilvy


"ADK" wrote:

I have numerous macros. I have originally added error handling for each
one.
I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

That worked great....THANK YOU


"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You should
pass the error number and description to your error handler proc. E.g.,
change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that the
error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You should
pass the error number and description to your error handler proc. E.g.,
change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure. So
instead of having to manually type MacroName = "Whatever" in each macro, the
code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that the
error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You should
pass the error number and description to your error handler proc. E.g.,
change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc, MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure. So
instead of having to manually type MacroName = "Whatever" in each macro,
the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that
the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You should
pass the error number and description to your error handler proc. E.g.,
change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

fixed it:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)



"ADK" wrote in message
...
I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc, MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure.
So instead of having to manually type MacroName = "Whatever" in each
macro, the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that
the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You should
pass the error number and description to your error handler proc. E.g.,
change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for each
one. I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

You're not declaring your variables, are you? If you don't declare the
MacroNam variable and you don't have "Option Explicit" at the top of your
module, VBA will create a Variant variable to hold the string and you'll get
an error passing the Variant to a String. See
http://www.cpearson.com/excel/DeclaringVariables.aspx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
fixed it:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)



"ADK" wrote in message
...
I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc, MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure.
So instead of having to manually type MacroName = "Whatever" in each
macro, the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that
the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You
should pass the error number and description to your error handler
proc. E.g., change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number and
Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for
each one. I thought maybe I could reduce my code by making a module
for the error....but the problem is when it goes to that sub, it loses
the Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2
'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

not sure what you mean by this. I have tried my changes and it works. I
errored a couple of macros to test it and the log shows what I was looking
for.

Any problems with it?



Private Sub AboutCommandButton_Click()
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MacName = "About-PDSR"
MyErrorRoutine Err.Number, Err.Description, MacName
End Sub


Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "Q:\acad\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, "Macro:" & MacName, ErrNum,
ErrDesc
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact JohnDoe (extension 123)"


End Sub






"Chip Pearson" wrote in message
...
You're not declaring your variables, are you? If you don't declare the
MacroNam variable and you don't have "Option Explicit" at the top of your
module, VBA will create a Variant variable to hold the string and you'll
get an error passing the Variant to a String. See
http://www.cpearson.com/excel/DeclaringVariables.aspx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
fixed it:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)



"ADK" wrote in message
...
I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As
String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc, MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure.
So instead of having to manually type MacroName = "Whatever" in each
macro, the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using that
the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You
should pass the error number and description to your error handler
proc. E.g., change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number
and Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for
each one. I thought maybe I could reduce my code by making a module
for the error....but the problem is when it goes to that sub, it
loses the Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) -
4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

I wrote my reply before I saw your updated code. Declaring the parameter As
Variant will work just fine.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"ADK" wrote in message
...
not sure what you mean by this. I have tried my changes and it works. I
errored a couple of macros to test it and the log shows what I was looking
for.

Any problems with it?



Private Sub AboutCommandButton_Click()
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MacName = "About-PDSR"
MyErrorRoutine Err.Number, Err.Description, MacName
End Sub


Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "Q:\acad\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, "Macro:" & MacName, ErrNum,
ErrDesc
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact JohnDoe (extension 123)"


End Sub






"Chip Pearson" wrote in message
...
You're not declaring your variables, are you? If you don't declare the
MacroNam variable and you don't have "Option Explicit" at the top of your
module, VBA will create a Variant variable to hold the string and you'll
get an error passing the Variant to a String. See
http://www.cpearson.com/excel/DeclaringVariables.aspx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
fixed it:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)



"ADK" wrote in message
...
I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As
String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc,
MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that procedure.
So instead of having to manually type MacroName = "Whatever" in each
macro, the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using
that the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You
should pass the error number and description to your error handler
proc. E.g., change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number
and Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for
each one. I thought maybe I could reduce my code by making a module
for the error....but the problem is when it goes to that sub, it
loses the Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) -
4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

Thanks for your help Chip - nice site


"Chip Pearson" wrote in message
...
I wrote my reply before I saw your updated code. Declaring the parameter As
Variant will work just fine.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"ADK" wrote in message
...
not sure what you mean by this. I have tried my changes and it works. I
errored a couple of macros to test it and the log shows what I was
looking for.

Any problems with it?



Private Sub AboutCommandButton_Click()
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MacName = "About-PDSR"
MyErrorRoutine Err.Number, Err.Description, MacName
End Sub


Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "Q:\acad\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append
As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, "Macro:" & MacName,
ErrNum, ErrDesc
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact JohnDoe (extension 123)"


End Sub






"Chip Pearson" wrote in message
...
You're not declaring your variables, are you? If you don't declare the
MacroNam variable and you don't have "Option Explicit" at the top of
your module, VBA will create a Variant variable to hold the string and
you'll get an error passing the Variant to a String. See
http://www.cpearson.com/excel/DeclaringVariables.aspx



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
fixed it:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, ByVal MacName As
Variant)



"ADK" wrote in message
...
I tried it but I receive a compile error - ByRef argument type mismatch



Private Sub AboutCommandButton_Click()
On Error GoTo addError
Workbooks.Open "xxxxxx" 'error testing line

AboutPDSR.Show
Exit Sub
addError:
MacroNam = "About"
MyErrorRoutine Err.Number, Err.Description, MacroNam
End Sub



module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroNam As
String)
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "T:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open
file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName, ErrNum, ErrDesc,
MacroNam
'; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"
End Sub



"Chip Pearson" wrote in message
...
Yes, you can certainly do that. I have code on my web site that will
automate the creation of the procedure name values. It will create a
constant in every VBA procedure specifying the name of that
procedure. So instead of having to manually type MacroName =
"Whatever" in each macro, the code will do that for you. See
http://www.cpearson.com/excel/InsertProcedureNames.aspx .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
Chip:

MyErrorRoutine Err.Number, Err.Description

I would also like to record what macro/routine the user was using
that the error occured.

Can I do something like:

Private Sub AboutCommandButton_Click()
Dim MacroName as String
MacroName = "About"
On Error GoTo addError
AboutPDSR.Show
Exit Sub
addError:
MyErrorRoutine Err.Number, Err.Description, MacroName
End Sub


and in module:

Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String, MacroName As
String)




"Chip Pearson" wrote in message
...
Err.Number will get reset when you call the error procedure. You
should pass the error number and description to your error handler
proc. E.g., change

Call MyErrorRoutine
' to
MyErrorRoutine Err.Number, Err.Description

and change

Sub MyErrorRoutine()
' to
Sub MyErrorRoutine(ErrNum As Long, ErrDesc As String)

and in that procedure use ErrNum and ErrDesc instead of Err.Number
and Err.Description


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"ADK" wrote in message
...
I have numerous macros. I have originally added error handling for
each one. I thought maybe I could reduce my code by making a module
for the error....but the problem is when it goes to that sub, it
loses the Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first
apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub


---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) -
4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For
Append As #2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2
'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension
123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------

error handling question
 

You need to assign the Err number associated with the specific sub
routine to a global/public variable and then use that value in your
error sub.
At the top of you module, declare something like Public ErrNum As Long
Then do something like this in your subs:
addError:
ErrNum = Err.Number
Call MyErrorRoutine

Then use the ErrNum variable inplace of your current Errin
MyErrorRoutine:
Error(ErrNum)

HTH

ADK wrote:
I have numerous macros. I have originally added error handling for each one.
I thought maybe I could reduce my code by making a module for the
error....but the problem is when it goes to that sub, it loses the
Error(Err); Err information ...it only returns a 0

Any ideas on how I can get this to work is it not possible?

Using Excel 2000, vba beginner

Thanks!
----------------------------------------------------------
Example of a macro in my spreadsheet (Sheet1):

Private Sub AboutCommandButton_Click()
On Error GoTo addError
'Workbooks.Open "xxxxxx" 'error testing line - remove first apostrophe
AboutPDSR.Show
Exit Sub
addError:
Call MyErrorRoutine
End Sub



---------------------------------------------------------
My Error module:

Sub MyErrorRoutine()
'Error routine for whole spreadsheet
Dim UserName As String
Dim CpuName As String
Dim WhatOffice As String
Dim MyFullName As String
Dim WorkbookName As String

UserName = Environ("USERNAME")
CpuName = Environ("COMPUTERNAME")
WhatOffice = Environ("USERDOMAIN")
MyFullName = ThisWorkbook.FullName
WorkbookName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)

Open "P:\PDSR\PDSRlogs\ErrorLog-" & WorkbookName & ".log" For Append As
#2
'Open ThisWorkbook.Path & "\ErrorLog.log" For Append As #2 'Open file
Print #2, Application.Text(Now(), "mm/dd/yyyy HH:mm") _
, UserName, CpuName, WhatOffice, MyFullName _
; Error(Err); Err 'Write data
Close #2 'Close

MsgBox "An error has occurred, contact John Doe (extension 123)"

End Sub

-------------------------------------------------------
The log file report example:

07/17/2007 08:06 doe DMPT04 BNT01
T:\PDSR_Project\PDSR.xls 0

--------------------------------------------------------