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

 

[joeu2004]

randomvalue = Int(top - low + 0) * Rnd + low ' Generate random value between low and top.

The correct implementation is:

randomvalue = Int((top-low+1)*Rnd) + low

 

[joeu2004]

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]

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.

 

[Rick Rothstein]

Debug.Print WorksheetFunction.RandBetween(0, 29) still brings up an error

What version of Excel are you using?

 

[AlexanderBB]

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)