Consulting

Results 1 to 8 of 8

Thread: Call Sub 'GoTo' Point?

  1. #1
    VBAX Regular
    Joined
    Jun 2010
    Location
    Dunstable
    Posts
    44
    Location

    Call Sub 'GoTo' Point?

    Hello again,

    Another puzzler for you.

    We have some Subs that are designed to work in a specific way and utilise GoTo points in order for them to be multifunctional, compact and clever. However, we are now realising that it would be a massive advantage if we could use these GoTo points as entry points from other subroutines.

    For example...

    Sub One()
    
    1:  MsgBox ("1")
        GoTo 5
        
    2:  MsgBox ("3")
        GoTo 4
        
    3:  MsgBox ("5")
        Exit Sub
        
    4:  MsgBox ("4")
        GoTo 3
    
    5:  MsgBox ("2")
        GoTo 2
    
    End Sub
    
    
    Sub Two()
    
        Call One
    
    End Sub
    Currently, When this is run, you get message boxes 1 to 5 in order. That's fine. But what if I wanted only to run the code in that sub for stage 3?

    Is there a way to say, 'Call One and then go immediately to 3 as your entry point.'?

    The only way we could currently think of doing it would be to pass a public variable to from sub Two to sub One saying 'If the variable is X then GoTo 3 else do nothing.' But this seems like a messy and unpleasant way of doing things.

    Thanks in advance,

    Rob.

  2. #2
    In general, using GoTo in programming is considered bad practice. It is almost always possible to restructure the code so that no goto's are needed.
    Passing a variable to the routine is the way forward (which BTW is not necesarily a public variable):
    [vba]
    Sub One(lStart As Long)
    Dim lCt As Long
    For lCt = lStart To 5
    MsgBox lCt
    Next
    End Sub
    Sub Demo()
    One 3
    End Sub
    [/vba]
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why not just split it into more subs?

    [vba]

    Sub AllFive()
    Call One
    Call Two
    Call Three
    Call Four
    Call Five
    End Sub
    Sub One()
    MsgBox ("1")
    End Sub
    Sub Three()
    MsgBox ("3")
    End Sub
    Sub Five()
    MsgBox ("5")
    End Sub
    Sub Four()
    MsgBox ("4")
    End Sub
    Sub Two()
    MsgBox ("2")
    End Sub
    Sub CallOne()
    Call One
    End Sub
    [/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    utilise GoTo points in order for them to be multifunctional, compact and clever
    I've never heard "GoTo" and "multifunctional, compact and clever" in one sentence before. I'd go with Jan and XLD and rethink the approach.

    But one way following your concept is to do something like the following:

    [vba]
    Option Explicit

    Sub Two()
    Call One(Array(1, 2, 3))
    Call One(Array(5, 4, 3))
    Call One(Array(1, 5, 2))
    End Sub


    Sub One(ComputedGoTo As Variant)
    Dim i As Long
    For i = LBound(ComputedGoTo) To UBound(ComputedGoTo)
    On ComputedGoTo(i) GoSub 1, 2, 3, 4, 5
    Next i

    Exit Sub

    1: MsgBox ("1")
    Return

    2: MsgBox ("2")
    Return

    3: MsgBox ("3")
    Return

    4: MsgBox ("4")
    Return
    5: MsgBox ("5")
    Return

    End Sub

    [/vba]

    Paul

  5. #5
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    [VBA]Sub One(Optional i As Integer)
    Select Case i
    Case 1
    GoTo 1
    Case 2
    GoTo 2
    Case 3
    GoTo 3
    Case 4
    GoTo 4
    Case 5
    GoTo 5
    End Select

    1: MsgBox ("1")
    GoTo 5

    2: MsgBox ("3")
    GoTo 4

    3: MsgBox ("5")
    Exit Sub

    4: MsgBox ("4")
    GoTo 3

    5: MsgBox ("2")
    GoTo 2

    End Sub


    Sub Two()

    One

    End Sub

    Sub Three()

    One 3

    End Sub[/VBA]

  6. #6
    OK, lets start a contest. Which of the posts in this thread contains the least intelligable code?

    Let me take this a step further: I would have happily contributed a prize to the one winning this contest: the worst Excel book I've ever seen.
    But I tossed it :-)
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    Quote Originally Posted by Stargazer
    ...We have some Subs that are designed to work in a specific way and utilise GoTo points in order for them to be multifunctional, compact and clever. ...
    Many years ago, programmers found that multi-functional, compact, clever routines were so confusing as to be almost un-editable, even by their authors.

    From that arose a concept called Structured Programming. The idea being that each bit of "atomic code" would do only one thing, perhaps with an argument being passed to it. Other routines would call these atomic routines as needed.
    Two of the big concepts of Structured Programming are
    1) The routine does only one thing.
    2) There is one way into a routine and one way out. (in VBA this translates into "End Sub" is always exicuted)

    xld's code is an example of structured programming being applied to the OP code.

    Another approch would pass the number as a vairable.

    [VBA]Sub Main()
    Call ShowMessageWithNumber(1)
    Call ShowMessageWithNumber(2)
    Call ShowMessageWithNumber(3)
    Call ShowMessageWithNumber(4)
    Call ShowMessageWithNumber(5)
    End Sub

    Sub ShowMessageWithNumber(aNumber as Long)
    MsgBox aNumber
    End Sub
    [/VBA]

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by mikerickson
    Many years ago, programmers found that multi-functional, compact, clever routines were so confusing as to be almost un-editable, even by their authors.
    Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are,
    by definition, not smart enough to debug it.
    Brian W. Kernighan.

    Quote Originally Posted by mikerickson
    xld's code is an example of structured programming being applied to the OP code.
    Thank you !
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •