# excel - Write prime numbers between 1 to 100 through VBA coding

1.6k views

### excel - Write prime numbers between 1 to 100 through VBA coding

I found the below coding but its not working. Please share the more appropriate code for writing prime numbers in VBA

``````Private Sub cmdPrime_Click()
Dim p, n, i As Integer
p = 1
Print “Prime Numbers are : ”
For n = 1 To 100
For i = 2 To n – 1
If n Mod i = 0 Then
p = 0
Exit For
Else
p = 1
End If

Next
If p = 1 Then
Print n
End If

Next
End Sub
``````

by (71.8m points)

I am guessing you were translating this from another language? You really should have pointed out which lines were failing and what you researched.

The `“”`: Smart quotes. This is typical of when copying between applications, so be careful. The speech marks for use in the visual basic editor need to be `""` in order to compile.

If you put `Option Explicit` at the top of your code it gives you lots of nice warnings about variable declarations and spellings by the way.

You are only going to 100 so `Integer` is fine, but there are no advantages of `Integer` over `Long` in this instance, so using `Long` is safer in case you decide, in the future, to go beyond the capacity of an `Integer`, and then you risk overflow. You would also, at a sufficiently high upper limit, need to factor out `mod`.

The MOD function returns an error if the divisor (the second argument in the MOD function), multiplied by 134,217,728, is less than or equal to the number being evaluated (the first argument in the MOD function).

Microsoft suggest re-working as `=number-(INT(number/divisor)*divisor)`; which I guess you could replace `INT` with `CLng` in to keep with Longs.

``````Option Explicit
Private Sub cmdPrime_Click()
Dim p As Long, n As Long, i As Long, iCounter As Long
p = 1
With ActiveSheet
.Cells(iCounter + 1, 1) = "Prime Numbers are: " 'Debug.Print "Prime Numbers are: "
For n = 2 To 100 ''< As pointed out 1 is not technically a prime btw so can start at 2
For i = 2 To n - 1
If n Mod i = 0 Then              ' If n - (CLng(n / i) * i) = 0 Then
p = 0
Exit For
Else
p = 1
End If
Next
If p = 1 Then
iCounter = iCounter + 1
.Cells(iCounter, 1) = n  'Debug.Print n
End If
Next
End With
End Sub
``````

To test if `n` is prime, you only need to test divisability up to square root of `n`. And you only need to test for divisibility by previously detected primes. And you can skip even values of `n`.