CODEWITHSUNDEEP
vbaloopsvariables
macu3
macu3

Reputation: 15

VBA: loop losing variable value

I've got a strange problem with a loop in VBA, as it seems to be losing the value of my variable. Any ideas why? If i delete the loop, debug.print shows "test", otherwise it's empty (unless I print the value of "dupa" inside the loop)... Seems very strange.

Function carbon_copy(indeks As String) As String

Dim emails(1 To 3) As String
Dim i As Integer
Dim dupa As String

emails(1) = "[email protected]"
emails(2) = "[email protected]"
emails(3) = "[email protected]"

i = 1
dupa = "test"

Do While emails(i) <> ""

    If i = indeks Then
         GoTo NextIteration
    End If

    dupa = dupa & ";" & emails(i)


    NextIteration:
    i = i + 1

Loop

Debug.Print dupa

carbon_copy = dupa

End Function

Upvotes: 0

Views: 502

Answers (4)

Ruvox
Ruvox

Reputation: 116

You should get a runtime error 9 since you index i will be 4 after you looped through your emails String array. As soon as it tries to compare the value of emails(4) with "" it should produce the "index out of range" since you have defined your Array to be only 3 elements long.

For a little clarification try this example code, it should produce the same error:

Function littleTest()
    Dim teststr(1 To 3) As String
    Dim i As Integer

    teststr(1) = "abc"
    teststr(2) = "def"
    teststr(3) = "ghi"

    i = 1

    Do While teststr(i) <> ""
        Debug.Print "i do it for the " & i & " time!"
        i = i + 1
    Loop

End Function

You have already found the solution yourself since UBound() is returning the actual length of your array which is in your case three so it will never search beyond the array.

Upvotes: 2

DisplayName
DisplayName

Reputation: 13386

this should work (explanations in comments):

Function carbon_copy(indeks As Long) As String
    Dim emails(1 To 3) As String
    Dim i As Long
    Dim dupa As String

    emails(1) = "[email protected]"
    emails(2) = "[email protected]"
    emails(3) = "[email protected]"

    i = 1
    Do While emails(i) <> ""
        If i <> indeks Then dupa = dupa & ";" & emails(i) ' update 'dupa' if current index doesn't natch passed 'indeks'
        i = i + 1
        If i > UBound(emails, 1) Then Exit Do ' be sure to exit upon exceeding 'emails()' array size
    Loop

    carbon_copy = dupa
End Function

Upvotes: 0

Comintern
Comintern

Reputation: 22185

You're indexing out of the array bounds. The condition Do While emails(i) <> "" is always true given your array, so the this fails on emails(4). Just test the array bounds and loop over that:

For i = LBound(emails) To UBound(emails)
    If emails(i) <> "" And i = indeks Then
        dupa = dupa & ";" & emails(i)
    End If
Next

Upvotes: 0

macu3
macu3

Reputation: 15

Actually, I've already solved the problem by using other loop type (For i = 1 To UBound(emails), Next i), but why the previous loop did not work is still quite mysterious to me... If anyone can explain, I'd appreciate it, as I prefer to understand things rather thank just do them correctly.

W.

Upvotes: 0

Related Questions