CODEWITHSUNDEEP
vba
codea
codea

Reputation: 1551

How to generate random positive and negative numbers in VBA

I am trying to generate random integers over the range (-5, 5) using VBA, something like 100+/- rnd 5. I was wondering if some of you has more elegant way to do it. What I have so far is only "+"

randomRange= 100 + CInt(Rnd * 5)

Upvotes: 1

Views: 7859

Answers (3)

osquro
osquro

Reputation: 11

Try this

randomRange = 100 + CInt(5 * (2 * Math.Rnd - 1))

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

You can use Excel's RANDBETWEEN function like this:

randomRange= 100 + Application.WorksheetFunction.RandBetween(-5, 5)

Upvotes: 3

codea
codea

Reputation: 1551

I have found a solution.

randomRange=100 + cint((Rnd()*((-1)^INT(rnd()*10))) * 5)

Where

  • (Rnd()*((-1)^INT(rnd()*10))) * 5 is the part that generates number between -1 and 1.
  • *5 gives a number between -5 and 5.

Thanks to DRJ on Mr. Excel Forum

Upvotes: 0

Related Questions