Speed--Excel 2003 RAND vs. VBA Rnd...

[TIMERESRCH]

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.

 

[TIMERESRCH]

I thought it might be a good idea to reproduce the section I referred to from
Microsoft's Knowledge Base article (828795):

The basic idea is to generate three streams of random numbers (in columns
headed "ix", "iy", and "iz") by a common technique and then to use the result
that if you take three random numbers on [0,1] and sum them, the fractional
part of the sum is itself a random number on [0,1]. The critical statements in
the Fortran code listing from the original Wichman and Hill article are:

C IX, IY, IZ SHOULD BE SET TO INTEGER VALUES BETWEEN 1 AND 30000 BEFORE
FIRST ENTRY

IX = MOD(171 * IX, 30269)

IY = MOD(172 * IY, 30307)

IZ = MOD(170 * IZ, 30323)

RANDOM = AMOD(FLOAT(IX) / 30269.0 + FLOAT(IY) / 30307.0 + FLOAT(IZ) / 30323.0,
1.0)


Therefore IX, IY, IZ generate integers between 0 and 30268, 0 and 30306, and 0
and 30322 respectively. These are combined in the last statement to implement
the simple principle that was expressed earlier: if you take three random
numbers on [0,1] and sum them, the fractional part of the sum is itself a
random number on [0,1].


Paul Williams

 

[Ian Smith]

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.

The following code plus global declarations for whIx, whIy & whIz and
a procedure to initialize whIx, whIy & whIz should be enough. It's
what I use but then I'm not out to get the fastest possible code!

Ian Smith

P.S. When all the fuss was made about the Rand function for EXCEL 2003
generating negative numbers, I did check this routine. I just put it
into a loop which was to stop when it delivered a negative number. I
got bored waiting after it had gone round the loop a few hundred
million times.

Public Function random() As Double

'// Algorithm AS 183 Appl. Statist. (1982) vol.31, no.2

'// Returns a pseudo-random number rectangularly distributed
'// between 0 and 1. The cycle length is 6.95E+12 (See page 123
'// of Applied Statistics (1984) vol.33), not as claimed in the
'// original article.

'// IX, IY and IZ should be set to integer values between 1 and
'// 30000 before the first entry.

'// Integer arithmetic up to 30323 is required.

whIx = (171 * whIx) Mod 30269
whIy = (172 * whIy) Mod 30307
whIz = (170 * whIz) Mod 30323

random = whIx / 30269# + whIy / 30307# + whIz / 30323#
If random > 1# Then random = random - 1#
If random > 1# Then random = random - 1#

End Function