Answers for "excel vba random number"

excel vba random

'VBA has the Rnd() function which returns a random single floating point value
'between 0 and 1, but not including 1.

MsgBox Rnd		'<--displays something like 0.74323

'To generate integer values between two integer numbers, use the following formula:
'Int((max - min + 1) * Rnd + min)

'For example, to generate random integers between 11 and 14:
MsgBox Int((14 - 11 + 1) * Rnd + 11)    '<--displays 11 or 12 or 13 or 14	

'------------------------------------------------------------------------------

'Note: If min is ONE for the above range to randomly select from, then 
'      the formula can be shortened to: Int(Rnd * max + 1):

MsgBox Int(Rnd * 4 + 1)		'<--randomly displays 1 or 2 or 3 or 4

'Note: If min is ZERO for the above range to randomly select from, then 
'      the formula can be shortened to: Int(Rnd * (max + 1)):

MsgBox Int(Rnd * (3 + 1))	'<--randomly displays 0 or 1 or 2 or 3

'------------------------------------------------------------------------------

'Note: Each time the Rnd() function is called a different random single floating 
'      point value will be returned. But Rnd() is likely to produce 
'      the same SEQUENCE of random values if the procedure containing
'      the multiple calls to the Rnd() function is accessed later.
'
'      To ensure that the routine procuces a fresh sequence of random
'      numbers when the routine is later called again, use the VBA
'      Randomize statement once:

Sub RandomSequence()
    Randomize
    Debug.Print Rnd, Rnd, Rnd
End Sub


'Reference:
    https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/randomize-statement

excel vba array of random numbers

'VBA function to return an array of random integer numbers:

Function RandomArray(elems&, min&, max&)
    RandomArray = Evaluate("transpose(randbetween(" & min & "+row(1:" & elems & ")*0," & max & "))")
End Function

'---------------------------------------------------------------------------------------------------

MsgBox Join(RandomArray(10, 1, 5)) 

'The above displays something similar to: 5 3 1 3 3 2 4 2 4 1



'NB: 
'-All arguments to the function are Long integers
'-elems specifies how many elements in the array
'-min is the minimum random value (can be negative, zero, or poistive)
'-max is the maximum random value (can be negative, zero, or poistive)
'-max must be greater than or equal to min
'-The array is a 1D array with the first index at 1

'Note: Office 365 Monthly contains a new worksheet function, 
'      'RANDARRAY()' which offers more features.

vba random number

Dim MyValue 
Randomize 		' Initialize random-number generator. 
MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

excel vba random

'VBA has the Rnd() function which returns a random single floating point value
'between 0 and 1, but not including 1.

MsgBox Rnd		'<--displays something like 0.74323

'To generate integer values between two integer numbers, use the following formula:
'Int((max - min + 1) * Rnd + min)

'For example, to generate random integers between 11 and 14:
MsgBox Int((14 - 11 + 1) * Rnd + 11)    '<--displays 11 or 12 or 13 or 14	

'------------------------------------------------------------------------------

'Note: If min is ONE for the above range to randomly select from, then 
'      the formula can be shortened to: Int(Rnd * max + 1):

MsgBox Int(Rnd * 4 + 1)		'<--randomly displays 1 or 2 or 3 or 4

'Note: If min is ZERO for the above range to randomly select from, then 
'      the formula can be shortened to: Int(Rnd * (max + 1)):

MsgBox Int(Rnd * (3 + 1))	'<--randomly displays 0 or 1 or 2 or 3

'------------------------------------------------------------------------------

'Note: Each time the Rnd() function is executed a different random single floating 
'      point value will be returned. But Rnd() is likely to produce 
'      the same SEQUENCE of random values if the procedure containing
'      the multiple calls to the Rnd() function is accessed later.
'
'      To ensure that the routine procuces a fresh sequence of random
'      numbers when the routine is later called again, use the VBA
'      Randomize statement once:

Sub RandomSequence()
    Randomize
    Debug.Print Rnd, Rnd, Rnd
End Sub


'Reference:
    https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/randomize-statement