GoTo Labels (Is it "bad" practice)?

[Rudi]

I recall reading in a VB forum that it is "bad" practice to use excessive GoTo statements!
Is it realy BAD?

I only have two in this macro... Is it acceptable to do it this way, or should I look at a difference code flow?
TX

Code: Select all

Private Sub CleanData()
Dim lLR As Long
Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "Report" Then
            lLR = ws.Range("A1000000").End(xlUp).Row
            If lLR = 5 Then GoTo wsDel
            ws.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
            ws.Range("A6:A" & lLR).FormulaR1C1 = _
            "=IF(OR(ISNUMBER(RC[1]),IFERROR(SEARCH(""-"",RC[1]),0)>0,LEN(RC[1])=2),NA())"
            ws.Range("A6:A" & lLR).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
            ws.Columns("I:K").ColumnWidth = 15
            ws.Range("A1").EntireColumn.Delete
wsNext:
        End If
    Next ws
    Exit Sub
wsDel:
    Application.DisplayAlerts = False
    ws.Delete
    Application.DisplayAlerts = True
    GoTo wsNext
End Sub

[John Gray]

Yes, using GoTo is wicked beyond measure!

See Edsger Dijkstra's 1968 paper GOTO statement considered harmful.

And here is Verity Stob's Tribute to the great man on his death.

[Rudi]

TX for the links John.

It brings to mind what I read on some VB forum a while back. The discussion was to do with the erratic "jumpy" nature that a GoTo statement brings to code and the illogical flow it creates...making it difficult to step through and error handle.
Granted, mine is only two jumps, but I'd rather NOT use them if I could.

I like the Tribute...that poem is quite fitting.

[John Gray]

Of course Dijkstra was assuming the existence of a properly-structured language! I'm not sure that Visual Basic can be so classed...

[HansV]

As far as I can tell, you could avoid the use of GoTo by adding an extra If ... Else ... End If:

Code: Select all

Private Sub CleanData()
    Dim lLR As Long
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name <> "Report" Then
            lLR = ws.Range("A1000000").End(xlUp).Row
            If lLR = 5 Then
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            Else
                ws.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
                ws.Range("A6:A" & lLR).FormulaR1C1 = _
                "=IF(OR(ISNUMBER(RC[1]),IFERROR(SEARCH(""-"",RC[1]),0)>0,LEN(RC[1])=2),NA())"
                ws.Range("A6:A" & lLR).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
                ws.Columns("I:K").ColumnWidth = 15
                ws.Range("A1").EntireColumn.Delete
            End If
        End If
    Next ws
End Sub

[Rudi]

The resolve looks so simple...
Just tested and it works with this structure too. TX

[Jay Freedman]

Just to throw another opinion on the heap: (a) Not all GoTo statements are "really BAD", and (b) they're more necessary in some languages than in others.

The classic reconsideration of Dijkstra's paper is http://david.tribble.com/text/goto.html" onclick="window.open(this.href);return false;.

Once in a great while, limited and judicious use of a GoTo will result in simpler code than would the gyrations needed to eliminate it.

In languages that support the try/catch mechanism (such as C# and PowerShell, but not VBA), you can nearly always eliminate the use of GoTo. In VBA and VB, there are often alternatives to GoTo, such as the Exit For and Exit Do statements, or calling a function rather than putting the special handling in-line.

Error-handling, especially when you don't know in advance all of the possible errors, is a common use of GoTo in VBA.

[HansV]

I frequently use On Error GoTo <label> in VBA to set an error handler.

There have been occasions on which I resorted to a plain GoTo <label> statement, but they were few and far between. I try to avoid GoTo except for error handling, and there are usually better alternatives, but it's not a sin to use it!

[Rudi]

TX for that comprehensive link Jay, and your and Hans's comments.
Based on everyone's comments, I can see that my path of learning in VB(A) has been a good one. I have been aware that GoTo is not a sin, but to be used wisely and with limit if one wants to create one directional, optimized and expert code. The sample I posted is actually a bit of "laziness" rearing its head. I was rushing through the code and did not put thought into its decision making. If one is not careful, its all to easy just to fall back in GoTo <here> and GoTo <there> instead of a solid one directional and well designed flow!

Cheers for all the comments and inserts.

[BobH]

My programmer training is antedeluvian - going to the mid 1960's. I practiced that craft for a relatively short while before moving to the role of project manager and systems designer then into IT management. All of this was in the world of large, expensive mainframes and very expensive programmers (because the discipline of computer science in schools was not yet a practical curriculum but a theoretical one).

As a manager who had to deal with constant code revisions to keep up with business demands, and struggling with high programmer turnover rates due to market demands; we did out best to establish standards for our shop that we could manage to. Our primary languages were COBOL and BAL (Basic Assembler Language) on IBM mainframes. We established that the only acceptable use of a GOTO statement in COBOL was to point conditionally to an error message or the end of the execution of the program. Further, we established rules for documenting the conditions extant when the conditions invoked either 'destination'.

The science has progressed a great deal in the intervening 50+ years.

[Rudi]

You have a great memory for details Bob. You are referring to details from years ago.
If I think back to the early days of computing in my time, I barely remember using DOS, Windows 3.1 and Office 3.0!

797px-Office_4.0_Suite.jpg

[BobH]

Howdy, Rudi!

Yes, memory is a strange thing. I can remember details going back to the mid '40s, but I can't remember what I had for lunch yesterday.

This is the reason why: My Head Hurts

[ChrisGreaves]

I recall reading in a VB forum that it is "bad" practice to use excessive GoTo statements!
Is it really BAD?

If I were setting up a programming team today, say anywhere from three to twenty programmers, I’d make it a rule that anyone who wanted to code a GoTo or Branch – conditional or unconditional – would have to make an argument for it in front of the team at the weekly technical meeting.

I wouldn’t ban the use of GoTo outright, but I’d make sure that:-
(a) The entire team knew who was contemplating using a GoTo
(b) The entire team knew where they might find a GoTo
(c) The programmer knew that their code was up for rigorous peer-review
(d) We all were educated in alternatives to the use of the GoTo

(e) We learned, occasionally, that there were times when the GoTo was justified.
The rarity of these times would be sufficient justification for a lunchtime trip to the pub at my expense.

[Rudi]

I agree with your standards Chris. GoTo is acceptable but not advisable or recommended.
Now...when can we GoTo the pub!?

[HansV]

Code: Select all

If Time >= #17:00:00# Then
    GoTo Pub
End If

[Rudi]

Code: Select all

If Time >= #17:00:00# Then
    GoTo Pub
End If

Oh yeah! That is an acceptable time to use it!
I like END If....it sounds so "non negotiable!"