Skip to main content

Creating recursive procedures

Procedures have a limited amount of space for variables. Each time a procedure calls itself, more of that space is used. A procedure that calls itself is a recursive procedure. A recursive procedure that continuously calls itself eventually causes an error. For example:

Function RunOut(Maximum)
    RunOut = RunOut(Maximum)
End Function

This error may be less obvious when two procedures call each other indefinitely, or when some condition that limits the recursion is never met. Recursion does have its uses. For example, the following procedure uses a recursive function to calculate factorials.

Function Factorial(N)
    If N <= 1 Then ' Reached end of recursive calls. 
        Factorial = 1 ' (N = 0) so climb back out of calls. 
    Else ' Call Factorial again if N > 0.
        Factorial = Factorial(N - 1) * N
    End If
End Function

You should test your recursive procedure to make sure it does not call itself so many times that you run out of memory. If you get an error, make sure your procedure is not calling itself indefinitely. After that, try to conserve memory by:

  • Eliminating unnecessary variables.
  • Using data types other than Variant.
  • Re-evaluating the logic of the procedure. You can often substitute nested loops for recursion.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>