Do while counter resets after returning to function

[WorkingVBA]

Hi All,

It has been about 4 years since I have done any programming and I am working on a project to parse a long description string, "~" delimeted, in a query. I am making progress and have thusfar been able to capture the substrings using the GetStr() function (all code is below) I am now trying to analyze the strings to determine if the last 5 characters are part of a zipcode. The problem is that for some reason my counter (j) in the getAddress() function is reset to 0 after I call the GetStr() function. I have no idea why this is happening though I have a feeling my long absence from the dev world is probably part of the problem. I know the code below is a bit crude, but I am looking for functional - this is a one time data conversion project.

Thanks I super appreciate any help

Sub Test()
Dim vTestString As String
    vTestString = "Blablablabla~600 Blablabla Ave,~City STC 29954~~Area takeoff for Blablabla; Blablabla; and 2nd Blablabla~Save original Blablabla, add in new Blablabla to Blablabla and Blablabla for Blablabla.~~PATH: M:\Blablabla\Blablabla"
    ' Call function GetAddress with test String
    GetAddress (vTestString)
End Sub
'========================================

Function GetAddress(vStr As String) As String
Dim j As Integer
Dim vEnd As Integer
Dim vTestStr As String
Dim vTestVal As Double
Dim vReturnStr As String
    j = 1
    vEnd = 3 'CntChar(vString, "~")
    vTestStr = ""
    vTestVal = 0
    vReturnStr = ""

    Do While j <= vEnd
        vTestStr = Trim(GetStr(j, vStr))
        vTestVal = Val(vTestStr)
        If vTestVal >= 10000 And vTestVal <= 99999 Then
            vReturnStr = vTestStr
        End If
        j = j + 1
    Loop
    GetAddress = vReturnStr

End Function
'========================================

Function GetStr(vLoc As Integer, vString As String) As String
Dim i As Integer
Dim vCurLoc As Integer
Dim vStrLen As Integer
Dim vChar As String
Dim vLastChar As String
Dim vSrchStr As String
Dim vReturnStr As String

    i = 1
    vCurLoc = 0
    vStrLen = Len(vString)
    vChar = ""
    vLastChar = ""
    vReturnStr = ""
    vSrchStr = "~"
    vLoc = vLoc - 1
    
    Do While i <= vStrLen
        vChar = Mid(vString, i, 1)
        If vCurLoc = vLoc And vChar <> vSrchStr Then
            vReturnStr = vReturnStr & vChar
        End If
        If vChar = vSrchStr And vLastChar <> vSrchStr Then
            vCurLoc = vCurLoc + 1
        End If
        vLastChar = vChar
        
        i = i + 1
    Loop
    GetStr = vReturnStr
End Function
'========================================

 

[essaytee]

You are passing into the function 'j' and in that function, 'j' now known as 'vLoc', is modified, hence the change in value.

What is happening, you are passing the argument by reference (the default) when in fact it should be 'By Value'.

Function GetStr(ByVal vLoc as Integer, vString as String) as String

Further here:

 

[The_Doc_Man]

Interesting. First, I agree with essaytee that you are probably looking at a side effect related to passage ByRef when you wanted ByVal.

But second, if you looked this up, you would find a VB (not VBA) article that says that ByVal is the default. In the link, look at the last sentence under "Determination of the Passing Mechanism." Which is misleading.

https://docs.microsoft.com/en-us/do...s/passing-arguments-by-value-and-by-reference

If you press a little farther, you get an article like the next link that says the default is ByRef for VBA. So if you looked this up, you might have gotten bad info because (apparently) VB and VBA have different answers for the same exact syntax element - a formal call parameter default.

https://access-excel.tips/vba-difference-between-byref-byval/

 

[essaytee]

Sub Test1()
    Dim j As Integer
    Dim x As Integer
    j = 20
    Debug.Print "Sub Procedure Test1()"
    Debug.Print "     j = " & str(j)
    Debug.Print "     Now call Test2() function"
    x = Test2(j)
    Debug.Print "Return to Sub Procedure Test1"
    Debug.Print "     j = " & str(j)
End Sub

Function Test2(y As Integer) As Integer
    Debug.Print "Test1() function"
    Debug.Print "     y = " & str(y)
    y = y * 10
    Debug.Print "     y = " & str(y)
    Test2 = y
End Function

In the above 'y' is passed ByRef and the result is:

Sub Procedure Test1()
     j =  20
     Now call Test2() function
Test1() function
     y =  20
     y =  200
Return to Sub Procedure Test1
     j =  200

Simply changing:

Test2(ByVal y as Integer) as Integer

- The result is:

Sub Procedure Test1()
     j =  20
     Now call Test2() function
Test1() function
     y =  20
     y =  200
Return to Sub Procedure Test1
     j =  20

@The_Doc_Man interesting regarding the docs.

 

[MajP]

Also as I explained in a recent thread you can force ByVal by using extra bracketing in your call

https://docs.microsoft.com/en-us/do...ow-to-force-an-argument-to-be-passed-by-value

Sub Test1()
    Dim j As Integer
    Dim x As Integer
    j = 20
    Debug.Print "Sub Procedure Test1()"
    Debug.Print "     j = " & Str(j)
    Debug.Print "     Now call Test2() function"
    x = Test2((j))  'This forces by val 
    Debug.Print "Return to Sub Procedure Test1"
    Debug.Print "     j = " & Str(j)
End Sub

 

[June7]

Using ByVal prevents endless loop and procedure runs but nothing is output anywhere. Running the Sub calls function and ends. No output. I tried calling GetAddress from immediate window and that errors "ByRef argument type mismatch".

If ~ character can be depended on as a delimiter and not otherwise used within string, could use Split() function and an array to parse those major parts of string. Is zip code always in the third part? Always 8 parts in string?

With the provided sample string, stated goal can be accomplished in about 10 lines of code. Consider:

Sub test1()
Dim aryS As Variant
Dim strS As String
Dim x As Integer
strS = "Blablablabla~600 Blablabla Ave,~City STC 29954~~Area takeoff for Blablabla; Blablabla; and 2nd Blablabla~Save original Blablabla, add in new Blablabla to Blablabla and Blablabla for Blablabla.~~PATH: M:\Blablabla\Blablabla"
aryS = Split(strS, "~")
For x = 0 To UBound(aryS)
    Debug.Print "String part has zip code: " & IsNumeric(Right(aryS(x), 5))
Next
End Sub

 

[WorkingVBA]

Thank you all for putting me back on the right path. All the replies were really helpful and I enjoyed reading all of the additional information. I was so used to my sloppy technique that I hadn't even considered using ByVal. Thanks to the The_Doc_Man for the gentle ribbing about not pressing hard enough, and I admit I could have put in a little more effort, though in my defense I wasn't sure what I was looking for. I certainly wasn't looking for ByVal. You guys are great and I really appreciate all the feedback. Thanks

Function GetStr([B]ByVal[/B] vLoc As Integer, vString As String) As String
Dim i As Integer
Dim vCurLoc As Integer
Dim vStrLen As Integer
Dim vChar As String
Dim vLastChar As String
Dim vSrchStr As String
Dim vReturnStr As String

    i = 1
    vCurLoc = 0
    vStrLen = Len(vString)
    vChar = ""
    vLastChar = ""
    vReturnStr = ""
    vSrchStr = "~"
    vLoc = vLoc - 1
    
    Do While i <= vStrLen
        vChar = Mid(vString, i, 1)
        If vCurLoc = vLoc And vChar <> vSrchStr Then
            vReturnStr = vReturnStr & vChar
        End If
        If vChar = vSrchStr And vLastChar <> vSrchStr Then
            vCurLoc = vCurLoc + 1
        End If
        vLastChar = vChar
        
        i = i + 1
    Loop
    GetStr = vReturnStr
End Function

 

[The_Doc_Man]

though in my defense I wasn't sure what I was looking for.

You know, that happens to us experienced folks too. No need to defend what is actually quite commonplace. Glad to be of assistance.