Excel Hints

Results 1 to 8 of 8

Thread: Call Sub 'GoTo' Point?

  1. #1

    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 Application Development Services
    www.topexcelclass.com

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,094
    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
    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
    [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 Application Development Services
    www.topexcelclass.com

  7. #7
    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 xld's Avatar
    Joined
    Apr 2005
    Posts
    24,094
    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
  •