Reading array from Excel cells creating 2d array

Question

I'm attempting to read a list of cell values in to an Array in Excel VBA. I am doing this through:

Dim varHrArray As Variant
varHrArray = Range(Cells(TITLE + 1, HR_LOCATION), Cells(TITLE + intHrLength, HR_LOCATION)).Value

While this works, it's creating a 2d array and I am uncertain exactly why. This has been confirmed by by message boxes, with MsgBox (varHrArray(1, 1)) working as expected but MsgBox (varHrArray(1)) giving a 'Subscript Out Of Range' error and through directly examining the variable in the watch window - varHrArray is showing as Variant/Variant(1 to 7, 1 to 1) (7 is correct for the length of the list, by the way).

I'm not clear why this is happening or how it can be stopped.

Answer 1

Whenever an array is created using range of cells then array created will always be a two dimensional array. This is because a spreadsheet stores values in two dimensions i.e. rows and columns. So the first dimension is the rows and the second dimension is the columns.

Moreover, lower bound of array will always be 1 i.e. LBound(varHrArray)=1.

You can loop through the array as

For i = 1 To UBound(arr)
    some_var = arr(i, 1) 'or arr(i, 2),arr(i, 3) as per column
Next

Though there are methods to convert 2-d array into 1-d array. One of them being

For i = 1 To UBound(varHrArray, 1)
    tempArr(i) = varHrArray(i, 1)
Next

Reading A Worksheet Range To A VBA Array

It is very simple to read a range on a worksheet and put it into an array in VBA. For example,

Dim Arr() As Variant ' declare an unallocated array.
Arr = Range("A1:C5")' Arr is now an allocated array

When you bring in data from a worksheet to a VBA array, the array is always 2 dimensional. The first dimension is the rows and the second dimension is the columns. So, in the example above, Arr is implicitly sized as Arr(1 To 5, 1 To 3) where 5 is the number of rows and 3 is the number of columns. A 2 dimensional array is created even if the worksheet data is in a single row or a single column (e.g, Arr(1 To 10, 1 To 1)). The array into which the worksheet data is loaded always has an lower bound (LBound) equal to 1, regardless of what Option Base directive you may have in your module. You cannot change this behavior.

Above is part of this article.

Answer 2

Application.Transpose is a great way of passing a single column or row range into a 1d variant array. For example:

v = Application.Transpose(Range("A1:A5"))

---

Note that your range is transposed by this function. This means a column range becomes a 1d row variant array, and vica versa, so you might want to transpose it again before you return it to the worksheet:

Range("B1:B5").Value = Application.Transpose(v)

... or alternatively, transpose it twice at the start:

v = Application.Transpose(Application.Transpose(Range("A1:A5")))