Random Number

[AlexanderBB]

I found this via Google but just wonder if it's correct

Public Function randomvalue(top As Integer, low As Integer)
    Randomize    ' Initialize random-number generator.
    randomvalue = CInt((top - low + 1) * Rnd + low) ' Generate random value between low and top.
End Function

Because if I send in a Top of 29 and a low of 0 I have found it will return 30

 

[Phuoc]

Try change CInt(... to Int(

 

[AlexanderBB]

What difference would that make ?

 

[Phuoc]

The VBA Int function rounds a supplied number down to an integer.

The VBA CInt function converts an expression into an Integer.

Public Sub Test()
    MsgBox Int(1.75)
    MsgBox CInt(1.75)
End Sub

 

[joeu2004]

What difference would that make

Of course, you could just try it and see for yourself.

Sub doit()
x = Int(30 * 0.999999999999999)
y = CInt(30 * 0.999999999999999)
MsgBox x & vbNewLine & y
End Sub

30 represents 29-0+1. And 0.999999999999999 represents the largest value that Rnd might return, in theory (close enough; actually, it is 1 - 2^-53).

The result is 29 for Int and 30 for CInt.

The point is: Int truncates, which is what you require. CInt rounds -- and banker's rounding, at that.

 

[AlexanderBB]

I found a 30 instead of 29 max whether I used Int or Cint
Removing the +1 gave the correct 0-29 range.

Public Sub mytest()
Dim a As Integer
Do
    a = randomvalue(29, 0)
    If a = 0 Or a = 29 Then Debug.Print a;

Loop
End Sub
Public Function randomvalue(top As Integer, low As Integer)
    Randomize    ' Initialize random-number generator.
    'randomvalue = CInt((top - low + 0) * Rnd + low) ' Generate random value between low and top.
    randomvalue = Int(top - low + 0) * Rnd + low ' Generate random value between low and top.
End Function

 

[Phuoc]

I found a 30 instead of 29 max whether I used Int or Cint
Removing the +1 gave the correct 0-29 range.

Please try it with : =randomvalue(2,1)

 

[AlexanderBB]

Both Int and Cint return 1,2, and 3 for : =randomvalue(2,1)
With +1 changes to +0 it's 1 and 2 (for both Int and Cint).
You get something different ?

 

[Phuoc]

Why not use: = RANDBETWEEN (0.29)?

 

[AlexanderBB]

Is that a command for VBA? I get Sub or Function not defined. Perhapos needs some reference added?