ThreeWave Recursive Programming

This page describes a programming technique called Recursive Programming, in which a procedure calls itself repeatedly until some escape condition is met.


Recursive programming is a powerful technique that can greatly simplify some programming tasks. In summary, recursive programming is the situation in which a procedure calls itself, passing in a modified value of the parameter(s) that was passed in to the current iteration of the procedure. Typically, a recursive programming environment contains (at least) two procedures: first, a procedure to set up the initial environment and make the initial call to the recursive procedure, and second, the recursive procedure itself that calls itself one or more times.

Let's begin with a simple example. The Factorial of a number N is the product of all the integers between 1 and N. The factorial of 5 is equal to 5 * 4 * 3 * 2 * 1 = 120. In the real world you would not likely use a recursive procedure for this, but it will serve as a simple yet illustrative example. The first procedure is named DoFact sets things up, calls the Fact function and displays the result.

Sub DoFact()
    Dim L As Long
    Dim N As Long
    N = 3
    L = Fact(N)
    Debug.Print "The Factorial of " & CStr(N) & " is " & Format(L, "#,##0")
End Sub

The Fact function does the real work of calculating the factorial.

Function Fact(N As Long) As Long
    If N = 1 Then
        Fact = 1
        Fact = N * Fact(N - 1)
    End If
End Function

In this code, the value of the input N is tested. If it is 1, the function simply returns 1. If N is greater than 1, Fact calls itself passing itself the value N-1. The function returns as its result the input value N times the value of itself evaluated for N-1.


Cautions For Recursive Programming

While recursive programming is a powerful technique, you must be careful to structure the code so that it will terminate properly when some condition is met. In the Fact procedure, we ended the recursive calls when N was less than or equal to 1. Your recursive code must have some sort of escape logic that terminates the recursive calls. Without such escape logic, the code would loop continuously until the VBA runtime aborts the processing with an Out Of Stack Space error. Note that you cannot trap an Out Of Stack Space error with conventional error trapping. It is called an untrappable error and will terminate all VBA execution immediately. You cannot recover from an untrappable error.

For example, consider the following poorly written recursive procedure:

    Function AddUp(N As Long)
        Static R As Long
        If N <= 0 Then
            R = 0
        End If
        R = AddUp(N + 1)
        AddUp = R
    End Function
In this code, there is no condition that prevents AddUp from calling itself. Every call to AddUp results in another call to AddUp. The function will continue to call itself without restriction until the VBA runtime aborts the procedure execution sequence.

See also Recursion And The File System Object for additional recursive code examples.

This page last updated: 14-September-2007