Setting variables in a recursive function (VBA)

Question

I have created a recursive function that is called within a For loop, but I'm having a problem with one chunk of the code. From the code below it hopefully is clear that there should be a scenario in which the recursive function goes "backwards" (going back to the For loop from where it was called). What I would like to have happen is to have the probInt variable not go backwards (if that makes any sense).

For example, assuming the first IF statement is FALSE when the function is first called, probInt will be set to 0 and then SOLVE will be called again. If the IF statement is TRUE the second time the function is called, then probInt will be set to 1 and then the function will be exited - returning us to the initial FOR loop. I'm finding that when I return to that initial FOR loop, probInt is set to 0 again and not 1. How can I set this up so that probInt stays at 1 even when the recursive function goes backwards?

Public probInt As Integer

Function Solve()
   If (some value in the spreadsheet) = 0 Then
       probInt = probInt + 1
       Exit Function
   End If

   For i = 0 To (some other value in the spreadsheet)
       probInt = 0
       Call Solve()
   Next i
Exit Function

Is this what you're actually doing, or have you "simplified" the situation? As written, it doesn't seem to make much sense. What purpose is served by using recursion here? Have you considered moving probInt = 0 out of the loop? — Tim Williams
– Tim Williams, Commented Jun 18, 2018 at 2:35
This is a very reduced version of my code. The real code delcares variables and does serve a purpose (unlike the code above). Would declaring variables in a certain way help me with my issue? — jrl
– jrl, Commented Jun 18, 2018 at 10:32

paul bica · Accepted Answer · 2018-06-18 03:23:22Z

Recursion is interesting, but it can become complex very quickly, and dangerous when using globals

You can think of it as a replacement for a loop, and it can be very effective in certain algorithms but in your case, as Tim mentioned, it can probably be replaced by a simple loop (your end-goal is not very clear, unless you are trying to learn)

Also, your Function should be a Sub because it doesn't return anything

1. Recursion vs Loop

Option Explicit

Public prob As Long     'Global - dangerous

Public Sub SetProb()
    Debug.Print prob    '0
    RecurseProb
    Debug.Print prob    '5

    Debug.Print prob    '5
    LoopProb
    Debug.Print prob    '0
End Sub

Public Sub RecurseProb()    'Recursion based on global variable
    prob = prob + 1
    If prob < 5 Then RecurseProb Else Exit Sub
End Sub

Public Sub LoopProb()
    While prob > 0
        prob = prob - 1
    Wend
End Sub

.

2. The danger of global variables - infinite recursion

where 2 procedures are modifying the prob global, at the same time

Public Sub SetProbInfinite()
    Debug.Print prob    '0
    RecurseProbInfinite 'Infinite loop - Run-time error 28: Out of stack space
End Sub

Public Function RecurseProbInfinite()   'Recursion based on global variable
    prob = prob + 1     'prob increments by 1
    If prob < 5 Then
        LoopProb        'prob decrements by 1
        RecurseProbInfinite
    Else
        Exit Function
    End If
End Function

.

3. Safer recursion - using parameters

Public Sub SetProbSafe()            'Recursion based on parameter
    prob = 0
    prob = RecurseProbSafeRef(prob) 'Parameter sent ByRef
    Debug.Print prob    '5

    prob = 0
    prob = RecurseProbSafeVal(prob) 'Parameter sent ByVal
    Debug.Print prob    '1
End Sub

Public Function RecurseProbSafeRef(ByRef prob As Long) As Long
    If prob < 5 Then
        prob = prob + 1
        RecurseProbSafeRef prob
    End If
    RecurseProbSafeRef = prob
End Function

Public Function RecurseProbSafeVal(ByVal prob As Long) As Long
    If prob < 5 Then
        prob = prob + 1
        RecurseProbSafeVal prob
    End If
    RecurseProbSafeVal = prob
End Function

.

Recursion in VBA can be very beneficial and effective (reduce amount of code exponentially) when dealing with binary trees, like an object model based on parent-child relationships, where we don't know where the "leafs" of the tree are (where children don't have children of their own), and we let each object determine if it has children or not

Another (surprising) fact about recursion in VBA, I found in a basic performance test, is that a For loop is faster than a recursive function

Collectives™ on Stack Overflow

Setting variables in a recursive function (VBA)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related