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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,842
Office Version
  1. 2010
Platform
  1. Windows
Why not use: = RANDBETWEEN (0.29)?
Is that a command for VBA? I get Sub or Function not defined. Perhapos needs some reference added?

I think Phuoc assumes that you call your randomvalue function from Excel. Of course, your test demonstrates that you might not.

In VBA, you could write:

randomvalue = WorksheetFunction.RandBetween(low, top)

But if randomvalue is called from Excel (or from a VBA function that is called from Excel), the major difference is: RANDBETWEEN and WorksheetFunction.RandBetween cause the Excel formula to be "volatile". That is, the Excel formula -- and any cell that references it directly or indirectly -- is recalculated whenever any cell in the entire workbook is edited, and at other times that Excel decides to recalculate any part of the workbook.

Your use of Rnd makes your randvalue function non-volatile.
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
I'm doing my tests in Excel, but I'm not using a worksheet. Just VBA module.
Debug.Print WorksheetFunction.RandBetween(0, 29) still brings up an error
But = Int((top-low+1)*Rnd) + low is testing good. I'm getting both 0 and 29 and no <0 or > 30 so I'll go with that. Good to have it right.
Thanks.
 

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,705
My apologies... although it didn't work the first time (and I only tried once) it did now..
Both acquired the dropdown list... (using Excel 2010)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,751
Messages
5,543,985
Members
410,589
Latest member
MrBK
Top