CODEWITHSUNDEEP
vbarecursion
jrl
jrl

Reputation: 11

Setting variables in a recursive function (VBA)

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

Upvotes: 1

Views: 2103

Answers (1)

paul bica
paul bica

Reputation: 10715

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

Upvotes: 1

Related Questions