Creating an Array from a Range in VBA

Just define the variable as a variant, and make them equal:

Dim DirArray As VariantDirArray = Range("a1:a5").Value

No need for the Array command.

If we do it just like this:

Dim myArr as VariantmyArr = Range("A1:A10")

the new array will be with two dimensions. Which is not always somehow comfortable to work with:

To get away of the two dimensions, when getting a single column to array, we may use the built-in Excel function “Transpose”. With it, the data becomes in one dimension:

If we have the data in a row, a single transpose will not do the job. We need to use the Transpose function twice:

Note: As you see from the screenshots, when generated this way, arrays start with 1, not with 0. Just be a bit careful.

Edit June.2021:In newer versions of Excel, the function is: Application.WorksheetFunctions.Transpose()

Using Value2 gives a performance benefit. As per Charles Williams blog

Range.Value2 works the same way as Range.Value, except that it does not check the cell format and convert to Date or Currency. And thats probably why its faster than .Value when retrieving numbers.

So

DirArray = [a1:a5].Value2

Bonus Reading

• Range.Value: Returns or sets a Variant value that represents the value of the specified range.
• Range.Value2: The only difference between this property and the Value property is that the Value2 property doesn't use the Currency and Date data types.