Below is an optimized function to check on the primality of a number. This function takes input number as Double, hence can be used to check upto a number having 15 significant digits. Whereas Long can take up to a maximum of 10 significant digits and maximum number it can support is 2,147,483,648.
First function is when pure number is passed, hence argument can be declared as Double. Hence, you will have to pass the value not the range.
Second function is when variant is passed as argument so that even range can be passed in this function.
Function IsPrime(Num As Double) As Boolean
Dim i As Double
If Int(Num / 2) = (Num / 2) Then
Exit Function
Else
For i = 3 To Sqr(Num) Step 2
If Int(Num / i) = (Num / i) Then
Exit Function
End If
Next i
End If
IsPrime = True
End Function
Function IsPrimeV(Num) As Boolean
Dim i As Double
If Int(Num / 2) = (Num / 2) Then
Exit Function
Else
For i = 3 To Sqr(Num) Step 2
If Int(Num / i) = (Num / i) Then
Exit Function
End If
Next i
End If
IsPrimeV = True
End Function
Below is time performance for both functions –
The file used for checking time performance – Prime Number Checker
Are you really sure, the result is correct for Num=2?
That's a surprisingly fast function, all things considered. I tried some other methods to improve it, but using anything other than a For/Next loop actually slows it down making most other tweaks non-viable. However, a couple simple modifications makes it about 20% faster. Below is a modified version for Double precision, same mods make a more noticeable improvement to the version using Variants.
Function IsPrime(Num As Double) As Boolean
Dim i As Long
Dim temp As Long
If Num > 2 ^ 50 Then
'too large, exceeds Excel significant digits so results would be meaningless
Exit Function
ElseIf Int(Num / 2) = (Num / 2) Then
Exit Function
ElseIf Int(Num / 3) = (Num / 3) Then
Exit Function
Else
'all primes >3 are of the form 6k+/-1, as all other odd numbers are multiples of 3
'the below uses that to reduce the number of divisions performed by 1/3
For i = 5 To Int(Sqr(Num)) Step 6
temp = i + 2
If Int(Num / i) = (Num / i) Then
Exit Function
ElseIf Int(Num / temp) = (Num / temp) Then
Exit Function
End If
Next
End If
IsPrime = True
End Function
P.S. there is a bug in the original, and in my updated version. Values less than 4 will return incorrect results. Easy fix is to add these lines at the start after DIM Temp…
If Num = 2 Or Num = 3 Then
'allow to fall through
ElseIf Num < 2 Then
'less than 2, not prime
Exit Function
ElseIf Int(Num / 2) = (Num / 2) Then ' replaces existing if statement