vba create random number code example

Example 1: 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.

Example 2: vba random number

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