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:

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,766
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
Please wrap your code with code tags ( the #) button. Thanks
 

rollis13

Member
Joined
Feb 22, 2013
Messages
80
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
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:

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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]
 

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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:

rollis13

Member
Joined
Feb 22, 2013
Messages
80
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
I thought that 'Srq' was a typo of 'Sqrt' (Square Root), so what is 'Sqr' ?
 

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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?
 

rollis13

Member
Joined
Feb 22, 2013
Messages
80
Reaction score
0
Points
6
Location
Cordenons
Excel Version(s)
2016 32bit
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.
 

Fluff

Member
Joined
Sep 9, 2018
Messages
136
Reaction score
0
Points
16
Excel Version(s)
O365
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
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,149
Reaction score
1
Points
38
Excel Version(s)
365
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:

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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!
 

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
Yes! Thank you very much for your help!!
 
Last edited by a moderator:

Dim85

New member
Joined
Dec 22, 2020
Messages
7
Reaction score
0
Points
0
Excel Version(s)
2016
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!
 
Top