Set Error handling INSIDE error-trap
 

I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the data-file
that's being used. However, if the error is generated 'late-on' in my macro,
the data-file may already be closed, so to handle this, I am usingthe
following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

Add a new variable to your macro call fileopen and initilize it to false

fileopen = false


Then when you open the file set it to true. When you close the file set it
false again. In you error code make the following change

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear

' and close the data file
if fileopen then
DataFile.Close SaveChanges:=False
end if


"Michelle" wrote:

I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the data-file
that's being used. However, if the error is generated 'late-on' in my macro,
the data-file may already be closed, so to handle this, I am usingthe
following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

If after closing your datafile (in the "normal" flow) you 'Set DataFile =
Nothing' then you can test for that in the error handler

ie.

If Not DataFile Is Nothing then
DataFile.Close SaveChanges:=False
End If



"Michelle" wrote in message
...
I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the
data-file that's being used. However, if the error is generated 'late-on'
in my macro, the data-file may already be closed, so to handle this, I am
usingthe following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

That's great, and thank you... but can I set error handling inside an error
trap?

M

"Joel" wrote in message
...
Add a new variable to your macro call fileopen and initilize it to false

fileopen = false


Then when you open the file set it to true. When you close the file set
it
false again. In you error code make the following change

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear

' and close the data file
if fileopen then
DataFile.Close SaveChanges:=False
end if


"Michelle" wrote:

I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the
data-file
that's being used. However, if the error is generated 'late-on' in my
macro,
the data-file may already be closed, so to handle this, I am usingthe
following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr &
"and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected
or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

Thats an even better reply than the last one - thanks - but do you knowe if
I can change the way errors are handled in the error trap anyway - for
future reference.

M


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
If after closing your datafile (in the "normal" flow) you 'Set DataFile =
Nothing' then you can test for that in the error handler

ie.

If Not DataFile Is Nothing then
DataFile.Close SaveChanges:=False
End If



"Michelle" wrote in message
...
I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the
data-file that's being used. However, if the error is generated 'late-on'
in my macro, the data-file may already be closed, so to handle this, I am
usingthe following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

You should be able to change the way errors are handling inside an error
haqndler. If is very normal to use On Error GoTo 0 inside an error handler
to return error handling to a normal mode.

"Michelle" wrote:

Thats an even better reply than the last one - thanks - but do you knowe if
I can change the way errors are handled in the error trap anyway - for
future reference.

M


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
If after closing your datafile (in the "normal" flow) you 'Set DataFile =
Nothing' then you can test for that in the error handler

ie.

If Not DataFile Is Nothing then
DataFile.Close SaveChanges:=False
End If



"Michelle" wrote in message
...
I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the
data-file that's being used. However, if the error is generated 'late-on'
in my macro, the data-file may already be closed, so to handle this, I am
usingthe following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr & "and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M

Set Error handling INSIDE error-trap
 

Once in the error handler code should be 100% safe so as not to generate an
error, other than an error designed to be trapped in the calling routine.

If that can't be guaranteed use Resume myLabel where myLabel is outside the
error handler (ie above it) and starts a new error handling routine.

Another approach might be to pass code to a dedicated procedure to handle
anything that might raise an error.

Following is highly contrived to demonstrate the above

' step through with F8
Sub aaa()
On Error GoTo errH
bbb
Exit Sub
errH:
MsgBox Err.Description, , Err.Number
End Sub

Sub bbb()
On Error GoTo errH
100 a = 1 / 0

110 a = 1 / 0

cleanup:
On Error Resume Next
' restore settings
200 a = 1 / 0
On Error GoTo errH
300 a = 1 / 0
Exit Sub
errH:
If Erl 290 Then
Err.Raise 12345, _
Description:="problem in bbb line " & Erl & vbCr & _
Err.Description
ElseIf Erl = 100 Then
Err.Clear
Resume Next
ElseIf Erl = 110 Then
ccc
Resume cleanup
End If
End Sub

Sub ccc()
On Error Resume Next
a = 1 / 100 ' that's an error
End Sub

Regards,
Peter T

"Michelle" wrote in message
...
Thats an even better reply than the last one - thanks - but do you knowe
if
I can change the way errors are handled in the error trap anyway - for
future reference.

M


"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
If after closing your datafile (in the "normal" flow) you 'Set DataFile
=
Nothing' then you can test for that in the error handler

ie.

If Not DataFile Is Nothing then
DataFile.Close SaveChanges:=False
End If



"Michelle" wrote in message
...
I am having a problem with my error trap.

I want to have my error-trap simplydisplay a message and close the
data-file that's being used. However, if the error is generated
'late-on'
in my macro, the data-file may already be closed, so to handle this, I
am
usingthe following code:

'===========================
Exit Sub

ErrorTrap:
MsgBox "The Macro has encountered an unforeseen problem" & vbCr &
"and
cannot continue", vbCritical, "Error " & Err
Application.Calculation = xlCalculationAutomatic

Err.Clear
On Error Resume Next ' in case the sheets are already protected
or
data file is already closed

' and close the data file
DataFile.Close SaveChanges:=False
'===========================

but the 'On Error Resume Next ' line seems to be ignored because it
generates a standard VB error (with the Debug-box displayed). Is this
because I am in an error trap? is there a way around this?

thanks

M