Making this IsPrime function work. Please help!

[Geir]

I have just found this function that returns TRUE if a number in cell is prime number:

http://www.bluemoosetech.com/microso...rime%20Numbers

I am not getting this thing to work. I get a #NAME? error when using it.

What are the proper steps to implement this function?!

[Roger Govier]

Hi

It sounds as though you haven't copied the code supplied into a standrad module in the VB Editor.

Copy the Code in the example
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
With any number in say, cell A1, enter in B1
=ISPRIME(A1)

[shg]

That function will take a loooooong time to check a large number, and it only accommodates numbers to the size of a Long. Try this one (same instructions as Roger suggested, but you can't have both in the same workbook, since they have the same name).

Function IsPrime(d As Double, Optional bFirstFactor As Boolean = False) As Variant
    ' Returns:      if d is:
    '   #VALUE!     < 2
    '   "Too big!"  > 1E+15
    '   #VALUE!     <> Int(d)
    '   FALSE       composite and bFirstFactor = False or omitted
    '   1st factor  composite and bFirstFactor = True
    '   TRUE        prime

    Const dMax  As Double = 1E+15
    Dim dDiv    As Double
    Dim dRt     As Long

    If d < 2 Then
        IsPrime = CVErr(xlErrValue)

    ElseIf d = 2# Or d = 5# Then
        IsPrime = True

    ElseIf d > dMax Then
        IsPrime = "Too big!"

    ElseIf Int(d) <> d Then
        IsPrime = CVErr(xlErrValue)

    Else
        ' can't use Mod with numbers bigger than Longs, so ...
        Select Case Right(CStr(d), 1)
            Case "0", "2", "4", "6", "8"
                IsPrime = IIf(bFirstFactor, 2, False)
            Case "5"
                IsPrime = IIf(bFirstFactor, 5, False)
            Case Else
                For dRt = 3 To Int(Sqr(d)) Step 2
                    dDiv = d / dRt
                    If Int(dDiv) = dDiv Then
                        IsPrime = IIf(bFirstFactor, dRt, False)
                        Exit Function    '----------------------------------------->
                    End If
                Next dRt
                IsPrime = True
        End Select
    End If
End Function

If you want the first factor instead of False when the number is not prime, set the optional second argument TRUE.

[Roger Govier]

Hi

i hadn't bothered to take a look at the actual code, merely tried to point you in the direction of entering what you had in the correct place.

If you want an efficient function for doing this, then the following (produced by the late George Simms) is the best that I have seen.

As mentioned by shg, you can't have this in the same module unless you rename the other functions first.

Function IsPrime(Number As Long) As Boolean
    Dim x As Long
    Application.Volatile
    If Number Mod 2 = 0 Then
        IsPrime = False
    Else
        For x = 3 To Sqr(Number) Step 2
            If Number Mod x = 0 Then
                IsPrime = False
                Exit For
            Else
                IsPrime = True
            End If
        Next x
    End If
End Function

[shg]

That is indeed better, but still limited to a Long.

And why need the function be volatile?

[Geir]

I tried using the code and instructions you provided but I still get a #NAME? when entering the =IsPrime(C6) inside a cell.

The excel file is xlsm type

Maybe there is some excel config. option that needs to be enabled?!

[Geir]

I made it work but not in the original file. For some or other reason it does not run there.

Would anyone please tell me how to make it return "1" or "One" when it considers a cell with the number 1 inside?!

Thanks for all the help at this great forum!

[shg]

=if(a1=1, 1, isprime(a1))