Recursive Procedures with Excel VBA

In VBA, it is possible to create recursive procedures, i.e., procedures that call themselves.

A classical example of a recursive procedure is one that returns the next member of the Fibonacci sequence.

The first two members of the Fibonacci sequence are equal to 1, and each subsequent member is the sum of the two preceding ones. Thus, the n-th Fibonacci number Fi(n) is defined by the following relation:

Fi(n) = Fi(n – 1) + Fi(n – 2)

where Fi(1) = Fi(2) = 1

Finding the n-th Fibonacci number (recursive)

Function Fi(n As Long) As Long
    If n = 1 Or n = 2 Then
        Fi = 1
    Else
        Fi = Fi(n - 1) + Fi(n - 2)
    End If
End Function

Finding a Fibonacci number without recursion

Sub Fibonacci()
    Dim n As Long, i As Long, s As Long
    Dim f1 As Long, f2 As Long
    n = 30
    f1 = 1 : f2 = 1 : s = 1
    For i = 3 To n
        s = f1 + f2
        f1 = f2
        f2 = s
    Next
    MsgBox s
End Sub

NOTE

Despite the elegance of recursive procedures, they must be used with caution, since careless use can lead to memory problems — repeated calls of such a procedure quickly consume stack memory.

Moreover, recursive programs execute significantly slower than iterative programs (loops) when solving the same problems.

On the other hand, it should be remembered that many computational problems naturally lead to stack overflows when recursion is used.

Another classical example of using recursive functions is computing the greatest common divisor (GCD) of two integers using Euclid’s algorithm.

The greatest common divisor (GCD) of two integers is the largest integer that divides both numbers. For example:

• GCD(10, 14) = 2
• GCD(15, 31) = 1

Euclid’s algorithm consists of the following steps:

1. If a is divisible by b, then GCD(a, b) = b.
2. Otherwise, GCD(a, b) = GCD(b, a Mod b).

The recursive function provided in the file 6-Examples of procedures.xlsm on the compact disc implements Euclid’s algorithm.