Prime number in EXCEL VBA-CodePudding

i want to create a function prime(x) and x can be "B1", "A3" or another cells. However, I feel so stuck with my code in EXCEL VBA. Can anyone help me? Thank you so much.

Function prime(x As String) As Boolean


Dim i As Integer, y As Integer

y = Range("x")

For i = 1 To ROUNDOWN(Sqr(y, 0))

    If y Mod i = 0 Then

    prime = True

    Else

    prime = False

    End If

Next i

End function

CodePudding user response：

First you want to use Range in the function not string.

Then you want to set the boolean as True and loop to see if it is divisible.

Then start the loop at 2

Function prime(x As Range) As Boolean
    Dim i As Long, y As Long
    y = x.Value
    prime = True
    For i = 2 To Application.RoundDown(Math.Sqr(y), 0)
        If y Mod i = 0 Then
            prime = False
            Exit For
        End If
    Next i

End Function

Just an FYI, Office 365 Formula version:

=NOT(OR(MOD(A1,SEQUENCE(ROUNDDOWN(SQRT(A1),0)-1,,2))=0))

Older versions:

=NOT(OR(MOD(A1,ROW($ZZ2:INDEX($ZZ:$ZZ,ROUNDDOWN(SQRT(A1),0))))=0))