My task is to randomly select 6 records from 360 records of numerical data
based on fairly tight criteria that might require about 3 or 4 million tests to
satisfy on average. I use a certain number of unique data files that I've
replicated a number of times with different file names, and these are opened
and tested by the Excel application. Since there are 2.8 trillion ways to
select 6 numbers from a total of 360 I'm not too worried about duplicate
selections when the same data residing in different files is put through the
program--at least I wasn't concerned until I used Excel 97 and found duplicate
results constantly.

Now I've upgraded to Excel 2003 and its new RAND function (with the recent
hotfix applied) seems to be doing an admirable job of producing unique results
when faced with the same data. After optimizing the VBA code in all the ways I
know how, the VBA routine was achieving about 51 million tests of 6 records
from 360 per hour.

At this point I decided to do all the pre-testing in VBA (a full spreadsheet
recalculation only occurs if a number of pre-tests have succeeded) by uploading
the table of data as an array, and by re-writing all the pre-testing formulas
in VBA, and I was very pleasantly surprised to see the program's speed increase
to about 150 million tests per hour. But then I noticed duplicate results
starting to come in: about every other file produced from the same data
contained an identical result. Obviously VBA's Rnd function works the "old
way," which is very surprising and disappointing--and I hope Microsoft will
update it (I sent that suggestion in to them the other day).

So instead of calling the Rnd function at the top of my looping code I replaced
it with a recalculation of just 6 cells containing the RAND function, and then
assigned each value back into the VBA variables...and the speed of the
application dropped back down to 58 million tests per hour.

I really need that extra speed, and I'd like to know if there's anything more I
can do. Specifically in Knowledge Base article 828795 MS provides the
algorithm for their new RAND function in Fortran code. It's only four lines
long, and looks like it might be possible to reproduce it in VBA. Is there any
way I can re-write that logic in VBA to produce my own random numbers in VBA
from scratch? (I'm not familiar with Fortran.)

Is there anything else I can do to produce decent random numbers in VBA?
Lastly, and I've searched all over and can't find an answer, is one CPU vendor
or another commonly thought of as producing the fastest Excel performance? I'm
considering a hardware upgrade, and I don't know if I should be looking at
Intel or AMD, or AMD's 64-bit chip. I'm trying to achieve the fastest
calculations possible in Excel and VBA. Currently I'm using an Athlon 1700 XP,
with 512 MB of ram. Would a chip twice as fast tend to double the speed of my
program, or are they not directly proportional?

Many thanks in advance for any expert help.