A recursive process is a process that calls itself. A recursive function is a function that calls itself (with different arguments).

Recursion is a powerful problem-solving tool. It can break a complex problem into simpler ones. It is available in many modern languages such as VBA.

For example the factorial function, n!, can be expressed recursively as

n! = n * (n - 1)!

for any non-negative n, with 0! defined as 1.
Thus 5! = 5 * 4 * 3 * 2 * 1 can be recast as a recursive sequence:

5! = 5 * 4!
4! = 4 * 3!
3! = 3 * 2!
2! = 2 * 1!
1! = 1 * 0! = 1

Let us first develop a function that computes the factorial function using a loop rather than using recursion. The basis for the loop implementation comes from the observation:

n! = 1 * 2 * 3 * ... * (n - 1) * n

We can write the function as follows:

Function Factorial(n)
Dim i As Integer
Factorial = 1
For i = 1 To n
    Factorial = Factorial * i
Next i
End Function

To see how this function works, let us assume that n = 3. Before the loop is entered, Factorial is initialized to 1. For the first iteration of the loop, i = 1, and Factorial is computed as

Factorial = 1 * 1 = 1

On the second iteration, i = 2,

Factorial = 1 * 2 = 2

On the final iteration, i = 3,

Factorial = 2 * 3 = 6

The loop then terminates, and the function returns the correct result for #! = 6.

Note that for n = 0, the loop is never executed, and the correct result for 0! = 1 is returned.

Next we develop an alternate factorial function that exploits recursive function, that is the function actually calls itself. In order for a recursive function to eventually terminate, there must be at least one termination condition. For the factorial function that computes n!, it involves calling itself to compute (n - 1)!, which in turn call itself to compute (n - 2)!, etc. The termination condition of course comes from the fact that 1! = 1, that is for an argument of 1, the function should return the value 1.

The following is a version of such a recursive factorial function:

Function FactRecurs(n)
If n > 0 Then
    FactRecurs = n * FactRecurs(n - 1)
Else
    FactRecurs = 1
End If
End Function

To see how this function works, again we assume that n = 3. When the function is first invoked, the If statement will test true, since 3 > 0, and FactRecurs will be computed as

FactRecurs = 3 * FactRecurs(2)

To compute the right-hand side FactRecurs is called, except that the argument n (= 2)is one less than before. With n = 2, the If statement will again test true, and FactRecurs will be computed as

FactRecurs = 2 * FactRecurs(1)

Thus FactRecurs calls itself again, but now with an argument of 1. Again the If statement will test true, and FactRecurs will be computed as

FactRecurs = 1 * FactRecurs(0)

Now the function calls itself with an argument of 0. . This time, since n = 0, the If statement will test False, and the Else clause will be implemented as
FactRecurs = 1

without invoking itself. Thus the function terminates and returns a value of 1 to the previous invocation (FactRecurs(1)). Thus,

FactRecurs = 2 * FactRecurs(1) = 2 * 1 = 2

This process is repeated as the value is returned to the initial invocation,

FactRecurs = 3 * FactRecurs(2) = 3 * 2 = 6

and the function terminates completely and returns a value of 6.

We use both of these versions of the factorial function in TestFactorial.xls.