Below we will look at a program in Excel VBA that checks whether a number is a prime number or not.
Before you start: in mathematics, a prime number is a number that has exactly two distinct number divisors: 1 and itself. The smallest twenty-five prime numbers are: 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89 and 97. For example, 8 has 1, 2, 4 and 8 as divisors and is not a prime number.
1. First, we declare three variables. One Integer variable we call divisors, one Long variable we call number and one Long variable we call i. We use Long variables here because Long variables have larger capacity than Integer variables.
Dim divisors As Integer, number As Long, i As Long
2. We initialize two variables. We initialize the variable divisors with value 0. We use the InputBox function to get a number from the user.
divisors = 0
number = InputBox(“Enter a number”)
After the user has entered a number, we want to check whether this number is a prime number or not. Remember, a prime number has exactly two distinct number divisors: 1 and itself.
3. We start a For Next loop.
For i = 1 To number
4. Now comes the most important part of the program. To calculate the number of divisors of a number, we use the Mod operator. The Mod operator gives the remainder of a division. So 7 mod 2 would give 1. 7 is divided by 2 (3 times) to give a remainder of 1. Only if “number mod i’ equals 0, i is a divisor of number. In this case we want to increment the variable divisors by 1. The macro below does the trick.
If number Mod i = 0 Then divisors = divisors + 1
Excel VBA checks this for i = 1, i = 2, i = 3, i = 4 until i = number. Note that i = 1 and i = number always are divisors of number. Only if these numbers are the only divisors of number, the number is a prime number.
5. Don’t forget to close the loop.
6. If divisors equals 2, we display a msgbox saying that the entered number is a prime number. If the number of divisors is higher than 2, we display a msgbox saying that the entered number is not a prime number.
If divisors = 2 Then
MsgBox number & ” is a prime number”
MsgBox number & ” is not a prime number”
7. Test the program.
Result for 104729: