Prime Numbers Excel Function
Prime numbers are numbers, divisible by themselves. In the current example I simply made an Excel VBA function telling us whether a number is prime or not. It works as a charm š CheckĀ the primeĀ numbers between 1 andĀ 100:
Check how the formula works in Excel:
So, this is how it works. And finally, here comes the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Option Explicit Public Const cPrime As String = "Prime" Public Const cNotPrime As String = "Not a prime, has other divisors." Public Const cNotPositive As String = "A number should be an integer bigger than 0." Public Function fPrime(lNumber) As String Dim i As Long If (lNumber = 1) Or (lNumber = 2) Or (lNumber = 3) Then fPrime = cPrime Exit Function End If If (lNumber < 1) Or (lNumber <> CLng(lNumber)) Then fPrime = cNotPositive Exit Function End If For i = 2 To CLng(Sqr(lNumber)) Step 1 If lNumber Mod i = 0 Then fPrime = cNotPrime Exit Function End If Next i fPrime = cPrime End Function Sub TestPrimeNumbers() Dim i As Long For i = 1 To 100 Step 1 If fPrime(i) = cPrime Then Debug.Print i & " " & fPrime(i) Next i End Sub |
Prime Numbers Matrix and Coloring
Creating a matrix of the prime numbers and coloring the prime numbers in green is achieveable with some VBA code and taking care of the numbers a bit more. Building the “sieve” looks like this:
|
1 2 3 4 5 6 7 8 |
For number = 1 To 130 If (number Mod 10 = 0) Then column = 10 row = number \ 10 Else row = number \ 10 + 1 column = number Mod 10 End If |
Once the “sieve” is built, then the isPrime boolean function can be introduced. The whole code, from a blank page looks like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Public Function isPrime(number As Long) As Boolean If (number = 1) Or (number = 2) Or (number = 3) Then isPrime = True Exit Function End If If (number < 1) Then isPrime = False End If Dim i As Long For i = 2 To CLng(Sqr(number)) Step 1 If number Mod i = 0 Then isPrime = False Exit Function End If Next i isPrime = True End Function Sub Main() Dim row As Long Dim column As Long Dim number As Long Dim myCell As Range Dim wks As Worksheet: Set wks = Worksheets(1) wks.Cells.Delete wks.Columns(1).Resize(, 10).ColumnWidth = 3.22 For number = 1 To 130 If (number Mod 10 = 0) Then column = 10 row = number \ 10 Else row = number \ 10 + 1 column = number Mod 10 End If Set myCell = wks.Cells(row, column) myCell.Value = number myCell.HorizontalAlignment = xlCenter If isPrime(number) Then myCell.Interior.Color = vbGreen End If Next End Sub |
Cheers! š