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

Question

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

Answer 1

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 preserve for future readers: The additional helpful comments are from @ChrisNeilsen.

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.