create staticrand function that uses (for example),choose(randbetween(1,6),0,0,1,1,2,3) ?

[David Wale]

Hello
I require a 'static' function for 'choose,randbetween(1,6),0,0,1,1,2,3)?
Thank you in anticipation
David Wale

 

[Rick Rothstein]

Your request is not entirely clear to me, so I guessed at what I think you are asking for. Here is a function where you specify a comma delimited list of value (numbers or text) and the function will return a random selection from among them...

[table="width: 500"]
[tr]
	[td]Function ChooseRand(ParamArray ChooseMe() As Variant) As Variant
  ChooseRand = ChooseMe(Application.RandBetween(LBound(ChooseMe), UBound(ChooseMe)))
End Function[/td]
[/tr]
[/table]

So, if you put this formula in a cell...

=ChooseRand(1,2,3,4,5,6)

the function will randomly return either 1, 2, 3, 4, 5 or 6.

 

[David Wale]

Good evening Rick
It's just gone 4am in England, this problem has been keeping me awake,(at night)! Thank you for you excellent solution.
A supplementary question, when the function returns either 1,2,3,4,5,6. I want the number to be 'static'/'the value' and not keep up dating.
Thank you for your help.
David Wale

 

[Rick Rothstein]

Ah, that is what you meant by static. I do not think you can do that with a function in a cell. However, you can do that with a macro that calls the ChooseRand function internally to generate a value and then place that value into a cell. Here is what I am thinking...

[table="width: 500"]
[tr]
	[td]Sub PlaceRandom()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Value = ChooseRand([B][COLOR="#FF0000"]1, 2, 3, 4, 5, 6[/COLOR][/B])
  Next
End Sub

Function ChooseRand(ParamArray ChooseMe() As Variant) As Variant
  ChooseRand = ChooseMe(Application.RandBetween(LBound(ChooseMe), UBound(ChooseMe)))
End Function[/td]
[/tr]
[/table]

Put the above code in a module (note the ChooseRand function is the same as the one I posted earlier, so make sure it only appears once in the module), then select one or more cells and run the PlaceRandom macro (make sure you change my 1,2,3,4,5,6 list (highlighted in red) to the list of values you actually want to randomly select from).

 

[David Wale]

Hello Rick
Thank you for the macro Sub PlaceRandom ()', I will be able to use and develop the 'Sub' in my spreadsheet programme.
And thank you again for the Function ChooseRand, I was hoping that this Function would be the answer, as it's ideal, if the outcome remained static?
Thank you again for your for your detailed response.
Best wishes
David Wale

 

[mopp1]

This revision of Rick's function stays static unless an all workbooks recalc (ctrl-alt-F9) is performed:

Function ChooseRand(ParamArray ChooseMe() As Variant) As Variant
ChooseRand = ChooseMe(Int(Rnd() * (-LBound(ChooseMe) + UBound(ChooseMe) + 1)) + LBound(ChooseMe))
End Function

It uses VBA Rnd() function rather than Excel function RandBetween