How to generate a random number in Excel VBA

Question

I wanted to write a function generate some numbers which represent

2 = White, 3 = Red, 4 = Green, 5 = Blue, 6 = Yellow

I know that if it is equal probability, then I can write

GenerateColor = Int(Rnd() * 5) + 2

However, I want to generate white half of the time and share the other half with the remaining 4 colors, how can I do? (It means 50% probability is white, 50% of probability is remaining 4 colors.)

Do it twice...First 50/50 to see if WHITE or RANDOM, then use RND again to choose the other 4 colors — Grantly
– Grantly, Commented Oct 9, 2017 at 19:13
Generate 2 instead. First one will be random between a 0 and a 1, and if it's 0, use white, and if it's 1, generate one for 3 to 6. — braX
– braX, Commented Oct 9, 2017 at 19:13
Or you can use GenerateColor = Application.Max(2, Int(Rnd() * 8) - 1) (But doing it in two steps as per the other comments will be more understandable.) (Reposted the comment because I think the brackets were wrong first time.) — YowE3K
– YowE3K, Commented Oct 9, 2017 at 19:30
If you want to use another distribution types, different from Uniform, refer to this. But i think that your case uses Bernoulli or Uniform. — danieltakeshi
– danieltakeshi, Commented Oct 9, 2017 at 20:00

Gary's Student · Accepted Answer · 2017-10-09 19:20:29Z

7

Give this a try:

Sub RanGen()
    With Application.WorksheetFunction
        x = .RandBetween(1, 2)
        If x = 2 Then
            MsgBox x
        Else
            x = .RandBetween(3, 6)
            MsgBox x
        End If
    End With
End Sub

answered Oct 9, 2017 at 19:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4039065 · Accepted Answer · 2017-10-09 19:16:50Z

5

In a worksheet formula this would be similar to,

=choose(randbetween(1, 2), "white", choose(randbetween(1, 4), "red", "green", "blue", "yellow"))

You can code that in VBA using native VBA functions or simply add worksheetfunction in several places.

answered Oct 9, 2017 at 19:16

user4039065

5 Comments

YowE3K Over a year ago

That would actually be simpler if you just wrote =choose(randbetween(1, 8), "white", "white", "white", "white", "red", "green", "blue", "yellow")

user4039065 Over a year ago

Yes, I had been thinking about stacking up the white to get the correct ratio but I tend to avoid repetitious (i.e. ugly) code like that.

user4039065 Over a year ago

er... uh... non-elegant...?

YowE3K Over a year ago

I'll make it "pretty" by suggesting =choose(Max(1,randbetween(-2, 5)), "white", "red", "green", "blue", "yellow")

YowE3K Over a year ago

(But, as I said in my comment on the question itself, the two step approach you used will be more understandable.)

ImaginaryHuman072889 · Accepted Answer · 2017-10-09 19:21:37Z

4

You can also do this without VBA:

=IF(RAND()<0.5,2,FLOOR(RAND()*4,1)+3)

This gives a 50% chance to return 2, and the other 50% is evenly distributed between 3 and 6.

answered Oct 9, 2017 at 19:21

ImaginaryHuman072889

5,2857 gold badges23 silver badges58 bronze badges

Comments

ian0411 · Accepted Answer · 2017-10-09 19:52:43Z

2

You can also use a helper column with cumulative values like this:

The formula you use for result is:

=INDEX($B$2:$B$6,COUNTIF($D$2:$D$6,"<="&RAND())+1)

The formula in column D just a simple SUM function:

=SUM($C$2:C2)

This should also give you the result you are looking for and will be easier to change in the future.

answered Oct 9, 2017 at 19:52

ian0411

4,2853 gold badges28 silver badges33 bronze badges

Collectives™ on Stack Overflow

How to generate a random number in Excel VBA

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related