Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with RNG Macro
XL 2002
I often use this macro in several cells to generate random numbers:- *-----------------------------------------------------------------* Sub UniqueNums() Function RandNums(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 RandNums = RandNums & " " & iArr(i) Next i RandNums = Trim(RandNums) End Function *-----------------------------------------------------------* In another macro I do a recalc. of the sheet until a certain parametre is met. Often, if I need to stop/interrupt this second macro (e.g. to debug) some or all of the cells containing the RandNums function become *#Value* i.e. they *lose* their original *=RandNums(x,y,z)* value. Is there any way to correct this? TIA Harry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with RNG Macro
Possibly don't make it application.Volatile
-- Regards, Tom Ogilvy "Harry" wrote: XL 2002 I often use this macro in several cells to generate random numbers:- *-----------------------------------------------------------------* Sub UniqueNums() Function RandNums(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 RandNums = RandNums & " " & iArr(i) Next i RandNums = Trim(RandNums) End Function *-----------------------------------------------------------* In another macro I do a recalc. of the sheet until a certain parametre is met. Often, if I need to stop/interrupt this second macro (e.g. to debug) some or all of the cells containing the RandNums function become *#Value* i.e. they *lose* their original *=RandNums(x,y,z)* value. Is there any way to correct this? TIA Harry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with RNG Macro
Hello Tom and thanks for replying. Removing Application.Volatile freezes the cells to the initial results i.e. the recalc in the second macro has no effect. BTW do you know of any similar macros that will give reasonable random results without the same problem? I'm not looking for anything *Hi Tech* here - basically I just need to generate about 30 rows of [exactly] 50 random integers in the range 1 - 500 but without repeats . That is to say: ( i ) no two rows can be the same and ( ii ) no repeats of consecutive numbers between any one row and another e.g. I need to avoid a situation such as :- Row 1 = 405 | 23 | 79 | 329 | 218|.......................................... Row 2 = 56 | 399 | 95 | 79 | 329 | 23 | 405 ......................................... i.e. to avoid a repeat of 79 & 329 and/or 23 & 405 (albeit reversed) Thanks Harry "Tom Ogilvy" wrote in message ... Possibly don't make it application.Volatile -- Regards, Tom Ogilvy "Harry" wrote: XL 2002 I often use this macro in several cells to generate random numbers:- *-----------------------------------------------------------------* Sub UniqueNums() Function RandNums(Bottom As Integer, Top As Integer, _ Amount As Integer) As String Dim iArr As Variant Dim i As Integer Dim r As Integer Dim temp As Integer Application.Volatile ReDim iArr(Bottom To Top) For i = Bottom To Top iArr(i) = i Next i For i = Top To Bottom + 1 Step -1 r = Int(Rnd() * (i - Bottom + 1)) + Bottom temp = iArr(r) iArr(r) = iArr(i) iArr(i) = temp Next i For i = Bottom To Bottom + Amount - 1 RandNums = RandNums & " " & iArr(i) Next i RandNums = Trim(RandNums) End Function *-----------------------------------------------------------* In another macro I do a recalc. of the sheet until a certain parametre is met. Often, if I need to stop/interrupt this second macro (e.g. to debug) some or all of the cells containing the RandNums function become *#Value* i.e. they *lose* their original *=RandNums(x,y,z)* value. Is there any way to correct this? TIA Harry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|