Writing recursive programs in VBA
Part four of a four-part series of blogs

In certain cases in VBA (displaying the contents of folders, listing hierarchical data) writing a program which calls itself is by far the easiest way to go - this blog gives worked examples of 3 such recursive programs.

  1. Recursive Programming
  2. Using Recursion to Display the Contents of a Folder
  3. Using Recursion to Show Hierarchical Breadcrumbs
  4. Using Recursion to Solve Factorials (this blog)

This blog is part of our Excel VBA tutorial series of blogs (consider also booking on one of our Visual Basic for Applications courses).

Posted by Andy Brown on 20 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Using Recursion to Solve Factorials

The factorial of a number is written (in the UK at any rate) as n!.  For example:

  • 5! = 5 x 4 x 3 x 2 x 1 = 120
  • 3! = 3 x 2 x 1 = 6

Notice that for any n:

 n! = n * (n-1)!

To the experienced programmer, this is crying out for a program which will call itself recursively.

Example of Factorial Program

The program below will show one of two things:

Factorial for modest number Factorial for too-large number
Output if the factorial isn't too big Output if the number is too large

Here is the suggested program.  First we set some global variables (including the seed whose factorial we'll calculate):

Option Explicit

'allow fairly large numbers

Dim CurrentFactorial As Long

'detect if this wasn't enough

Dim IfOverflow As Boolean

'number whose factorial we'll calculate

Const Seed As Integer = 10

Next, we'll write our subroutine which calls itself recursively:

Sub GetFactorial(n As Integer)

'if we've had an overflow, no point continuing

If IfOverflow Then Exit Sub

'if an error happens, set flag and exit

On Error GoTo Overflow

'if we've got down to 1, exit

If n <= 1="">Then Exit Sub

'multiply by this number and recursively get the next factorial down

CurrentFactorial = CurrentFactorial * n

GetFactorial (n - 1)

'finished - exit

Exit Sub


IfOverflow = True

End Sub

The important line is the one I've put in bold: the GetFactorial subroutine calls itself, with a number one less each time.

Finally, we need to write a program to call the GetFactorial subroutine in the first place, and to display its result:

Sub ShowFactorials()

'initially there is no overflow problem ...

IfOverflow = False

'... and factorial equals 1

CurrentFactorial = 1

'call routine to find factorial

Call GetFactorial(Seed)

'if a problem say so - otherwise, return answer

If IfOverflow Then

MsgBox "Too large a number generated - try something smaller"


MsgBox "Factorial of " & Seed & " is " & CurrentFactorial

End If

End Sub

When you run this last subroutine, you'll see the factorial of the number you chose as a seed!


This blog has 0 threads Add post