VBA - Cannot return Value from within If Statement inside For Loop

Question

I have a method called "checkConvert" which is supposed to return a String.

I have created a For Loop which at the end of the loop returns a value as checkConvert. Also, inside that For Loop, I have setup a Case and inside that Case, another For loop with If Statements inside. Inside those If Statements, I have also tried to return a value as checkConvert, however it does NOT work at all.

For i = val To lastColumn_source
    For j = 1 To 3
        Dim cellValue As String
        cellValue = salesSource.Cells(j, i).Value

        Select Case i
            Case 1
                Dim match As Boolean
                match = False                'Default is false.  
                                             'Equating two cells = false.

                For x = 1 To lastRow_check
                    If cellValue = sourceCheck.Cells(x, 1) Then
                        CRMDest.Cells(j, 2) = newName
                        match = True         'Match is found.  Thus, true.
                    End If
                    If x = lastRow_check And match = False Then
                        checkConvert = newName           'Supposed to return
                                                         'value.  Doesn't
                                                         'work.
                        MsgBox checkConvert              'But MsgBox works.
                    End If
                 Next x
            Case 2                                'Several more cases follow.
            Case 13
                 checkConvert = "End of Program."        'Returns String
                                                         'to "end program."
                                                         'This one works.
        End Select

        checkConvert = "Move."                           'This return also
                                                         'works.
    Next j
Next i

Mathieu Guindon · Accepted Answer · 2016-05-18 01:54:59Z

4

    checkConvert = newName           'Supposed to return
                                     'value.  Doesn't
                                     'work.
    MsgBox checkConvert              'But MsgBox works.

It looks like you're under the impression that assigning a function's return value immediately returns. That's not how VBA works.

You've assigned a return value, but the code keeps running because, well, an assignment is just that: an assignment.

If you want to exit the function immediately after setting its return value, you need to do so explicitly:

    checkConvert = newName
    Exit Function

The return values that "work", only work out of sheer luck. You're assigning the return value inside a loop, and never jumping out of it, so the value that's returned by the function is whatever value checkConvert was last assigned to, in the last iteration.

answered May 18, 2016 at 1:54

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

1 Comment

TheBSITGuy Over a year ago

My God. Damn. That makes so much sense. I'll see what happens if I do "Exit Function." Thanks so much, Mat's Mug. Appreciate it. :)

Collectives™ on Stack Overflow

VBA - Cannot return Value from within If Statement inside For Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related