About Us

Ian Smith[_2_]

(TIMERESRCH) wrote in message ...
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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2003 speed up printer	kfreasstudio	Excel Discussion (Misc queries)	1	March 3rd 09 01:10 PM
how can i set up rand or rand between to give only -1 or 1?	Uesiet	Excel Worksheet Functions	8	October 27th 08 02:28 PM
How to speed up slow network access in office 2003	LeanneW	Excel Discussion (Misc queries)	0	February 22nd 08 01:16 AM
calculation speed of Excel 2003 and Excel 2000	calc difference in Excel 2003 and 2000	Excel Discussion (Misc queries)	0	March 23rd 07 09:18 PM
Macros i XL 2003 versus 2000 - speed problems	Ankan[_2_]	Excel Programming	2	December 10th 03 12:08 PM

Menu

About Us