Access Excel VBA Check Prime Number using custom Function

This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE.

Access Excel VBA Check Prime Number using custom Function

In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number.

1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 67 71
21–40 73 79 83 89 97 101 103 107 109 113 127 131 137 139 149 151 157 163 167 173
41–60 179 181 191 193 197 199 211 223 227 229 233 239 241 251 257 263 269 271 277 281
61–80 283 293 307 311 313 317 331 337 347 349 353 359 367 373 379 383 389 397 401 409

http://en.wikipedia.org/wiki/List_of_prime_numbers

Access Excel VBA Function Code – Check Prime Number

Public Function wIsPrimeNumber(sinput) As Boolean
    wIsPrimeNumber = True
    If sinput = 1 Then
        wIsPrimeNumber = False
    ElseIf sinput > 2 Then
        For i = 2 To sinput - 1
            If sinput Mod i = 0 Then
                wIsPrimeNumber = False
                Exit Function
            End If
        Next i
    End If
End Function

VBA Function Code Algorithm – Check Prime Number

Below is what I have done in the Function. This code is 100% original and I originally created it for a question posted in Microsoft Community. I did not even google for similar Function, let me know if you see any holes I am not aware of.

– Function wIsPrimeNumber returns TRUE or FALSE

– Function returns TRUE by default, unless it falls into IF criteria

– Divide the user input number by 1,2,3,…until number itself-1 , if no reminder for all the calculations, then it should be a prime number,  no action is needed because default is already TRUE (is prime number)

– Otherwise if any one of those calculations have no remainder, then it is not a prime number, and the Function returns FALSE

VBA Function Syntax – Check Prime Number

wIsPrimeNumber (sInput)

sInput is the number you want to check whether it is a Prime Number

Example – Check Prime Number

Formula Result
=wisprimenumber(5) TRUE
=wisprimenumber(6) FALSE
=IF(wisprimenumber(5),”Prime Number”,”Not Prime Number”) Prime Number
=IF(not(wisprimenumber(5)),”Not Prime Number”,”Prime Number”) Prime Number

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *