# Prime Numbers

#### XL_2K3

##### New Member
Hi there:

Do you think any of you could help me to make this UDF so when a 0 (zero) or 1 (one) is inputted it retrieves an "X" or any other character as the output? (since 0 and 1 are not prime nor composite numbers).

Well here is the UDF i want to be adapted.

Code:
``````Function prime(num)
Dim j, k, remain As Integer
j = Int(num)
If j <> num Then
prime = "NonInt"
Exit Function
End If
j = Sqr(num)
For k = 2 To j
remain = num Mod k
If remain = 0 Then
prime = "NonPrime"
Exit Function
End If
Next k
prime = "Prime"
End Function``````

Thanks a lot in advance! ### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Andrew Poulsom

##### MrExcel MVP
Try:

Code:
``````Function prime(num)
Dim j, k, remain As Integer
j = Int(num)
If j <= 1 Then
prime = "X"
Exit Function
End If
If j <> num Then
prime = "NonInt"
Exit Function
End If
j = Sqr(num)
For k = 2 To j
remain = num Mod k
If remain = 0 Then
prime = "NonPrime"
Exit Function
End If
Next k
prime = "Prime"
End Function``````

#### XL_2K3

##### New Member
Try:

Code:
``````Function prime(num)
Dim j, k, remain As Integer
j = Int(num)
If j <= 1 Then
prime = "X"
Exit Function
End If
If j <> num Then
prime = "NonInt"
Exit Function
End If
j = Sqr(num)
For k = 2 To j
remain = num Mod k
If remain = 0 Then
prime = "NonPrime"
Exit Function
End If
Next k
prime = "Prime"
End Function``````

Hey A.P. thanks for the adaptation of the code!

I already had tried adding that one more If statement for j<=1 then "X", but somehow it won't work; instead of retrive the X or the words Prime and Nonprime it retrieves a #NAME? error... Anyhow i already fixed it implementing and IF statement but within the Exel formula and it works great!

Cheers! #### Andrew Poulsom

##### MrExcel MVP
Hey A.P. thanks for the adaptation of the code!

I already had tried adding that one more If statement for j<=1 then "X", but somehow it won't work; instead of retrive the X or the words Prime and Nonprime it retrieves a #NAME? error... Anyhow i already fixed it implementing and IF statement but within the Exel formula and it works great!

Cheers! The function worked when I tried it. It certainly didn't give me a #NAME? error.

#### XL_2K3

##### New Member

Yuu are absolutely right Andrew, i just checked again using your adapted code and it works fine. At the begining when i copied and paste the formula through a range of cells the first 200 numbers was marked with an "X" but then they got the right output, some times i have a lot of windows and programs opened at the same time so that consume a lot of CPU and memory resource and makes Excel calculations slow, i think that was that made excel retrieve the error message...

Anyway, it's good all works ok now. Thanks again for your help.

#### tusharm

##### MrExcel MVP
You already have a solution to your specific problem but a few pointers...

j is not declared as an integer. It is a variant.

You can skip the test for num being an integer by declaring it as such.

You should test that num is positive but I guess it is covered by the new <=1 test.

You can skip 1/2 of the loops. Outside the loop test for Num Mod 2 =0. If not, then the loop should be from 3 to sqr(num) step 2. This is because once you've tested for divisibility by 2 you can safely skip testing all multiples of 2.
Hi there:

Do you think any of you could help me to make this UDF so when a 0 (zero) or 1 (one) is inputted it retrieves an "X" or any other character as the output? (since 0 and 1 are not prime nor composite numbers).

Well here is the UDF i want to be adapted.

Code:
``````Function prime(num)
Dim j, k, remain As Integer
j = Int(num)
If j <> num Then
prime = "NonInt"
Exit Function
End If
j = Sqr(num)
For k = 2 To j
remain = num Mod k
If remain = 0 Then
prime = "NonPrime"
Exit Function
End If
Next k
prime = "Prime"
End Function``````

Thanks a lot in advance! #### XL_2K3

##### New Member

You already have a solution to your specific problem but a few pointers...

j is not declared as an integer. It is a variant.

You can skip the test for num being an integer by declaring it as such.

You should test that num is positive but I guess it is covered by the new <=1 test.

You can skip 1/2 of the loops. Outside the loop test for Num Mod 2 =0. If not, then the loop should be from 3 to sqr(num) step 2. This is because once you've tested for divisibility by 2 you can safely skip testing all multiples of 2.

Hi @tusharm:

I understan what you mean above and i agree. I tried to modify the code as you suggested but, as i'm less than a beginner with VBA, i probably just did the mess with the code!, lol. which certainly i did! This are the change i did to the original code:

Code:
``````Function prime(num)
Dim j, k, remain As Integer
j = Int(num)
If j <= 1 Then
prime = "X"
Exit Function
End If
Exit Function
j = Sqr(num)
For k = 3 To j
remain = num Mod k
If remain = 0 Then
prime = "NonPrime"
Exit Function
End If
Next k
prime = "Prime"
End Function``````

So now i only get 0 (zeros) in the output! .

But i know you know how to do it right! #### tusharm

##### MrExcel MVP
A great resource to use is the google archive of the Excel newsgroups (groups.google.com). I searched it on your behalf and found a discussion that contained a function by Vasant Nanavati who got it right after a few attempts, a suggested improvement by Jerry Lewis that showed how to speed up the function by skipping checks for multiples of 2, 3, and even 5, and a non-VBA solution by Harlan Grove. See http://groups.google.com/group/micr...functions/browse_frm/thread/919fa5d2078e5045/

In another discussion at http://groups.google.com/group/micr...mming/tree/browse_frm/thread/4b8b96cfaa612430 Frank Kabel points to a page dedicated to prime numbers and a list of the first 1000 prime numbers, which I presume one could load into a VBA collection and then test primality (is there such a word?) with one check!

#### XL_2K3

##### New Member
Ok, thanks for the links, i'll have a look...

For now the issue i had with the prime numbers is fixed, (it was a cookie to crack), but i'll be back later with a real challange for any of the Excel MVP and high advanced users who want accept the challenge . As a hint the problem i'm dealing with have to do with the Vlookup and/or match/index functions, in other words i need to invoice/retrieve values from a main table to several secondary tables located in other spreadshhets and workbooks...

Alright, so for now is all and thanks one more time to A.P. and T.M. for your help.

Regards.

Replies
0
Views
314
Replies
3
Views
533
Replies
0
Views
290
Replies
3
Views
222
Replies
7
Views
295 Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,774
Messages
5,833,620
Members
430,221
Latest member
jmmccormick ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back