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

 

Access Excel VBA Check Prime Number using custom Function