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.

Situation:

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.

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.

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.

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. For example, 7 mod 2 = 1 because 7 divided by 2 equals 3 with a remainder of 1. Only if ‘number mod i’ = 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.

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.

7. Test the program.

Result for 104729: