good gotos and bad gotos

[davegb]

I've seen here and in my reading that it's best to avoid using gotos in
code. The last program I wrote had quite a few, but after looking back
at them, they were mostly handling user input errors. I tried to cover
every possible user mistake because the macro creates a spreadsheet
used in some pretty backward places.

So my question is, are these kinds of gotos good uses? How do you know
whether it's a "good" goto or a "bad" goto? Any guidelines or
suggestions?

Thanks.

 

[Bob Phillips]

In VBA, Gotos for error handling are largely unavoidable, VBA doesn't have a
good error catch.

As to whether it is good or bad, is it well structured even allowing for
gotos, can you follow it easily, is it well commented, could a different
design remove some. Answer those questions, and you can probably say it is
as best as can be.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Mark Lincoln]

When handling user input errors you should be able to avoid GoTo
entirely. I've never had to use it for that purpose. The only GoTo
I've ever used is On Error Goto, which deals with errors thrown by VBA.

 

[Dave Peterson]

Just to add my two cents...

I find that using goto's to branch to an exit routine acceptable, too.

I think that that kind of thing can actually make the code easier to
understand. But except for error handling and application.goto <bg>, those are
the only ones I use.



application.screenupdating = false
'some other setup

if somethingiswrong then
goto ExitNow:
end if

'some other stuff

if somethingelseiswrong then
goto exitnow:
end if

'some more junk

exitnow:
application.screenupdating = true
'reset other stuff, calculation, activewindow.view...

exit sub

 

[davegb]

Thanks to everybody. Quite a variety of responses. Especially from
Mark!

"When handling user input errors you should be able to avoid GoTo
entirely. I've never had to use it for that purpose. The only GoTo
I've ever used is On Error Goto, which deals with errors thrown by VBA.
"

I'd be very curious to see how you can trap user errors without using
GoTo at all, without having the same code repeated in several places.
Any tips on how to do this, Mark?

 

[Guest]

I beleive what Mark is refering to is data validation. That is taking what
the user has input and validating that it is what you expect it to be (ie. a
numeric value between x and y, or a date, or...).

I am with Mark on this. All too often I see people using the error handler
inappropriatly. The error handler is like the Bat Chute. It should only be
used in case of real emergency. It is your last line of defence to handle
thing that you can not readily anticipate.
--
HTH...

Jim Thomlinson

Thanks to everybody. Quite a variety of responses. Especially from
Mark!

"When handling user input errors you should be able to avoid GoTo
entirely. I've never had to use it for that purpose. The only GoTo
I've ever used is On Error Goto, which deals with errors thrown by VBA.
"

I'd be very curious to see how you can trap user errors without using
GoTo at all, without having the same code repeated in several places.
Any tips on how to do this, Mark?

 

[Bob Phillips]

Don't fully agree with that Jim. It can be useful for instance to test for
the existence of a workbook, worksheet, or whatever, and take appropriate
action. Not an emergency, let alone a real one.

Bob

I beleive what Mark is refering to is data validation. That is taking what
the user has input and validating that it is what you expect it to be (ie. a
numeric value between x and y, or a date, or...).

I am with Mark on this. All too often I see people using the error handler
inappropriatly. The error handler is like the Bat Chute. It should only be
used in case of real emergency. It is your last line of defence to handle
thing that you can not readily anticipate.

 

[Guest]

There are exceptions to every rule. I use that myself quite often. But in
general I see a lot of overuse of the error handler. Instead of writing a
validation routine or proper well structured code the coder just lets the
error handler pick up the problem. IMO that is a bad use of the error
handler. The error handler is a poor substitue for solid code. Error's have a
fair bit of overhead and once in the error handler any subsequent errors will
cause a real problem.

The error handler is a very powerful tool and with great power comes great
responsibility. If you over use the error handler it will come back to haunt
you at some point. Before you head into an error handler ask yourself if
there is a better way. Sometimes the anwer is no.

All of this being said EVERY sub or function that I write for production
purposes has an error handler built in. No matter how good your code is
something can always go wrong... The error handler allows you to exit from
the error gracefully and reset your system settings (if required)...

It is the same with global variables and variants and volatile functions and
.... They are very handy and very powerful. So long as the power is used
effectively and judisciously then more power to you...

 

[davegb]

Most of your replies make sense to me. The main reason I used so many
GoTos in this code is that the userform asks for 2 pieces of
information, and has 3 command buttons as to what to do next. I put in
error handling for any combination I could think of for someone using
it, including things like putting in no data and clicking a button, or
putting in once piece of data but not the other and proceding. So most
of the GoTos are for handling situations like entering no input and
clicking on the "Finish" button when there's nothing to finish.

I'm still very curious as to how Mark handles this without using GoTos.
You still out there, Mark?

 

[bplumhoff]

Hello,

Goto's can make sense when implementing a finite state machine:
http://groups.google.co.uk/group/mi...=bplumhoff+goto&rnum=2&hl=en#925ef1f7dec87f8c

See http://www.ics.uci.edu/~eppstein/161/960222.html and
http://en.wikipedia.org/wiki/Finite_state_machine for further info.

HTH,
Bernd

 

[M. Authement]

In your FinishButton_Click event procedure add something like this:

If TextBox1.Value = "" Then
MsgBox "Must include value in TextBox1"
Exit Sub
End If

The Exit Sub will exit the FinishButton_Click procedure and return the user
to the userform and allow them another chance to add/correct an entry then
click the finish button again.

You will obviously have to adjust for the actual object names and the data
validation you require.

 

[Mark Lincoln]

Yup, I'm still here. The usual question is whether I'm "all there."

Given your example, I'd do something like the following.

Dim ItsCool as Boolean

Private Sub CommandButton1_Click()
CheckEntries(1)
If ItsCool then
'Do some stuff
End If
End Sub

Private Sub CommandButton2_Click()
CheckEntries(2)
If ItsCool then
'Do some other stuff
End If
End Sub

Private Sub CommandButton3_Click()
CheckEntries(3)
If ItsCool then
'Do entirely different stuff
End If
End Sub

Private Sub CheckEntries(WhichButton as Integer)
ItsCool = True
If (TextBox1 = "" or TextBox2 = "") then ItsCool = False
If ItsCool then
Select Case WhichButton
Case 1
'make any specific tests needed to verify
'entries are correct for this button;
'set ItsCool to False if anything's wrong
Case 2
'make specific tests for button 2
'set ItsCool to False if anything's wrong
Case 3
'you know the drill by now
End Select
End If
If Not ItsCool Then
'Tell the user what's wrong
End If
End Sub

All your tests for proper information are in a single Sub, keeping you
from having to deal with duplicate code for each command button. (Very
nice if you need to change something in your validation.) It is
assumed here that the command button code closes the form. And not a
GoTo in sight!

You could validate data in a textbox using its Exit event. The
following pseudocode forces the user to enter valid information before
proceeding. But possibly not useful in this case if the data needed is
different for each command button.

Private Sub TextBox1_Exit(ByVal Cancel as MSForms.ReturnBoolean)
Dim AOK as Boolean
'do your testing here - set AOK to False if any problems
'are found and display a MsgBox to alert the user
If Not AOK then Cancel = True 'focus remains in TextBox1
End Sub

I hope this helps.

 

[Guest]

Exactly my point. Very nice. Instead of dealing with invalid input data,
validate the input on entry. If it is wrong then deal with it without
resorting to an error handler after the fact. The error handler serves a
specific purpose. It is not there as a shortcut to allow you to write sloppy
code...

 

[davegb]

Thanks for your reply! And yes, it helps, as have all the replies.
Your sample code below makes sense. I see what you're saying, and it
looks like a better way to go. Now I wish I had time to re-write all
the code I just wrote. I'm glad I asked the question.
At least I'll know how to do it better in the future.

 

[Mark Lincoln]

I'd rewrite a lot of code if I had the time!

Glad to be able to help.