What's So Bad About Using GoTo 6

[alr0]

Hi All,

As long as I can remember, GoTo was considered bad coding and was to be avoided. I have used them whenever the circumstances seemed to call for it with no known problems.

When stepping through a recordset, once a record has all of the processing needed I may be skipping 20 to 50 lines of code by jumping to the end of the loop and going on to the next record. This seems much faster than setting a variable to cancel and testing several times in code.

Is this old advice that can be safely ignored or have I just been lucky. Does anyone know the origins of this advice?

Thanks,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[Golom]

Used sparingly GoTo is OK but have a look at faq700-1764 to see how convoluted GoTo code can be (and that's only a very mild example.)

 

[JoeAtWork]

GoTo got its bad reputation from all the bad old "spaghetti" code where it was used to jump forward and backward all over the place, and it became impossible to follow the logic of such code. However, it has its place when used properly.

I use GoTo to "fast forward" when some condition is met that doesn't require the code in between to be processed. My rule is it's OK to jump forward, but never back.

And of course, in VBA if you want proper error handling it is pretty much impossible to avoid using GoTos.

 

[TheAceMan1]

How are ya alr0 . . .

The only reason to use [blue]Goto[/blue] is because [blue]you know nothing else to fit the bill![/blue] With experience you'll learn to program without it!

Although it was powerful in the early days of basic, learned programmers found ways to do without. At my present experience level there's no way I could ever find it necessary . . . Its just too easy to program around it and in a more exacting manner . . . Give me a secnaro whre you use it . . . and I'll give you a scenario where its not needed . . . You need to take a step up and see how its never needed! . . .

Provide some code and watch me return the magic! . . .

See Ya! . . . . . .

 

[ogri]

I can think of one time when I had to use a goto (and that was in PL/1 which has limited error handling, jumping out of an error handling routine for certain expected errors).

In VB based code, just embed the code in a large if...then...end if . If that is unreadable then hive some of the code off into functions / subroutines to perform particular tasks.

That said, long ago using Goto was more efficient (and the efficiency was important). For example using Cobal under CICS you did not have inline loops but performed functions multiple times. Which ran in small blocks. Performing a function 10 times would result in the computer dropping and reloading the peform and the function each multiple times. Similarly when you have a mainframe dump it is rather easier to sort through when there are not multiple functions, hence setting a label variable / altered goto to the return label and using goto to call the routine.

Oh well.

All the best

Keith

 

[nhtennispro]

TheAceMan1,

Here is an example of when I use "GoTo" what do you do instead.

Function Test()

On Error GoTo TestErr
.....

Exit Function
TestErr:
MsgBox Err & ". " & Error
If Err = 1111 Then
Resume
ElseIf Err = 2222 Then
Resume Next
Else
'Exit
End If
End Function

How would you rewrite the error trap?

 

[alr0]

How are you Aceman1,

I accept your invitation and the code I am working on is a good example:

I have a City, State, Zip line from an address label and need to copy it to city, state, and zip fields if it is USPS address or all to FCountry if not.

I have the line parsed into words in an array and a second array with the length of each segment.

If the last segment is of length 2, check to see if that last segment is a USPS state. If yes, copy to State field and concatenate the other fields to the city field. If not copy everything to FCountry.

Now check for arrays ending in five numbers then 2 state letters with similar assignments.

Next is from the right, four numbers, five numbers, and then 2 state letters.

Once an assignment is made in any of these or a few more cases, we are done with this record and there is a GoTo which moves execution to the MoveNext and loop commands to go to the next record.

It is certainly possible to have variable that is set to true once an assignment is made. Then, enclose each case in an IF Statement to check the variable and bypass further checks if an assignment has already been made.

However:

This means two extra lines of code must be executed (If & End If) for each test that is bypassed and further, there are two extra lines to execute before each condition is tested even before an assignment is made. If there are five tests, which is ten extra lines of code minus one if there is a GoTo that get executed per record. In a One million record table, averaging half of the tests before assignment, that comes to roughly 4.5 Million extra lines of code to be executed.

I imagine that there is some difference in the speed of execution but 4.5 Million probably does not round to zero! This seems like a legitimate use of GoTo. If there is a better way, I look forward to understanding why and learning how to do it.

Thanks (as always),

alr


_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[ogri]

Hi

One way to do it would be

Select case true
case check first line to be edited
do editing
case check 2nd line to be edited
do editing
case else
End select

Although that does land up with fairly complex case statements (although if you wanted to you could hive each check off into a function returning true or false).

First case which is edited will result in it dropping past the other case statements with no need for a goto, or an extra variable or heavily embedded if statements.

All the best

Keith

 

[Golom]

... roughly 4.5 Million extra lines of code to be executed ...

My processor runs at 2.79 GHz (not the fastest kid on the block.) Assuming that each of these 4.5 million lines of code takes 10 instruction cycles to perform, that's 45 million instruction cycles. At 2.79 billion instructions per second that should take about 0.02 seconds. That's an amount that probably couldn't be measured because things like disk latency would have a much greater variable impact on loop performance that just processor activity.

Code efficiency is important in some cases but this sort of micro-optimization at the expense of readability confers no real benefit and reduces code maintainability. You should also note that compilers are designed to optimize the usual constructs (e.g. If, Case, Do, etc.) but most of them cannot optimize code that uses arbitrary jumps in the code (i.e. GoTo statements). Your GoTo version could actually take longer than the version without it simply because the compiler can't use optimization or at least can't use it as much.

 

[TheAceMan1]

Howdy nhtennispro . . .

On Error GoTo is one form of [purple]required syntax[/purple] when using the [blue]On Error Statement[/blue]. I'm sure [blue]alr0[/blue] is describing the older more [blue]traditional sense[/blue] of goto when its said:

[blue]When stepping through a recordset, once a record has all of the processing needed [purple]I may be skipping 20 to 50 lines of code by jumping to the end of the loop[/purple] and going on to the next record.[/blue]

So . . . On Error GoTo doesn't count!

See Ya! . . . . . .

 

[alr0]

Hi Orgi,

Case is certainly useful but if three varibles are involved I am not sure it would work. Nesting them does not seem useful.

Perhaps GoTo is sometimes justified but it would depend on the execution time of finding the target and moving control. It could be much slower than other statements.

Thanks,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[TheAceMan1]

alr0 . . .

You'll have to present your goto version first. [blue]Also thats quite a hefty load of logic to prove what you say is so simple! . . .[/blue]

See Ya! . . . . . .

 

[ogri]

Hi

While goto can be justified sometimes, it is something I try to avoid. Rather too easy to code in an endless loop with careless use of constructs such as "On error goto....", and they are likely to be a nightmare to find.

As to using 3 variables with the case statement, easy enough for each case statement to be a function call returning true or false, so the check can be as complex as you like.

Main point against gotos is long term readability. CPU time is very cheap compared to the costs of employing someone to fix the code in a few years time. Company I work for is still using a billing system which was initially developed in 1970 (despite several attempts to replace it), so development costs have been swamped by ongoing support costs.

All the best

Keith

 

[alr0]

I just got this working so I apologize, it has yet to be cleaned up and I have never used the code window for display here and did not see any instructions for it.

I understand the maintainability issue and it is real. In a case where there is one statement (GoTo nextCsz) used several times it is quite clear what is going on. It is possible that I overestimated the time savings but this takes 50% more time than the alternative even now. The cleanup will yield some additional savings but I may not get to do much because of the deadlines imposed and other customer work.

I am curious what you all think.

Thanks,

alr






Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim varNewCsz As Variant
Dim varLenCsz(0 To 20) As Integer
Dim intNumComp As Integer
Dim i As Integer 'counter
Dim StrtTm
Dim EndTm

Dim rsPrsCsz As DAO.Recordset
Set rsPrsCsz = CurrentDb.OpenRecordset("SELECT WkDt.* FROM WkDt ORDER BY WkDt.IDWO;")

rsPrsCsz.MoveFirst
StrtTm = Now()

Do Until rsPrsCsz.EOF

If Not IsNull(rsPrsCsz![CSZ]) And rsPrsCsz![CSZ] <> "" Then

'varNewCsz = ParseWord("ross coms ca,94939- 1234", -2, , True, True)
varNewCsz = ParseWord(rsPrsCsz![CSZ], -2, , True, True)

'Calculate length of each csz component
intNumComp = UBound(varNewCsz)
i = 0
Do Until i = intNumComp + 1
varLenCsz(i) = Len(varNewCsz(i))
i = i + 1
Loop

If intNumComp > 0 Then


'If last 2 csz words are of length 2 and 5: check for legit state and 5 numbers
If varLenCsz(intNumComp) = 5 And varLenCsz(intNumComp - 1) = 2 Then

If varNewCsz(intNumComp) Like "#####" Then
'Debug.Print varNewCsz(intNumComp)

'If Not IsNull(DLookup("[statename]", "xstateabbrvs", "[stateabbrv] = '" & varNewCsz(intNumComp - 1) & "'")) Then
'Debug.Print varNewCsz(intNumComp - 1)

rsPrsCsz.Edit
rsPrsCsz!ZIP = varNewCsz(intNumComp)
rsPrsCsz!ST = varNewCsz(intNumComp - 1)

i = 0
For i = 0 To intNumComp - 2

rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)

Next i
rsPrsCsz.Update
GoTo nextCsz

'Else

'rsPrsCsz.Edit
'rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
'rsPrsCsz.Update
'GoTo nextCsz

'End If

Else

rsPrsCsz.Edit
rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
rsPrsCsz.Update
GoTo nextCsz


End If
End If


'If last 3 csz words are of length 2, 5, and 4: check for legit state and 5 numbers and 4 numbers
If intNumComp > 1 Then

If varLenCsz(intNumComp) = 4 And varLenCsz(intNumComp - 1) = 5 And varLenCsz(intNumComp - 2) = 2 Then

If varNewCsz(intNumComp) Like "####" And varNewCsz(intNumComp - 1) Like "#####" Then
'Debug.Print varNewCsz(intNumComp)

'If Not IsNull(DLookup("[statename]", "xstateabbrvs", "[stateabbrv] = '" & varNewCsz(intNumComp - 2) & "'")) Then
'Debug.Print varNewCsz(intNumComp - 2)

rsPrsCsz.Edit
rsPrsCsz!ZIP4 = varNewCsz(intNumComp)
rsPrsCsz!ZIP = varNewCsz(intNumComp - 1)
rsPrsCsz!ST = varNewCsz(intNumComp - 2)

i = 0
For i = 0 To intNumComp - 3

rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)

Next i
rsPrsCsz.Update
GoTo nextCsz

'Else

'rsPrsCsz.Edit
'rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
'rsPrsCsz.Update
'GoTo nextCsz


'End If

Else

rsPrsCsz.Edit
rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
rsPrsCsz.Update
GoTo nextCsz


End If

End If
End If


If intNumComp > 0 Then

'If last 2 csz words are of length 2 and 9: check for legit state and 9 numbers
If varLenCsz(intNumComp) = 9 And varLenCsz(intNumComp - 1) = 2 Then

If varNewCsz(intNumComp) Like "#########" Then
'Debug.Print varNewCsz(intNumComp)

'If Not IsNull(DLookup("[statename]", "xstateabbrvs", "[stateabbrv] = '" & varNewCsz(intNumComp - 1) & "'")) Then
'Debug.Print varNewCsz(intNumComp - 1)

rsPrsCsz.Edit
rsPrsCsz!ZIP4 = Right(varNewCsz(intNumComp), 4)
rsPrsCsz!ZIP = Left(varNewCsz(intNumComp), 5)
rsPrsCsz!ST = varNewCsz(intNumComp - 1)

i = 0
For i = 0 To intNumComp - 2

rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)

Next i
rsPrsCsz.Update
GoTo nextCsz

'Else

'rsPrsCsz.Edit
'rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
'rsPrsCsz.Update
'GoTo nextCsz

'End If

Else

rsPrsCsz.Edit
rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
rsPrsCsz.Update
GoTo nextCsz


End If
End If
End If


'If last word of csz are of length 2: check for legit state
If varLenCsz(intNumComp) = 2 Then

'If Not IsNull(DLookup("[statename]", "xstateabbrvs", "[stateabbrv] = '" & varNewCsz(intNumComp) & "'")) Then
'Debug.Print varNewCsz(intNumComp)

rsPrsCsz.Edit
rsPrsCsz!ST = varNewCsz(intNumComp)

i = 0
For i = 0 To intNumComp - 1

rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)

Next i
rsPrsCsz.Update
GoTo nextCsz

' Else
'
' rsPrsCsz.Edit
' rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
' rsPrsCsz.Update
' GoTo nextCsz

'End If

End If

End If

rsPrsCsz.Edit
rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
rsPrsCsz.Update

nextCsz:

End If

rsPrsCsz.MoveNext
Loop

EndTm = Now()
Debug.Print StrtTm & " To " & EndTm

Exit_cmdParse_Click:
Exit Sub

Err_cmdParse_Click:
MsgBox Err.Description
Resume Exit_cmdParse_Click

End Sub
Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer, Optional strDelimiter As String = " ", _
Optional bRemoveLeadingDelimiters As Boolean, Optional bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler
'Purpose: Return the iWordNum-th word from a phrase.
'Return: The word, or Null if not found.
'Arguments: varPhrase = the phrase to search.
' iWordNum = 1 for first word, 2 for second, ...
' Negative values for words form the right: -1 = last word; -2 = second last word, ...
' (Entire phrase returned if iWordNum is zero.)
' strDelimiter = the separator between words. Defaults to a space.
' bRemoveLeadingDelimiters: If True, leading delimiters are stripped.
' Otherwise the first word is returned as null.
' bIgnoreDoubleDelimiters: If true, double-spaces are treated as one space.
' Otherwise the word between spaces is returned as null.
'Author: Allen Browne.

http://allenbrowne.com.

June 2006.

Dim varArray As Variant 'The phrase is parsed into a variant array.
Dim strPhrase As String 'varPhrase converted to a string.
Dim strResult As String 'The result to be returned.
Dim lngLen As Long 'Length of the string.
Dim lngLenDelimiter As Long 'Length of the delimiter.
Dim bCancel As Boolean 'Flag to cancel this operation.
'Const CHARS = ".!?,;:""'()[]{}#"
'Const CHARS = ".!?,;:""'()[]{}#-"
Const CHARS = ",-."
Dim intIndex As Integer

'*************************************
'Validate the arguments
'*************************************
'Cancel if the phrase (a variant) is error, null, or a zero-length string.
If IsError(varPhrase) Then
bCancel = True
Else
strPhrase = Nz(varPhrase, vbNullString)
If strPhrase = vbNullString Then
bCancel = True
End If
End If
' 'If word number is zero, return the whole thing and quit processing.
' If iWordNum = 0 And Not bCancel Then
' strResult = strPhrase
' bCancel = True
' End If
' 'Delimiter cannot be zero-length.
' If Not bCancel Then
' lngLenDelimiter = Len(strDelimiter)
' If lngLenDelimiter = 0& Then
' bCancel = True
' End If
' End If

'*************************************
'Process the string
'*************************************
If Not bCancel Then
strPhrase = varPhrase
'Remove leading delimiters?
' If bRemoveLeadingDelimiters Then
' strPhrase = Nz(varPhrase, vbNullString)
' Do While Left$(strPhrase, lngLenDelimiter) = strDelimiter
' strPhrase = Mid(strPhrase, lngLenDelimiter + 1&)
' Loop
' End If

'Remove Chars
For intIndex = 1 To Len(CHARS)
strPhrase = Trim(Replace(strPhrase, _
Mid(CHARS, intIndex, 1), " "))
Next intIndex

'Ignore doubled-up delimiters?
If bIgnoreDoubleDelimiters Then
Do
lngLen = Len(strPhrase)
strPhrase = Replace(strPhrase, strDelimiter & strDelimiter, strDelimiter)
Loop Until Len(strPhrase) = lngLen
End If

'Cancel if there's no phrase left to work with
' If Len(strPhrase) = 0& Then
' bCancel = True
' End If
End If

'*************************************
'Parse the word from the string.
'*************************************
If Not bCancel Then
' varArray = Split(strPhrase)

ParseWord = Split(strPhrase)

' If UBound(varArray) >= 0 Then
' If iWordNum > 0 Then 'Positive: count words from the left.
' iWordNum = iWordNum - 1 'Adjust for zero-based array.
' If iWordNum <= UBound(varArray) Then
' strResult = varArray(iWordNum)
' End If
' Else 'Negative: count words from the right.
' iWordNum = UBound(varArray) + iWordNum + 1
' If iWordNum >= 0 Then
' strResult = varArray(iWordNum)
' End If
' End If
' End If
End If

'*************************************
'Return the result, or a null if it is a zero-length string.
'*************************************
' If strResult <> vbNullString Then
' ParseWord = strResult
' Else
' ParseWord = Null
' End If

'Added to replace with entire array
'ParseWord = varArray


Exit_Handler:
Exit Function

Err_Handler:
'Call LogError(Err.Number, Err.Description, "ParseWord()")
Resume Exit_Handler
End Function

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[JoeAtWork]

Good programming doesn't mean eliminating gotos. Methodical decomposition, refinement, and selection of control structures automatically lead to goto-free programs in most cases. Achieving gotoless code is not the aim, but the outcome, and putting the focus on avoiding gotos isn't helpful.

In other words, choose the control construct that makes the code the most readable and maintainable, and possibly the most efficient (where such efficiency is not trivial). In most cases, this will be a non-GoTo method.

But occasionally GoTo will be the most readable/maintainable option, as well as being the construct that most naturally follows the logic that is being implemented. In such a case you have a legitimate argument to use it.

 

[ogri]

Hi

Quick play and I have done this.

Have not executed it, but should work (not needed to pass complete arrays to functions before).

Option Compare Database
Option Explicit

Private Function FirstCase(intNumComp As Integer, varLenCsz As Variant) As Boolean
    If varLenCsz(intNumComp) = 5 And varLenCsz(intNumComp - 1) = 2 Then
        FirstCase = True
    Else
        FirstCase = False
    End If
End Function

Private Function SecondCase(intNumComp As Integer, varLenCsz As Variant) As Boolean
    If intNumComp > 1 Then
        If varLenCsz(intNumComp) = 4 And varLenCsz(intNumComp - 1) = 5 And varLenCsz(intNumComp - 2) = 2 Then
            SecondCase = True
        Else
            SecondCase = False
        End If
    Else
        SecondCase = False
    End If
End Function
            
Private Function ThirdCase(intNumComp As Integer, varLenCsz As Variant) As Boolean
    If varLenCsz(intNumComp) = 9 And varLenCsz(intNumComp - 1) = 2 Then
        ThirdCase = True
    Else
        ThirdCase = False
    End If
End Function
            
         
Private Function ForthCase(intNumComp As Integer, varLenCsz As Variant) As Boolean
    If varLenCsz(intNumComp) = 2 Then
        ForthCase = True
    Else
        ForthCase = False
    End If
End Function
                       

Private Sub cmdParse_Click()
On Error GoTo Err_cmdParse_Click

Dim varNewCsz As Variant
Dim varLenCsz(0 To 20) As Integer
Dim intNumComp As Integer
Dim i As Integer  'counter
Dim StrtTm
Dim EndTm

Dim rsPrsCsz As DAO.Recordset
Set rsPrsCsz = CurrentDb.OpenRecordset("SELECT WkDt.* FROM WkDt ORDER BY WkDt.IDWO;")

rsPrsCsz.MoveFirst
StrtTm = Now()

Do Until rsPrsCsz.EOF

    If Not IsNull(rsPrsCsz![CSZ]) And rsPrsCsz![CSZ] <> "" Then
    
        varNewCsz = ParseWord(rsPrsCsz![CSZ], -2, , True, True)
        
        'Calculate length of each csz component
        intNumComp = UBound(varNewCsz)
        i = 0
        Do Until i = intNumComp + 1
            varLenCsz(i) = Len(varNewCsz(i))
            i = i + 1
        Loop
        
        If intNumComp > 0 Then
    
            Select Case True
            'If last 2 csz words are of length 2 and 5: check for legit state and 5 numbers
                Case FirstCase(intNumComp, varLenCsz)
                
                    If varNewCsz(intNumComp) Like "#####" Then
                        rsPrsCsz.Edit
                        rsPrsCsz!ZIP = varNewCsz(intNumComp)
                        rsPrsCsz!ST = varNewCsz(intNumComp - 1)
                        
                        i = 0
                        For i = 0 To intNumComp - 2
                        
                            rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
                            rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)
                        
                        Next i
                        rsPrsCsz.Update
                    
                    Else
                    
                        rsPrsCsz.Edit
                        rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
                        rsPrsCsz.Update
                    
                    End If
        
        
        'If last 3 csz words are of length 2, 5, and 4: check for legit state and 5 numbers and 4 numbers
                Case SecondCase(intNumComp, varLenCsz)
                
                    If varNewCsz(intNumComp) Like "####" And varNewCsz(intNumComp - 1) Like "#####" Then
                        
                        rsPrsCsz.Edit
                        rsPrsCsz!ZIP4 = varNewCsz(intNumComp)
                        rsPrsCsz!ZIP = varNewCsz(intNumComp - 1)
                        rsPrsCsz!ST = varNewCsz(intNumComp - 2)
                        
                        i = 0
                        For i = 0 To intNumComp - 3
                        
                            rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
                            rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)
                        
                        Next i
                        rsPrsCsz.Update
                    
                    Else
                    
                        rsPrsCsz.Edit
                        rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
                        rsPrsCsz.Update
                    
                    End If
                                
        'If last 2 csz words are of length 2 and 9: check for legit state and 9 numbers
                Case ThirdCase(intNumComp, varLenCsz)
                
                    If varNewCsz(intNumComp) Like "#########" Then
                        rsPrsCsz.Edit
                        rsPrsCsz!ZIP4 = Right(varNewCsz(intNumComp), 4)
                        rsPrsCsz!ZIP = Left(varNewCsz(intNumComp), 5)
                        rsPrsCsz!ST = varNewCsz(intNumComp - 1)
                        
                        i = 0
                        For i = 0 To intNumComp - 2
                        
                            rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
                            rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)
                        
                        Next i
                        rsPrsCsz.Update
                    
                    Else
                    
                        rsPrsCsz.Edit
                        rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
                        rsPrsCsz.Update
                    
                    End If
        
            'If last word of csz are of length 2: check for legit state
                Case FirstCase(intNumComp, varLenCsz)
            
                    rsPrsCsz.Edit
                    rsPrsCsz!ST = varNewCsz(intNumComp)
                    
                    i = 0
                    For i = 0 To intNumComp - 1
                    
                        rsPrsCsz!CITY = rsPrsCsz!CITY & " " & varNewCsz(i)
                        rsPrsCsz!CITY = Trim(rsPrsCsz!CITY)
                    
                    Next i
                    rsPrsCsz.Update
            
                Case Else
        
                    rsPrsCsz.Edit
                    rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
                    rsPrsCsz.Update
                    
            End Select
        
        Else
        
            rsPrsCsz.Edit
            rsPrsCsz!FCOUNTRY = rsPrsCsz!CSZ
            rsPrsCsz.Update
            
        End If
            
nextCsz:
    
    End If

rsPrsCsz.MoveNext
Loop

EndTm = Now()
Debug.Print StrtTm & " To " & EndTm

Exit_cmdParse_Click:
    Exit Sub

Err_cmdParse_Click:
    MsgBox Err.Description
    Resume Exit_cmdParse_Click
    
End Sub
Function ParseWord(varPhrase As Variant, ByVal iWordNum As Integer, Optional strDelimiter As String = " ", _
    Optional bRemoveLeadingDelimiters As Boolean, Optional bIgnoreDoubleDelimiters As Boolean) As Variant
On Error GoTo Err_Handler
    'Purpose: Return the iWordNum-th word from a phrase.
    'Return:    The word, or Null if not found.
    'Arguments: varPhrase = the phrase to search.
    '         iWordNum = 1 for first word, 2 for second, ...
    '             Negative values for words form the right: -1 = last word; -2 = second last word, ...
    '             (Entire phrase returned if iWordNum is zero.)
    '         strDelimiter = the separator between words. Defaults to a space.
    '         bRemoveLeadingDelimiters: If True, leading delimiters are stripped.
    '             Otherwise the first word is returned as null.
    '         bIgnoreDoubleDelimiters: If true, double-spaces are treated as one space.
    '             Otherwise the word between spaces is returned as null.
    'Author:    Allen Browne. [URL unfurl="true"]http://allenbrowne.com.[/URL] June 2006.
    
    Dim varArray As Variant     'The phrase is parsed into a variant array.
    Dim strPhrase As String     'varPhrase converted to a string.
    Dim strResult As String     'The result to be returned.
    Dim lngLen As Long         'Length of the string.
    Dim lngLenDelimiter As Long 'Length of the delimiter.
    Dim bCancel As Boolean     'Flag to cancel this operation.
    Const CHARS = ",-."
    Dim intIndex As Integer
    
    '*************************************
    'Validate the arguments
    '*************************************
    'Cancel if the phrase (a variant) is error, null, or a zero-length string.
    If IsError(varPhrase) Then
        bCancel = True
    Else
        strPhrase = Nz(varPhrase, vbNullString)
        If strPhrase = vbNullString Then
            bCancel = True
        End If
    End If
                
    '*************************************
    'Process the string
    '*************************************
    If Not bCancel Then
        strPhrase = varPhrase
        
         'Remove Chars
        For intIndex = 1 To Len(CHARS)
        strPhrase = Trim(Replace(strPhrase, _
            Mid(CHARS, intIndex, 1), " "))
        Next intIndex
        
        'Ignore doubled-up delimiters?
        If bIgnoreDoubleDelimiters Then
            Do
                lngLen = Len(strPhrase)
                strPhrase = Replace(strPhrase, strDelimiter & strDelimiter, strDelimiter)
            Loop Until Len(strPhrase) = lngLen
        End If
        
    End If

    '*************************************
    'Parse the word from the string.
    '*************************************
    If Not bCancel Then

        ParseWord = Split(strPhrase)

    End If


Exit_Handler:
    Exit Function

Err_Handler:
    'Call LogError(Err.Number, Err.Description, "ParseWord()")
    Resume Exit_Handler
End Function

All the best

Keith

PS - Code tags and explanations can be seen if you click on the "Process TGML" link beside the tick box below the post reply window.

 

[alr0]

Thanks to all and especially:

Keith: the code worked the first time and it is instructive to see some other ways to combine these tools. It is a little slower than with the GoTos. I wonder in terms of speed if calling another functions isn't similar to a GoTo?

This quote that JoeAtWork supplied, addresses the original question and helped clarify the real objective.

Good programming doesn't mean eliminating gotos. Methodical decomposition, refinement, and selection of control structures automatically lead to goto-free programs in most cases. Achieving gotoless code is not the aim, but the outcome, and putting the focus on avoiding gotos isn't helpful.

...and the example Golum provided certainly makes the need clear for anyone who has had to fix others code.

I believe the simplicity of having one point at the end of a big loop when a process has ended is easy to read and understand so I left it that way. It may even be easier that having 4 other procudures and a case statement, though I am still greatful for the example.

So there is a similar version in beta now since Friday was the deadline! I hope to do some more cleanup before posting here as an FAQ.

Thanks again for everyone's input,

alr

_____________________________________
There is no rule more invariable than that we are paid for our suspicions by finding out what we expected.

Henry David Thoreau

 

[ogri]

Hi

Glad you got it sorted. I can certainly see your temptation to use a single exit point.

I suspect you are correct that a function call is not the most efficient solution, but due to the way VB evaluates IF statements it is semi forced in this situation. Some other languages will evaluate the if as they go along, and terminate the evaluation when it becomes certain to fail.

For example

b(1) = "Fred"
b(2) = "Burt"
a = null
If not isnull(a) and b(a) = "Burt"

would fail in VB but would work in other languages where the check for null would be evaluated and result in it not bothering to evaluate the check for b(a) = "Burt".

Not sure if using embedded IIF statements and dummy array members you could simulate this behavior and avoid the function call, but I would doubt it would be readable.

By the way, the seperate procedures would be more readable if I had given them meaningful names. Unfortunatly I am not familiar with (I assume) US postal addresses / zip codes.

Think the example Gollum found would do well in a code obfuscation competition....

All the best

Keith