Issue with Function - Compile error

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
Hello everyone,
I have an issue with a Function I am trying to create. The function’s target is to bring as a result whether a number is prime or not.
The code I have prepared is the following:

Code:
[[B]Function prime[/B](n As Integer) As Boolean
Dim Flag As Boolean, l As Integer, i As Integer
l = WorksheetFunction.RoundDown(Sqr(n, 0))
Flag = True
For i = 2 To l
    If n Mod i = 0 Then
        Flag = False
    End If
Next i
If Flag = True Then
    prime = False
Else
    prime = True
End If
'Place your code here
End Function]
When I try to run the code, a compile error appears, saying “Wrong number of arguments or invalid property assignment”.
It indicates the “Sqr”
I am entirely new to VBA, and I would appreciate your help in this matter.
 
Last edited by a moderator:
Please wrap your code with code tags ( the #) button. Thanks
 
Code:
l = WorksheetFunction.RoundDown(Sqr(n, 0))
should be:
l = WorksheetFunction.RoundDown(Sqr(n), 0))
but:
=ROUNDDOWN(A1,0)
=SQRT(A1)
so:
l = WorksheetFunction.RoundDown(Sqrt(n), 0))
 
Last edited:
Please wrap your code with code tags ( the #) button. Thanks

Sorry for that and thank you for noticing!

Code:
Code:
[B]Function prime(n As Integer) As Boolean
Dim Flag As Boolean, l As Integer, i As Integer
l = WorksheetFunction.RoundDown(Sqr(n, 0))
Flag = True
For i = 2 To l
     If n Mod i = 0 Then
         Flag = False
     End If
Next i
If Flag = True Then
     prime = False
Else
     prime = True
End If
End Function[/B]
 
Thank you very much
; I appreciate your help! It worked with the error, but still, it brings me the wrong result! I must recheck the code!
 
Last edited:
I thought that 'Srq' was a typo of 'Sqrt' (Square Root), so what is 'Sqr' ?
 
I thought that 'Srq' was a typo of 'Sqrt' (Square Root), so what is 'Sqr' ?

As far as I can understand from a course I currently attend, 'Sqr' also means (Square Root). In the VBE, when I use ‘Sqr’ it takes it asSquare Root. Have you put it like this and see if it brings you the right result?
 
Yes, ok, 'Sqr' is Square Root for VBE but I can't be of any other help since I don't know which would be the 'right result'; my goal was to get your macro working.
 
How about
Code:
Function prime(n As Integer) As Boolean
Dim Flag As Boolean, l As Integer, i As Integer
l = WorksheetFunction.RoundDown(Sqr(n), 0)
Flag = True
For i = 2 To l
     If n Mod i = 0 Then
         Flag = False
     End If
Next i
If Flag = True Then
     prime = True
Else
     prime = False
End If
End Function
 
If using many larger numbers, exiting the for loop on the first encounter of no remainder could save iterations and (a little) time.
It will also save the need for the Flag variable:
Code:
Function prime(n As Long) As Boolean
Dim i As Long
prime = True
For i = 2 To Application.RoundDown(Sqr(n), 0)
  If n Mod i = 0 Then
    prime = False
    Exit For
  End If
Next i
End Function
 
Last edited:
Yes, ok, 'Sqr' is Square Root for VBE but I can't be of any other help since I don't know which would be the 'right result'; my goal was to get your macro working.

I want to indicate the result when I put a groupof numbers, whether they are prime numbers or not. With your help withparenthesis, I achieved to do it. Hence, the code is working correctly now.Thank you very much!
 
Yes! Thank you very much for your help!!
 
Last edited by a moderator:
If using many larger numbers, exiting the for loop on the first encounter of no remainder could save iterations and (a little) time.
It will also save the need for the Flag variable:
Code:
Function prime(n As Long) As Boolean
Dim i As Long
prime = True
For i = 2 To Application.RoundDown(Sqr(n), 0)
  If n Mod i = 0 Then
    prime = False
    Exit For
  End If
Next i
End Function

Perfect! I will try it! Thank you very much for your help!
 
Back
Top