Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed--Excel 2003 RAND vs. VBA Rnd...
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed--Excel 2003 RAND vs. VBA Rnd...
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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed--Excel 2003 RAND vs. VBA Rnd...
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 speed up printer | Excel Discussion (Misc queries) | |||
how can i set up rand or rand between to give only -1 or 1? | Excel Worksheet Functions | |||
How to speed up slow network access in office 2003 | Excel Discussion (Misc queries) | |||
calculation speed of Excel 2003 and Excel 2000 | Excel Discussion (Misc queries) | |||
Macros i XL 2003 versus 2000 - speed problems | Excel Programming |