Random Number

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
I found this via Google but just wonder if it's correct
VBA Code:
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Phuoc

Board Regular
Joined
Apr 29, 2016
Messages
209
The VBA Int function rounds a supplied number down to an integer.

The VBA CInt function converts an expression into an Integer.

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

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,842
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

What difference would that make

Of course, you could just try it and see for yourself.
VBA Code:
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

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
I found a 30 instead of 29 max whether I used Int or Cint
Removing the +1 gave the correct 0-29 range.
VBA Code:
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

Board Regular
Joined
Apr 29, 2016
Messages
209

ADVERTISEMENT

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

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
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 ?
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
Is that a command for VBA? I get Sub or Function not defined. Perhapos needs some reference added?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,749
Messages
5,543,979
Members
410,587
Latest member
kaloybulan
Top