VBA help - Count Prime Numbers

[pdhil87]

I have created a function PRIME with Boolean output to check if a number is prime or not
I am now looking to create a function that counts the prime numbers between two numbers n1 and n2
Help much appreciated!

 

[Peter_SSs]

Welcome to the MrExcel board!

Try

Function CountPrimes(n1 As Long, n2 As Long) As Long
  Dim i As Long
  
  For i = n1 To n2
    CountPrimes = CountPrimes - Prime(i)
  Next i
End Function

 

[Rick Rothstein]

The following function (no other functions are required) will return the count for the number of primes between any two positive numbers less than 20000000 (the function returns -1 if you submit invalid values)...

[table="width: 500"]
[tr]
	[td]Function PrimeCountBetween(FromNumber As Long, ToNumber As Long) As Long
  Dim X As Long, NthPrime As Long, Test As Long, Sum As Long, Results As Variant
  Const MaxPrime As Long = 20000003
  Static Initialized As Boolean, PrimesFlag() As Byte
  If FromNumber > 0 And FromNumber < ToNumber And ToNumber < 20000001 Then
    If Not Initialized Then
      Initialized = True
      PrimesFlag = ChrW(1) & ChrW(257) & String(10000000, ChrW(256))
      Test = 3
      Do While Test <= MaxPrime
        For X = 2 * Test To MaxPrime Step Test
          PrimesFlag(X) = 0
        Next
        Test = Test + 1 - (Test > 2)
      Loop
    End If
    For X = FromNumber To ToNumber
      If PrimesFlag(X) = 1 Then PrimeCountBetween = PrimeCountBetween + 1
    Next
  Else
    PrimeCountBetween = -1
  End If
End Function[/td]
[/tr]
[/table]

Note: The first time the function is used in a session of Excel, there will be a slight delay of a few seconds, after that, the count is returned in about a second or less (at least that is the timing on my computer).

 

[patel45]

Hi Rick, can you explain me your code ? I can not understand how you find prime numbers