Paste array to range VBA 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 code to use array formula

Range("A1").FormulaArray = "=INDEX(subset!R1C1:R2472C10,MATCH(1,(RC1=subset!C1)(RC2=subset!C2)(RC5=subset!C5)*(RC6=subset!C6),0),10)"