vba excel array to range code example

Example 1: Excel vba range to array

Dim vArray As Variant
vArray = Range("A1:A10").Value2

Example 2: excerl vba array to range

arrayData = Array("A", "B", "C", "D", "E")

[a1].Resize(UBound(arrayData)) = Application.Transpose(arrayData)

Example 3: excel vba create an array from a range

v = [a1:b20]

'If v is dimensioned as a variant then he above creates a 2d array, 
'20 rows high by 2 columns wide and those 40 array elements contain
'the values from the specified range.

'The square brackets are shorthand notation. Another way to code the
'same thing is:

v = Range("a1:b20")

'In both of the above examples, the Range object is returning its 
'default... the Range.Value property. But keep in mind that the
'Range.Value2 property is about 20% quicker. So it could be more
'performant to code these two examples like so:

v = [a1:b20].Value2
v = Range("a1:b20").Value2