Click the arrow to go up a level to the Math Index.
| Follow the above link or click the graphic below to visit the Homepage. |
 | MS Excel VBA Prime Explorer v1.4 Jim Cullen |
 | Download MS-Excel VBA Prime Explorer v1.4 |
|
Excel VBA: Prime Explorer v1.4
Do not delete or rename Sheet1 since the macro references that sheet by name for it's functions. It is recommended keeping the spreadsheet PrimeExp.xls as Read-Only. I would suggest creating a copy and using that to run the macro, keeping the original as a backup. There is only one VBA macro, named PrimeExplorer(), and it can be launched by pressing the Ctrl+Z keys. All other functions are nested within PrimeExplorer as subroutines. All calculations take place within a numbered grid which must be set up before any calculations will be performed. All functions are entered as letters and numbers that begin in the first cell ('A1'). For functions that require multiple parameters; the first parameter goes into cell (A1), the second into (A2), the third into (A3), and so on. Entered functions are moved to the right side of the grid after they are processed. This is for reference as you inspect the results of a function execution. In these instructions, the # sign indicates that the parameter should be a positive integer. The current functions in Prime Explorer are: g Creates the grid. This function requires two integer parameters; the number of rows and the number of columns... in that order. The grid will be filled with numbers beginning with 1 in the upper left corner. Example: (A1)= g (A2)= 20 (A3)= 10 and pressing Ctrl+Z will create a grid of 20 rows and 10 columns. The interior cells will be numbered from 1 to 200. An optional fourth parameter may be added that will allow the grid to begin at some other number besides one. Example: (A1)= g (A2)= 35 (A3)= 19 (A4)= 23 and pressing Ctrl+Z will create a grid of 35 rows and 19 columns, with the numbering of the grid beginning with 23 in the upper left corner. Numbering will still wrap to the width of the grid as the modulus labels above the grid dictate. c Cleans the grid. Removes all bold type and background colors in the grid but leaves the grid itself intact. Also restores any accidentally deleted entries in the grid. Example: (A1)= c and press Ctrl+Z. # An integer in cell (A1) will cause that number and all multiples of that number to be set in bold type and colored in the grid. The color used will be the background color of cell (A1) so you get to choose the divisor as well as its background color. You may enter more than one divisor by entering your second integer in cell (A2) and setting the background for that divisor and all its multiples in the grid. You may enter as many divisors and background colors as you like - just note that prime numbers have been assigned the color red internally. e Automated Sieve of Eratosthenes up to a limit specified by the user. The background color set in cell (A1) is used as the color to highlight the marked entries in the grid. The upper limit of the sieve is set as an integer value in cell (A2). The function will remove multiples of all primes, up to the specified limit, from the grid by highlighting the multiples. Running the sieve function erases all current highlighting in the grid so perform sieving first prior to highlighting primes or other values in the grid. Example: (A1)=e (A2)=17 Sieves out and highlights all entries in the grid that are evenly divisible by any prime number less than or equal to 17. p Prime Numbers within the grid will be set to bold type and the cell background color set to red. The primes are accurate up to 1,000,000 and after that are 'probable primes'. The algorithm consists of four Fermat Tests, to bases 2,3,5,7 followed by a lookup table of 22 pseudoprimes up to 1,000,000. A Fermat Test checks for the congruence b^(p-1) mod p equal to 1 with base b in the range from 1 to (p-1) and coprime to p. These tests are performed using a form of binary modular exponentiation. All primes will pass the Fermat Test but a few composite integers also pass for a given base b. These are known as pseudoprimes. Different bases have different sets of pseudoprimes but there are still some pseudoprimes, known as the Carmichael Numbers, that are shared by ALL bases except those that are one of the factors of p. The first few Carmichael Numbers are: 561, 1105, 1729, 2465, etc. These can be filtered out by trial division, table lookup, or stronger prime tests that are immune to these types of pseudoprimes, such as the Rabin-Miller Test. For our purposes, the Fermat Test with a simple table lookup is sufficient. Example: (A1)= p and press Ctrl+Z. t Calculates Totals for marked entries for all columns in the table. The sum Totals are printed out along the bottom of the grid. Example: (A1)= t and press Ctrl+Z. s Highlights Fermat Pseudoprimes for a given Base in bold type with an automatically assigned background color of magenta. Fermat Pseudoprimes (p) to base (b) are those numbers where b^(p-1) is congruent to 1 mod p but p is not prime. An additional restriction is that b is less than p. Example: (A1)= s (A2)= 2 will cause - if your grid is large enough to include them - 341, 561, 645, etc., to be given bold print and a magenta background in the table. Note that using the pseudoprime function will erase all existing highlighted or bolded entries in the table so, if you want to compare pseudoprimes to highlighted divisors and their multiples in the table, do the pseudo-prime function first followed by the divisor function. You can choose multiple bases by entering further bases down the A column. The highlighted entries will then correspond to integers that are pseudoprimes to ALL the chosen bases. For example, in the primes up to 1000, find all Fermat Pseudoprimes to bases 2, 5, and 13. You would enter: (A1)= s (A2)= 2 (A3)= 5 (A4)= 13 and press Ctrl+Z. You will see only one result and that is the number 561. On the 39 by 39 grid I tried this on, which has over 1500 entries, this was the only result that appeared and took about 5 or 6 seconds to calculate. What's the next pseudoprime to bases 2, 5, and 13? The only hint I'll give is that it's less than 10000. The result was verified by other software but Prime Explorer is perfectly capable of finding it. The pseudoprime function takes a few seconds extra time to finish calculating since all chosen Fermat tests, as well as a Prime test, need to be performed. u Creates an Ulam pattern, highlighting the primes in bold and red background. This is not Ulam's Spiral but one of the variations where the grid is numbered beginning at the upper-left corner and scanning outward diagonally through the grid. The typical patterns evident in the Spiral of Ulam will also appear on the spreadsheet, though on a smaller scale. The highlighting of divisors or pseudoprimes is not yet enabled for the Ulam display and the pattern does not allow the option of beginning the table at a value other than one. |