Better method than GoTo for Guard Clause at the start of a loop?

[Pat Hartman]

I imagine they are right

Why? That would make me wrong? It's like not using your hammer because someone once dropped one on their toe and broke their toe. Like any other tool, if you know what it is for and how to use it, it allows you to use the best tool for the situation.

GoSub's allow you to break up a procedure for readability and in many cases to actually simplify and eliminate deeply nested if's.

How many levels to you nest If's? The answer is - as many levels as the logic dictates. GoSub's help with that.

For COBOL people, If there are any left, GoSub = Perform

I would be interested in why some experts avoid them also. Maybe because they have the word "Go" in them. GoTo in COBOL and other languages is how people create spaghetti code. GoSub is like a call to a sub but the code is within the calling sub so it shares all the variables.

Personally, I HATE having to use GoTo to handle errors. It is the absolute worst thing about VBA.

 

[Josef P.]

I think that calling another sub has the advantage that it is separately testable and a manageable code is created.
Of course, if you can create a very long procedure that never needs to be adjusted later, I don't want to prevent you from doing so.


I will adapt the suggestion from #7 a little bit:

Baseline:

Function Func1()

   Dim Var3 As Long

   Do Until DCount("field", "table", "done = false") = 0
      Var3 = DLookup("field", "table", "done = false")
      DoSometingWithField Var3
   Loop

   MsgBox "Fin", vbSystemModal

End Function

Replace DCount+DLookup with Recordset:

Function Func1()

   with CurrentDb.OpenRecordset("select field from table where done = false")
      Do while not .eof
          DoSometingWithField .Fields(0).Value
          .movenext
       Loop
   End with

   MsgBox "Fin", vbSystemModal

End Function

I see the possible optimizations better with such a structure.
Of course you can also adapt the code at Goto & co. But the more lines I have to read coherently, the less I see the potentials.

 

[NauticalGent]

Why? That would make me wrong?

No, that is not what I was implying

 

[NauticalGent]

it allows you to use the best tool for the situation.

This is what I am trying to figure out...

 

[Pat Hartman]

I think that calling another sub has the advantage that it is separately testable and a manageable code is created.

Calling a separate procedure is absolutely correct and even necessary when the called procedure stands alone or is referenced by multiple other procedures. The choice of using a GoSub is specifically limited to code that would otherwise have to be embedded in a nested If or a loop. That is not the case with using a separate procedure or function.

This is a case of using the right tool for the job.

Whether you have to change code later has nothing to do with the "choice" of a GoSub or a procedure.

Funny, I don't even see this as a choice. I ALWAYS would use a separate procedure if the code were to be used from different procedures and I would NEVER use a separate procedure if the code were to be used within only one procedure. AND, in the couple of situations where I have decided later that I wanted to reuse the code, separating the GoSub to a separate procedure was trivial.

Joseph, the point I think you might have missed was that the GoSub code was part of the procedure that contained it. It was not separate multi-use code. GoSubs are used to simplify Loops and Nested If's. There is no other real use although I'm sure that some people might break up a large procedure just because it was large.

 

[InstructionWhich7142]

Thanks all, I'm pretty happy using GoTo how I plan to as basically "Continue" or "Skip to the loop"

I appreciate the separate sub/function option and I do if things repeat or are used elsewhere (as Pat says in his reply as I type this), that Criteria example looks perfectly reasonable size wise, they've done a good job of making it very dense in what it does, many tests & checks etc!

I do have one (old - so less well written) module that is basically one function split into about 8 functions called by a Case because it just got out of hand, the module is 520 lines of code, 1022 lines including blanks and comments, it was just one function, to handle a fairly complex (7 different types) barcode system, i.e. "if it's a persons barcode do A, if it's a job do B, if it's stock do C, if it's equipment do D, if it's a step in a process do E, etc etc etc. The poor module starts with 31 global variables defined, lol

This is one thing very cool about GoSub, it keeps you in the same sub/function for local variables, or recordsets (I use these a fair bit lately) which make me more hesitant to split things out and are what lead me to ask this question in the first place, I am going to be 2 or 3 loops and multiple recordsets deep and I'd like to avoid nesting if's as much as possible

 

[Josef P.]

... I would NEVER use a separate procedure if the code were to be used within only one procedure.

Do you find the long procedures clearer or compacter?

I already write the code in such a way that I create the procedure structure top down and then fill the code into the procedures bottom up.
This approach allows me to develop more complex flows using unit testing.

See: https://www.access-programmers.co.u...cedures-splitting-a-string-into-lines.327529/

 

[Pat Hartman]

Thanks all, I'm pretty happy using GoTo how I plan to as basically "Continue" or "Skip to the loop"

Discipline is the key. I would never do this but the GoTo is valid. Just be conscious of the fact that this is how the spaghetti logic starts. But, it's just one GoTo

 

[Pat Hartman]

Do you find the long procedures clearer or compacter?

The code is as long as it is whether it is one long, unbroken procedure or a procedure broken into separate subs or a long procedure organized onto GoSubs, which might even be reused.

I once had a manager who after reviewing my overall system design for 9 high level modules that might each contain several programs, ask me if I could do the project in less time if I broke it into 6 high level modules

I already write the code in such a way that I create the procedure structure top down and then fill the code into the procedures bottom up.

This is absolutely the professional way to do development. The Point is that using GoSub's can simplify the procedures because they allow the sharing of recordsets and variables that would otherwise have to be recreated and/or passed.

Do not discount using GoSubs without knowing what they are used for and how to use them. Just because you've always turned your nose up against them for reasons unknown, doesn't mean that you might not now, rethink your reticence now that an expert has told you that they are not actually evil - unlike multi-value fields

 

[Josef P.]

Do not discount using GoSubs without knowing what they are used for and how to use them.

No way, as soon as it makes the code more readable and maintainable, I would use it.
However, when I compare the code shown from #6 with #7, I don't see the advantage.

 

[Pat Hartman]

You seem to be confusing GoTo with GoSub. They are different although they both use internal labels.

GoTo = absolute transfer of execution control. Control is transferred to the specified label and control continues from that point.
GoSub = transfer execution to the sub but control returns to the next statement after the GoSub when the Return is encountered. It's a go and come right back.

If you use GoSub's, you use an "EndSub" label at the end of the main code section with an Exit Sub so the code doesn't fall into the embedded subs.

EndSub:
    Exit Sub
ProcA:
    ....
    Return
ProcB:
    .....
    Return
ProcC:
    .....
    Return
End Sub

 

[Josef P.]

Don't worry, I know VBA well enough.

 

[Pat Hartman]

Except you thought that GoSub = GoTo It's OK to not know everything. I am always amazed by the new things I learn when I read other people's answers. Feel free to ignore this entire thread. There is nothing about VBA worth learning here.

 

[The_Doc_Man]

UP side of GoSubs:
1. They exemplify my preferred methodology, "Divide and Conquer" as applied to program design. I'm not being arrogant by pushing this method because it was taught to me by my college professors and instructors, and it appeared in college programming texts as far back as the 1960s. This is one of Pat's "likes" - the ability to break up the process into smaller, more easily managed chunks.
2. The GoSub's code can see the variables and objects defined in the code from which they are called. No need to worry about passing variables.
3. They do not change contexts, which means their stack footprint is small. Specifically, they do not involve a "call frame" like a Sub or Function would involve, so they are memory friendly. Call frames for Sub or Function code that involves an array (for example) or an application object (for another, bigger example) can get quite demanding.

DOWN side of GoSubs:
1. In the Access VBA environment, they MUST be within the programming scope of the overall event code from which they are called. Unlike having a Public Sub in a general module, the target label of GoSub MUST be something that (if it weren't the entry point to a GoSub) could be the target of a GoTo. They are RIGOROUSLY concerned with transfer-of-control scope.
2. Because they can see things within the event-code scope, a lot of what they do is akin to side-effects - which are a temptation. Stated another way, all variables used in a GoSub routine are used as though they were passed by reference - which in fact, they are - even if you didn't want them to be.
3. Because they do not change contexts, error handling will be less specific since you won't know whether you were in the caller of a GoSub or in the GoSub itself. The Show Call Stack function used in code debugging won't make a distinction.

In my genealogy database, I had to use GoSub several times because of the potential for recursion in trying to establish generational differences between two people. Because it had a smaller stack footprint, it worked far better than the functions I originally used that, because of recursion, often "blew out" the program stack. ("Heap overflows stack" error. Or "Stack overflows heap", depending on which action revealed the dirty deed.)

 

[Josef P.]

In my genealogy database, I had to use GoSub several times because of the potential for recursion in trying to establish generational differences between two people. Because it had a smaller stack footprint, it worked far better than the functions I originally used that, because of recursion, often "blew out" the program stack. ("Heap overflows stack" error. Or "Stack overflows heap", depending on which action revealed the dirty deed.)

This is a good example in my opinion. GoSub has improved the process. That is a good reason to use it.

I return to the initial example, which I rearranged somewhat for comparison:

Variant with GoTo:

Public Function Func1_WithGoTo()

   Dim Var3 As Long
   Dim Var2 As Long
   Dim Var1 As Long

   With CurrentDb.OpenRecordset("select field, done from table1 where done = false", ...)
      Do While Not .EOF
         Var3 = .Fields(0).Value
         .edit
         .fields(1).Value = True
         .Update

         If DCount("field2", "table2", "field = " & Var3 & " and outstanding > 0") = 0 Then
             MsgBox "invalid Var"
             GoTo SkipLoop
         End If

         'do a whole bunch of stuff

         Do Until Var1 = Var2
         '  ...
         Loop

         .MoveNext
SkipLoop:
      Loop
      .Close
   End With

   MsgBox "Fin", vbSystemModal

End Function

Variant with GoSub:

Public Function Func1_WithGoSub()

   Dim Var3 As Long
   Dim Var2 As Long
   Dim Var1 As Long

   With CurrentDb.OpenRecordset("select field, done from table1 where done = false", ...)
      Do While Not .EOF
         Var3 = .Fields(0).Value
         .edit
         .fields(1).Value = True
         .Update
         GoSub DoSometingWithField
         .MoveNext
      Loop
      .Close
   End With

   MsgBox "Fin", vbSystemModal

Exit Function

DoSometingWithField:
   If DCount("field2", "table2", "field = " & Var3 & " and outstanding > 0") = 0 Then
       MsgBox "invalid Var"
       Return
   End If

   'do a whole bunch of stuff

   Do Until Var1 = Var2
   '  ...
   Loop

   Return

End Function

Variant with sub-procedure:

Public Function Func1_WithSubProc()

   With CurrentDb.OpenRecordset("select field, done from table1 where done = false", ...)
      Do While Not .EOF
         .edit
         .fields(1).Value = True
         .Update
         DoSometingWithField .Fields(0).Value
         .MoveNext
      Loop
      .Close
   End With

   MsgBox "Fin", vbSystemModal

End Function

Private Sub DoSometingWithField(ByVal Var3 As Long)

   Dim Var2 As Long
   Dim Var1 As Long

   If DCount("field2", "table2", "field = " & Var3 & " and outstanding > 0") = 0 Then
       MsgBox "invalid Var"
       Exit Sub
   End If

   'do a whole bunch of stuff

   Do Until Var1 = Var2
   '  ...
   Loop

End Sub

Unless I included a bug, all 3 variants should do the same thing.

Do we agree that the variant with GoTo is the worst to read?
How does it look comparing the variants with GoSub and the sub-procedure?

For this use case, I see more advantages for the last variant.

1. easier testable (you can test the sub-procedure alone)
2. no overlapping data operations in one procedure, which complicate error handling
3. separate variable declaration ... no side effects possible

What do you think?

 

[Pat Hartman]

Do we agree that the variant with GoTo is the worst to read?

No. The GoSub and called procedure are equivalent. In fact, the GoSub will always be close by and always "after" the main line but the procedure could be anywhere "before" or "after" and it could be in a separate module or even an add-in, so the GoSub is almost always better However, you can't right click on the label in the GoSub so it is less convenient in that respect than a called procedure.

Both the GoSub and called procedure reduce the complexity of reading the code. You may not even have to drop down into the lower level procedure depending on why you are even reviewing/modifying the code. AND with the separation, you are far less likely to introduce a bug so both methods are better than embedding all the code inside the nested if's and loops.

Never using GoTo's is best practice since it reduces the risk of later modifications that create the dreaded Spaghetti code. You can't avoid the GoTo's for error traps but I can't be responsible for that.

@The_Doc_Man I don't think 1 and 2 are cons. For 1, the GoSub is ALWAYS part of the procedure that contains it and is NEVER used outside of that procedure. If you need to use the code from multiple procedures, then you MUST use subs or functions. Regarding 2, sloppy developers get burned regardless of whether they use GoSub's or not.

Not all lengthy procedures benefit from the use of GoSub's. One of my earliest uses was for an application that needed to process an EDI text file. EDI = Electronic Data Interchange which is an industry wide standard for a large number of files that big corporations need to exchange to do business. Some instances are medical billing transactions and usage statements from electric/gas providers.

EDI files are very complex and can contain dozens of different record types so processing one gets complicated. However with a Case statement and GoSub's the mainline is clean and short. If you care about record type X, just look at the code in GoSub X. When I first started writing this, I was embedding the code in the case statement. That was getting ugly so I was going to make separate subs. That was also ugly because I had to pass too many variables around and even had to use global variables. GoSub to the rescue. Added a new tool to my toolbox. Now, I never make separate subs unless I actually need a separate sub because the code will be called from multiple procedures.

 

[The_Doc_Man]

@Pat Hartman - OK, stated another way...

Con #1 means that you cannot effectively put the GoSub routine in another module even if you DID want to re-use it.
Con #2 is a holdover from someone who practically slapped my knuckles with a ruler when I was careless with side-effects, and that "in-scope" situation is a case where lines are blurred. Call me paranoid. Or if I was beside myself, call me paradox.

EDITED to clarify a fine point.

 

[Pat Hartman]

You crack me up

 

[KitaYama]

@The_Doc_Man I regularly use GoSubs (when I don't have any plan to re-use them in other functions) and I don't see any of your Cons as a real con. Maybe only the memory stack one. (I don't understand that)

I see only two cons for GoSubs:
1- You can not shift+F2 a GoSub to jump to that specific section of program (Subs and functions give you this shortcut) It means you have to scroll up and down regularly.
2- You can't jump over them while debugging. If you have several subs and functions that are called in a procedure, you can shift+F8 them to execute them without stepping in. But you can not do this on GoSubs. While debugging, you have to go line by line, or put a break somewhere ahead and do a F5 to jump over them. And after a while your function is full of break points and you're lost between them.

Yet, I like them and use them as much as I can. (again, if that section is not going to be used by other functions)

 

[The_Doc_Man]

@The_Doc_Man I regularly use GoSubs (when I don't have any plan to re-use them in other functions) and I don't see any of your Cons as a real con. Maybe only the memory stack one. (I don't understand that)

I see only two cons for GoSubs:
1- You can not shift+F2 a GoSub to jump to that specific section of program (Subs and functions give you this shortcut) It means you have to scroll up and down regularly.
2- You can't jump over them while debugging. If you have several subs and functions that are called in a procedure, you can shift+F8 them to execute them without stepping in. But you can not do this on GoSubs. While debugging, you have to go line by line, or put a break somewhere ahead and do a F5 to jump over them. And after a while your function is full of break points and you're lost between them.

Yet, I like them and use them as much as I can. (again, if that section is not going to be used by other functions)

Your point #2 is one that I didn't think about, but you are quite right. A GoSub isn't a procedure call so step into/step over don't recognize it. I so rarely use your point #1 that it also eluded me.