How to create factorial function in VBA?

This time something different. We will go back to the math lessons. In this article I will show You 5 ways of how to create factorial function in VBA.

Definition & example

In short – the factorial function (symbol: !) says to multiply all whole numbers from our chosen number down to 1.

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

So for example if You want to calculate factorial function of 5 (5!) , You need to calculate this operation:

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

Interview

The factorial function is a very good example for first question on job interview.

Why?

First of all, it is not that hard, but also not that easy.
Secondly, this is mathematical function. And good math skill is very useful in programming.
Thridly, there is no the only solution. The are several approaches, which can show the experience of the person.

In this article I want to put a pressure on the third point and go through all the methods to achieve factorial function – from the easiest to the most complex.

I method – For Loop factorial

This is the easiest one – For Loop from 1 to the specified number.

Function factorialForLoop(val As Long) As Long

 Dim i As Long, result As Long
 Let result = 1

 For i = 1 To val
     result = result * i
 Next

 factorialForLoop = result

End Function

II method – Do Until factorial

This time less commonly used form of the loop than For Loop – at least it’s me. Here function loops (multiplies) until the iteration (i) is bigger than (i > val) the specified value (val).

Function factorialDoUntil(val As Long) As Long

 Dim i As Long, result As Long
 Let i = 1
 Let result = 1

 Do Until i > val
     result = result * i
     i = i + 1
 Loop

 factorialDoUntil = result

 End Function

III method – Do While factorial

When I’m using Do Loop I always prefer Until than While, so that’s why it is 3rd on my list. This time the function loops (multiplies) as long as (while) the iterator (i) is smaller or equal (i <= val) to the specified value (val).

Function factorialDoWhile(val As Long) As Long

 Dim i As Long, result As Long
 Let i = 1
 Let result = 1

 Do While i <= val
     result = result * i
     i = i + 1
 Loop

 factorialDoWhile = result

 End Function

IV method – Recursive factorial

As the 4th method I want to show You my attempt to use recursion in case of this issue. In my approach the function calls itself as long as specified value (a) is bigger than 1. My first attempt was not so good, because I needed to use optional value to start (tempVal).

Function factorialFunction(a As Long, Optional tempVal _
    As Long = 1) As Long
 
 If a > 1 Then
     tempVal = tempVal * a
     a = a - 1
     factorialFunction a, tempVal
 End If

 factorialFunction = tempVal

 End Function

V method – 2nd Recursive factorial

This time the recursive factorial looks really profesional. After my trials, small research and little help this is what I got. Without any optional value, You can use something like this:

Function factorialRecursion(n As Integer) As Integer

     If n > 0 Then factorialRecursion = n * factorialRecursion(n - 1)
     If n = 0 Then factorialRecursion = 1

 End Function

Factorial function is a piece of cake!

I hope from now on You’ve learned how to create factorial function in VBA. Not only using the easiest method, but all of them. Not only to impress the recruiter, but to be able to use those methods in other cases.