StrOutput = Int(CSng(Interval)) & ' days ' & Format(Interval, 'hh') _ ' Format and print the time interval in days, hours, minutes and seconds. Set oWorksheet = ActiveWorkbook.Worksheets(SheetName)įunction ElapsedTime(endTime As Date, startTime As Date) If (number Mod 6 = 1 Or number Mod 6 = 5) Then possiblePrime = Trueįunction PrintArray(Data, SheetName As String, intStartRow As Integer) S(1, 3) = 'Elapsed time: ' & ElapsedTime(endTime, startTime)įunction GetPrimeList(count) As ArrayList Prints the 1st 100,000 prime numbers in 10 seconds approx. I have created 2 modules for separating logic and utility functions. Here is my solution using Sieve of Eratosthenes algorithm with 6k ± 1 optimization and ignoring all even and divisors of 5. And you can skip even values of n.Ĭame across this question while brushing up my VBA skills. And you only need to test for divisibility by previously detected primes. To preserve for future readers: The additional helpful comments are from test if n is prime, you only need to test divisability up to square root of n. If n Mod i = 0 Then ' If n - (CLng(n / i) * i) = 0 Then Cells(iCounter + 1, 1) = 'Prime Numbers are: ' 'Debug.Print 'Prime Numbers are: 'įor n = 2 To 100 ''< As pointed out 1 is not technically a prime btw so can start at 2 Option Explicitĭim p As Long, n As Long, i As Long, iCounter As Long Microsoft suggest re-working as =number-(INT(number/divisor)*divisor) which I guess you could replace INT with CLng in to keep with Longs.

To the number being evaluated (the first argument in the MOD In the MOD function), multiplied by 134,217,728, is less than or equal The MOD function returns an error if the divisor (the second argument You would also, at a sufficiently high upper limit, need to factor out mod. 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. 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. The speech marks for use in the visual basic editor need to be '' in order to compile. This is typical of when copying between applications, so be careful. I am guessing you were translating this from another language? You really should have pointed out which lines were failing and what you researched.

List of prime numbers through 100