Go Sub, to go?

15 February 2006 by Ross McLean

I was flicking through John's recently opened Hard Core XL VBA site (well forum), theres a lot of content there, it's a nice way to store a lot of code. One thing that i had never seen before was the go sub method

Basically you goto a Lable, excute whatever code follows and finish with a return line, viz

PLAIN TEXT
VBA:
  1. Sub Test()
  2. x = Array(1, 10, 100)
  3. For i = 0 To UBound(x)
  4. If x(i)> 10 Then
  5. GoSub ValueError
  6. End If
  7. Next
  8. Exit Sub
  9. ValueError:
  10. MsgBox "the " & i & "th vlaue in the set X is more than 10."
  11. Return
  12. End Sub

Now i didn't know you could do that!!!. I think i'd still be inclined to farm it out into another sub or function, but it could be useful all the same.

Related posts:

  1. Setting the default value of a Class in VBA.
  2. Find First Lower Case Letter
  3. Geocoding in Excel using Google Maps API

Tags: ,
Categories: Code • Permalink • 4 Comments »

Comments

  1. John Skewes says:
    21 March 2006 at 3:07 am

    Hi Ross,

    Yes, I usually would do the same, but it's useful when you may have (say) lots of variables that you don't want to declare as public (but then a function would probably be the way to go) anyway, it's another choice that's always available in our bag of tricks.

    Regards,
    John :)

    Reply
  2. ross says:
    21 March 2006 at 11:29 am

    Not to mention the static var dec too John - it's a stange one, but it guess it just another tool like toy say.

    Reply
  3. John Skewes says:
    24 March 2006 at 4:49 am

    Hi Ross,

    Yes, not to mention the static vars... Gosub and Return was used in a lot of earlier programming languages, notably BASIC (the grandfather of VBA).

    You can have as many Gosub/Returns as you like in a procedure but its use has largely declined due to the propensity for novice programmers to use it in a loose unstructered manner, with GoSubs and Returns popping up everywhere in the main body of code. It then becomes difficult to follow the flow of the code.

    Never-the-less, it can be handy occasionally as long as it's kept structured and not 'overdone'. I've updated the example on my site to show the difference, here it is (I don't know if the code tags will show properly here)

    ---------------------------------------------------------------------------------

    A simple example use of Gosub and Return...

    PLAIN TEXT
    VBA:
    1. Sub CopyRows_UseGosub()
    2.  
    3.       Dim Cell As Range, TargetSheet As String
    4.  
    5.       With Sheets("Sheet1")
    6.             For Each Cell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    7.                   Select Case Cell.Offset(0, 4)
    8.                   Case "Match"
    9.                         TargetSheet = "Sheet2": GoSub DoCopy
    10.                   Case "No Match"
    11.                         TargetSheet = "Sheet3": GoSub DoCopy
    12.                   Case "Part Match"
    13.                         TargetSheet = "Sheet4": GoSub DoCopy
    14.                   Case "Negative Match"
    15.                         TargetSheet = "Sheet5": GoSub DoCopy
    16.                   Case Else
    17.                         TargetSheet = "Sheet6": GoSub DoCopy
    18.                   End Select
    19.             Next
    20.             Exit Sub
    21.  
    22. DoCopy:
    23.             .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    24.                         Sheets(TargetSheet).Range("A" & Rows.Count) _
    25.                         .End(xlUp).Offset(1, 0)
    26.             Return
    27.  
    28.       End With
    29. End Sub

    As a comparison, the alternative code below to accomplish exactly the same task is 'wordier', more obscure for a coder to 'read', and it's difficult to ascertain the differences for each case (the only differences being the sheets name :))

    PLAIN TEXT
    VBA:
    1. Sub CopyRows_NoGosub()
    2.  
    3.       Dim Cell As Range
    4.  
    5.       With Sheets("Sheet1")
    6.             For Each Cell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    7.                   Select Case Cell.Offset(0, 4)
    8.                   Case "Match"
    9.                         .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    10.                                     Sheets("Sheet2").Range("A" & Rows.Count) _
    11.                                     .End(xlUp).Offset(1, 0)
    12.                   Case "No Match"
    13.                         .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    14.                                     Sheets("Sheet3").Range("A" & Rows.Count) _
    15.                                     .End(xlUp).Offset(1, 0)
    16.                   Case "Part Match"
    17.                         .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    18.                                     Sheets("Sheet4").Range("A" & Rows.Count) _
    19.                                     .End(xlUp).Offset(1, 0)
    20.                   Case "Negative Match"
    21.                         .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    22.                                     Sheets("Sheet5").Range("A" & Rows.Count) _
    23.                                     .End(xlUp).Offset(1, 0)
    24.                   Case Else
    25.                         .Range(Cell.Address, Cell.Offset(0, 2)).Copy _
    26.                                     Sheets("Sheet6").Range("A" & Rows.Count) _
    27.                                     .End(xlUp).Offset(1, 0)
    28.                   End Select
    29.             Next
    30.       End With
    31.      
    32. End Sub

    Reply
  4. ross says:
    27 March 2006 at 9:49 am

    thanks John, Sorry that the code is not very well displayed, when time permits i will look into it ;-)

    Reply

Submit a Comment