Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
how error-trap "no cells were found error" | Excel Programming | |||
Error handling with a handling routine | Excel Programming | |||
On Error doesn't trap Application.Run error | Excel Programming | |||
Error Handler not handling error... | Excel Programming |