CODEWITHSUNDEEP
excelvba
user2672288
user2672288

Reputation:

VBA - random number between two values

I've seen multiple answers say the following algorithm works fine to generate a random number between two values. I'm getting spurious results where I'll sometimes get a value returned that is higher than the upper bound.

Dim random as Integer
random = Int (3 - 0 + 1) * Rnd + 0
Debug.Print random

This should give values between 0 - 3 right? I'm seeing 0 to 4 when run a few times..???

enter image description here

Upvotes: 2

Views: 16791

Answers (2)

Bathsheba
Bathsheba

Reputation: 234635

This is one of the idiosyncrasies of VBA.

You can see the effect more clearly by writing 

random = Int(4) * Rnd

4 * Rnd is a floating point double type, and when it gets assigned to random, The same rounding convention as for CInt is applied1; i.e. if Int(4) * Rnd is 3.5 or greater, the result is 4.

The fix is to write

random = Int(4 * Rnd)

1 The convention "round half to even" is often called Banker's Rounding. See https://en.wikipedia.org/wiki/Rounding#Round_half_to_even

Upvotes: 5

Slai
Slai

Reputation: 22866

I think it is to be expected when explicitly converting floating point number to integer, but what might not be expected is that the rounding is towards the closest even number:

Dim i As Integer
i = 3.5           ' i = 4
i = "2.5"         ' i = 2

Upvotes: 1

Related Questions