Search code examples
arraysvbaexcelrandom

Random element from array in Excel-VBA

I've stormed the internet and still have not found any solution to this question:

I have an array like so;

Dim PayRate As Variant
PayRate = Array(10, 20, 30)

Cells(i, "E").value = PayRate(Int((2 - 1 + 1) * Rnd + 1))

but this will only give me 20 and 30 from the array (elements 1 and 2), I also want element 0 = 10 in the mix ? How do I got about doing that?

Solution

  • This will do it: Int(Rnd() * 3) + 1

    Rnd() returns a uniformly distributed random number from and including 0 to and not including 1.

    Important: you must have Option Base 1 at the top of the module so your PayRate array has lowest bound of 1. Seems like you have this given your question text.