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.

You know that array's are usually 0 based, and therefore you inflate it by writing in column 1 instead of 0. — Luuklag
– Luuklag, Commented Aug 29, 2017 at 11:09

CallumDA · Accepted Answer · 2017-08-29 12:02:24Z

4

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")))

edited Aug 29, 2017 at 12:02

answered Aug 29, 2017 at 11:27

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zac Over a year ago

Good shout. Nice and simple :)

Patrick Honorez Over a year ago

Great trick I didn't know. Surely faster than looping. This is the best answer IMHO

Community · Accepted Answer · 2018-05-08 21:49:49Z

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.

Collectives™ on Stack Overflow

Reading array from Excel cells creating 2d array

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related