Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > error handling question
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default 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

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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default 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

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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default 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

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






Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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








Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
ADK ADK is offline
external usenet poster
  
Posts: 89
Default 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

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










Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 638
Default 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

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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Handling question J@Y Excel Programming 3 June 22nd 07 06:16 PM
Error handling question colofnature[_56_] Excel Programming 0 June 6th 06 05:38 PM
Error handling question sungen99[_114_] Excel Programming 0 June 6th 06 05:28 PM
Look up error handling question. sungen99[_104_] Excel Programming 4 May 12th 06 04:23 PM
question error handling Pierre via OfficeKB.com[_2_] Excel Programming 3 November 7th 05 09:39 PM


All times are GMT +1. The time now is 10:18 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement