Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Neil M
 
Posts: n/a
Default Random Number generator

When I use random number generator, is there a way to make specific random
numbers remain the same based on data in other cells?


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Random Number generator

"Neil M" wrote:
When I use random number generator, is there a way to
make specific random numbers remain the same based
on data in other cells?


I create my own RNG function in a macro, often simply calling
the VBA Rnd() function. Excel then does the "right" thing, which
is to call the VBA function only when the entire worksheet is
recalculated or when a referenced cell is recalculated.

I might define myrand() as follows:

function myrand(optional range)
myrand = Rnd()
end function

Then I can use it in the worksheet in any of the following ways:

=myrnd()
=myrnd(A1)
=myrnd(A1:C10)

The first form will call myrnd() only one time and whenever
the entire worksheet is recalculated.

The second form will also call myrnd() when A1 is recalculated.

The third form will also call myrnd() when any cell in the range
is recalculated.

Caveat: Normally the entire worksheet is recalculated only
when you type shift-ctrl-F9. However, I discovered that the
entire worksheet is also recalculated whenever you delete
another worksheet(!). The only way I know to avoid that is
to disable auto-recalc. Of course, sigh, that defeats the
purpose of avoiding recalc by hiding the RNG inside a VBA
function in the first place -- unless you like recalculating
individual cells ;-).

PS: You can create the above VBA function by typing alt-F11
and clicking Insert Module in the VBE. Unless you want to
sign your macros (a good idea, I guess), you might also need
to weaken your macro security by clicking Tools Macros
Security Security Level Medium in Excel (not VBE). This
prompts you to enable macros when you open workbooks
that have macros.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default Random Number generator

Errata ....

I wrote:
Then I can use it in the worksheet in any of the following ways:
=myrnd()
=myrnd(A1)
=myrnd(A1:C10)


Of course, all instances of "myrnd" shoud be "myrand". I
variously use "rnd" and "rand" in the name because of the
different in spelling between Excel and VBA. "Sometimes
you feel like a nut; sometimes you don't" :-).
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA "Rnd" Function: Truly Random? TheRobsterUK Excel Discussion (Misc queries) 2 September 27th 05 04:50 AM
random number without repeating? nonoi via OfficeKB.com Excel Worksheet Functions 2 July 11th 05 05:59 AM
Random Number Generator Pascale Excel Worksheet Functions 3 July 9th 05 12:37 AM
Random number generator Philippe L. Balmanno Excel Worksheet Functions 4 December 17th 04 08:42 AM
Random # Generator vamosj Excel Worksheet Functions 0 November 8th 04 08:14 PM


All times are GMT +1. The time now is 08:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"