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.
Dim L As Long
Dim N As Long
N = 3
L = Fact(N)
Debug.Print "The Factorial of " & CStr(N) & " is " & Format(L, "#,##0")
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)
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.
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
R = AddUp(N + 1)
AddUp = R
In this code, there is no condition that prevents AddUp
from calling itself. Every call
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