Creating an Array from a Range in VBA

Question

I'm having a seemingly basic problem but can't find any resources addressing it.

Simply put, I just want to load the contents of a Range of cells (all one column) into an Array.

I am able to accomplish this by means of

DirArray = Array(Range("A1"), Range("A2"))

But for some reason, I cannot create the array when expressed this way:

DirArray = Array(Range("A1:A2"))

My real Range is much longer (and may vary in length), so I don't want to have to individually enumerate the cells this way. Can anyone tell me how to properly load a whole Range into an Array?

With the latter code:

MsgBox UBound(DirArray, 1)

And

MsgBox UBound(DirArray)

Return 0, whereas with the former they return 1.

Solution 1

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

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

No need for the Array command.

Solution 2

If we do it just like this:

Dim myArr as Variant
myArr = 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()

Solution 3

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.

Solution 4

In addition to solutions proposed, and in case you have a 1D range to 1D array, i prefer to process it through a function like below. The reason is simple: If for any reason your range is reduced to 1 element range, as far as i know the command Range().Value will not return a variant array but just a variant and you will not be able to assign a variant variable to a variant array (previously declared).

I had to convert a variable size range to a double array, and when the range was of 1 cell size, i was not able to use a construct like range().value so i proceed with a function like below.

Public Function Rng2Array(inputRange As Range) As Double()

    Dim out() As Double    
    ReDim out(inputRange.Columns.Count - 1)

    Dim cell As Range
    Dim i As Long
    For i = 0 To inputRange.Columns.Count - 1
        out(i) = inputRange(1, i + 1) 'loop over a range "row"
    Next

    Rng2Array = out  
End Function

Solution 5

This function returns an array regardless of the size of the range.

Ranges will return an array unless the range is only 1 cell and then it returns a single value instead. This function will turn the single value into an array (1 based, the same as the array's returned by ranges)

This answer improves on previous answers as it will return an array from a range no matter what the size. It is also more efficient that other answers as it will return the array generated by the range if possible. Works with single dimension and multi-dimensional arrays

The function works by trying to find the upper bounds of the array. If that fails then it must be a single value so we'll create an array and assign the value to it.

Public Function RangeToArray(inputRange As Range) As Variant()
Dim size As Integer
Dim inputValue As Variant, outputArray() As Variant

    ' inputValue will either be an variant array for ranges with more than 1 cell
    ' or a single variant value for range will only 1 cell
    inputValue = inputRange

    On Error Resume Next
    size = UBound(inputValue)

    If Err.Number = 0 Then
        RangeToArray = inputValue
    Else
        On Error GoTo 0
        ReDim outputArray(1 To 1, 1 to 1)
        outputArray(1,1) = inputValue
        RangeToArray = outputArray
    End If

    On Error GoTo 0

End Function

View more solutions

Comments

[basaltanglia]

Wow, that is blindingly obvious. I had tried that but not, I think, in conjunction with DirArray(1, 1), which was needed instead of just DirArray(1). Thank you so much!

[Rick Henderson]

But then is that not a Variant and not an array? Is there a memory use difference in this case?

[Rick Henderson]

I realize now that using Variant arrays is one of the only ways to do somethings in Excel.

[ThomasMcLeod]

This actually creates an array length 5 of arrays of length one of variants.

[QHarr]

God all the times I have done extra unnecessary steps. +1

[Slai]

Also, myArr = [transpose(A1:A10)]

[Vityata]

@Slai - yup, it works as well, but I am having my own rule, to use [] only for named ranges, like in the third point here - stackoverflow.com/a/35864330/5448626

[John Alexiou]

Almost. It is recommended to show the array as an array of Variants. Declare Dim DirArray() As Variant instead.

[p014k]

@basaltanglia Could you explain the need for DirArray(1,1) in returning values? This seems like a 1D array to me, so DirArray(1) should work.

[Excel Hero]

@ja72 Recommended by whom? Stuffing an array inside a variant is almost always the better option.

[John Alexiou]

@ExcelHero - I did a test and both DirArray As Variant and DirArray() As Variant yield the exact same data structure in memory. So the point is moot.

[Excel Hero]

@ja perhaps your point is moot... and that's why I brought it to your attention. My point, however, is far from moot. A variant holding the array is far more flexible than a declared array of variants!

[WhyGeeEx]

Good catch. But also note if the Range is columnar, you'd need to look at inputRange.Rows.Count, not inputRange.Columns.Count. You'd also use the counter variable in your assignment statement on the row, not the column.

[TravelinGuy]

To hopefully save others time, newer versions of Excel now have the function as Application.WorksheetFunctions.Transpose()

[drgs]

Suppose your range is only one cell. DirArray = Range("a1:a1").Value, DirArray is no longer an array

[Rodolfo]

@ExcelHero I would like to know in which way it is more flexible and if there is any efficiency loss. Thanks for your comments

[BigBen]

@TravelinGuy - no Application.Transpose is the late-bound version of Application.WorksheetFunction.Transpose.