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...

    VB:
    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 
    
    
    Formatting tags added by mark007
    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):
    VB:
    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 
    
    
    Formatting tags added by mark007
    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,066
    Location
    Why not just split it into more subs?

    VB:
     
    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 
    
    
    Formatting tags added by mark007
    ____________________________________________
    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:

    VB:
    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 
    
    
    Formatting tags added by mark007
    Paul

  5. #5
    VB:
    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 
    
    
    Formatting tags added by mark007

  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.

    VB:
    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 
    
    
    Formatting tags added by mark007

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    24,066
    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
  •