VBA – Function in Excel to check prime numbers

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:

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:

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:

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! 🙂