error handling question

[ADK]

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

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

 

[Tom Ogilvy]

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

 

[Chip Pearson]

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]

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.

 

[ADK]

That worked great....THANK YOU

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]

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)

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)

 

[Chip Pearson]

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)

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)

 

[ADK]

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

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]

fixed it:

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

 

[Chip Pearson]

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]

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]

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]

Thanks for your help Chip - nice site

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)

 

[JW]

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