VBA: loop losing variable value

Question

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

Does it work if you add an emails(4) with a value of "" (and change to Dim emails(1 To 4) As String)? — Andrew Morton
– Andrew Morton, Commented Nov 28, 2018 at 13:56
Yeah, it does indeed. All clear now, stupid mistake, thanks a lot :) — macu3
– macu3, Commented Nov 28, 2018 at 14:06
Do you have On Error Resume Next somewhere in the code? Ignoring an error with that (such as trying to access an array element which doesn't exist) could give unexpected results. — Andrew Morton
– Andrew Morton, Commented Nov 28, 2018 at 14:10

Ruvox · Accepted Answer · 2018-11-28 14:09:51Z

2

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.

edited Nov 28, 2018 at 14:09

answered Nov 28, 2018 at 14:06

Ruvox

1161 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ruvox Over a year ago

Yes, that's what it should do. He has already found the actual solution himself but by giving him an example of a little more simple code which results in the same error I hoped to make it easier for him to understand what his error was about.

Comintern · Accepted Answer · 2018-11-28 14:07:12Z

0

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

answered Nov 28, 2018 at 14:07

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

2 Comments

DisplayName Over a year ago

Either Do While emails(i) <> "" is always true, and then it's an useless check, or it is not and then the loop doesn't exit upon first empty string (as OP's intent seems to be).

Comintern Over a year ago

@DisplayName Both true. I didn't attempt to match what the OP intended to do, because I have no clue what the intended output of the function was. This was simply showing the loop with the While test inside.

macu3 · Accepted Answer · 2018-11-28 14:04:09Z

0

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.

answered Nov 28, 2018 at 14:04

macu3

153 bronze badges

Comments

DisplayName · Accepted Answer · 2018-11-28 14:07:40Z

0

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

answered Nov 28, 2018 at 14:07

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

VBA: loop losing variable value

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related