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
The factorial function is a very good example for first question on job interview.
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.