Error handler

[jhnnyboz]

Hey i have a question about the On Error statement...

so my code is

[VBA]
Global off1(1 To 10) As Date
Global work1(1 To 10), enddate As Date
Global daysworked As Integer
Global daysoff As Integer
Global mont As Integer
Global startdate As String
Global days(1 To 31) As Integer
Global daysinmonth(1 To 31)
Sub calender()

On Error GoTo 1

result = MsgBox("Did you wish to fill this Date column automcatically", vbYesNo)

If result = vbYes Then

' a bunch of stuff in here
' then after the if statement i have

End If



1: MsgBox ("An error occured.")

End Sub


[/VBA]

so when i run the macro if i dont satify that first if statement then it should just close the program and not run the error message. But for some reason it always runs that error message, it seems that once it sees that code it shows the msgbox even if there has not been an error...

Please help..

[CodeNinja]

Yup, it will do that... you need to tell the program to exit... that looks like this:

[VBA]Global off1(1 To 10) As Date
Global work1(1 To 10), enddate As Date
Global daysworked As Integer
Global daysoff As Integer
Global mont As Integer
Global startdate As String
Global days(1 To 31) As Integer
Global daysinmonth(1 To 31)
Sub calender()

On Error Goto 1

result = MsgBox("Did you wish to fill this Date column automcatically", vbYesNo)

If result = vbYes Then

' a bunch of stuff in here
' then after the if statement i have

End If

exit sub

1: MsgBox ("An error occured.")

End Sub [/VBA]

[Kenneth Hobs]

Funny how some ask the same question on the same day. This is what I posted in another thread. I do as CodeNinja explained. End can also be used depending on other factors.

[vba]Sub t()
On Error Goto ErrMsg

' *********** Delete line below and add your code. *********************
MsgBox 5 / 0


Exit Sub
ErrMsg:
Dim Msg As String
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End
End Sub[/vba]

[CodeNinja]

Kenneth,
For once, I disagree with you. Believe me, I hold you in high regard as I have seen your posts and believe you to be one of the stronger programmers on this forum.

I disagree only in the sense that I would not advise using end unless the desired outcome is to abort the remainder of the program... If that is the intent, I wholly agree with you. If not, however, exit sub will continue to run the program to it's natural end. I guess it is a matter of need, but I try to be careful to differentiate between exit and end.

If jhnnyboz did not pay attention, he might put the end before the errhandler, and may exit his program prematurely... that is really all I am concerned about.

[Kenneth Hobs]

End is a valuable tool that I used sometimes but I put a disclaimer "depending" for reasons that you stated. If some cases, it is exactly what you want.

End can also be used depending on other factors.

Press F1 in or near a command word like End to get specific help.

I am just an average programmer but thanks for the flowers!

[jhnnyboz]

thanks everyone for the responses...

exit sub worked perfectly

[Paul_Hossler]

As a small point, not to disagree, but having a generic single error handler might be ok for a small program, but if there's any complexity at all a slight expansion would IMHO greatly enhance the reliability.

For example


[vba]
Option Explicit

Global off1(1 To 10) As Date
'without the As Date after work1, the 10 work1 are Variant, not Date
' nit picky I know
Global work1(1 To 10) As Date, enddate As Date
Global daysworked As Integer
Global daysoff As Integer
Global mont As Integer
Global startdate As String
Global days(1 To 31) As Integer
Global daysinmonth(1 To 31)


Sub calender()
Dim result As VbMsgBoxStyle

On Error GoTo ErrHandler

result = MsgBox("Did you wish to fill this Date column automcatically", vbYesNo)

If result = vbYes Then

mont = 13

If mont < 1 Or mont > 12 Then Err.Raise 10000, , "Month must be between 1 and 12"

' a bunch of stuff in here

If daysoff < 1 Or daysoff > 12 Then Err.Raise 10001, , "Too many days off"

' a bunch of stuff in here

If daysoff < 1 Or daysoff > 12 Then Err.Raise 10002, , "Too many days off"

' a bunch of stuff in here

If daysoff > daysworked Then Err.Raise 10003, , "I wish I had your job"

' a bunch of stuff in here

End If

Exit Sub

ErrHandler:
Select Case Err.Number
Case 10000
Call MsgBox("You need to pick a better month", vbCritical + vbOKOnly, "Error: " & Err.Description)
Case 10001
Call MsgBox("You need to pick a better day", vbCritical + vbOKOnly, "Error: " & Err.Description)
Case 10002
Call MsgBox("You need work more", vbCritical + vbOKOnly, "Error: " & Err.Description)
Case 10003
Call MsgBox("Lucky you", vbCritical + vbOKOnly, "Error: " & Err.Description)

Case Else
MsgBox ("An error occured.")
End Select

Err.Clear

End Sub
[/vba]


Within the Case xxxxx you could correct an error and Resume or Resume Next. Online help has good examples

I am just an average programmer but thanks for the flowers!

Ken is just being modest. He's much, much above average

Paul

[Bob Phillips]

Kenneth,
For once, I disagree with you. Believe me, I hold you in high regard as I have seen your posts and believe you to be one of the stronger programmers on this forum.

I disagree only in the sense that I would not advise using end unless the desired outcome is to abort the remainder of the program... If that is the intent, I wholly agree with you. If not, however, exit sub will continue to run the program to it's natural end. I guess it is a matter of need, but I try to be careful to differentiate between exit and end.

If jhnnyboz did not pay attention, he might put the end before the errhandler, and may exit his program prematurely... that is really all I am concerned about.

I agree, it is a terrible way to shutdown. It leaves any 'temporary' changes, does not reset application settings and so on. It is far too easy to get used to using it and not thinking about the consequences.