This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Recursive Programming Using Recursion to Display the Contents of a Folder Using Recursion to Show Hierarchical Breadcrumbs 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).

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.

Recursive Programming
Using Recursion to Display the Contents of a Folder
Using Recursion to Show Hierarchical Breadcrumbs
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

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:


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

Overflow:

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"

Else

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!

Parts of this blog
Recursive Programming Using Recursion to Display the Contents of a Folder Using Recursion to Show Hierarchical Breadcrumbs Using Recursion to Solve Factorials (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Using Recursion to Solve Factorials

Example of Factorial Program

Head office

London

Manchester