error problem

[Pierre via OfficeKB.com]

Hi,

i have an application with lots of sheets, forms and modules and subs.
with each error i would like the following code to be executed:

private sub error()
With Sheets("waarschuwing")
Dim c As Integer
.Visible = xlSheetVisible
For c = 1 To Sheets.Count
If Sheets(c).Name <> "waarschuwing" Then
Sheets(c).Visible = xlSheetVeryHidden
End If
Next
End With
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

however, the sub is in a module.
I would like to have the on erro goto statement go to this sub in the error
handling module.
How can i do that in VBA?
Thanks,
Pierre

 

[Guest]

First, you would need to make your sub Public, not Private, to be visible
across modules. Also, I would not name it Error() as that is a reserved word
in VBA causing potential confusion with the VBA built-in Error function. So
I will call it "MyError" here.

Then, in each function/sub where you wanted to use this as your error
handler, you could do as illustrated below:

Sub MySub()
On Error goto MyErrHandler

' Regular code for sub goes here

Exit Sub

MyErrHandler:
MyError
End Sub

Since your code closes the active workbook, I am assuming you do not need
any code to continue at that point, but if you do need to continue, put a
Resume Next statement after you call MyError:

MyErrHandler
MyError
Resume Next
End Sub

 

[Pierre via OfficeKB.com]

Thanks K.
This helps a lot !
Pierre

First, you would need to make your sub Public, not Private, to be visible
across modules. Also, I would not name it Error() as that is a reserved word
in VBA causing potential confusion with the VBA built-in Error function. So
I will call it "MyError" here.

Then, in each function/sub where you wanted to use this as your error
handler, you could do as illustrated below:

Sub MySub()
On Error goto MyErrHandler

' Regular code for sub goes here

Exit Sub

MyErrHandler:
MyError
End Sub

Since your code closes the active workbook, I am assuming you do not need
any code to continue at that point, but if you do need to continue, put a
Resume Next statement after you call MyError:

MyErrHandler
MyError
Resume Next
End Sub

Hi,

[quoted text clipped - 21 lines]

Thanks,
Pierre