Populating an array from a range

[barryleajo]

I need a push to get started on learning array techniques please.
In the trivial example below I am trying to read a few values into a single dimensional array from a range.
My Debug Report 1 reports Lbound as 1 and Ubound as 7 as expected.
However after my assignment from Range to Array (?)
My Debug Report 2 reports LBound as 1 and Ubound as 1... and no values!
What am I missing?
The code runs without error.
I don't want to specify the range in A1 format and I don't want to use a named range.
With thanks

Please Login or Register  to view this content.

[Colin Legg]

Hi Barry,

First, a small error in the code here:

Please Login or Register  to view this content.

The Cells properties need to be qualified in the same way as the Range property, otherwise, assuming your code is in a standard code module and if Sheet1 is not the active sheet, you will get an error.

Please Login or Register  to view this content.

Since num is 7 in your example, the range you are picking up the values from is Sheet1!B9:G9. If, as in this case, the range is more than one cell, then you will get a two dimensional array of values. Illustration:

Please Login or Register  to view this content.

The syntax of the LBound (and UBound) functions is as follows:

LBound(arrayname[, dimension])


dimension

Optional; Variant (Long). Whole number indicating which dimension's lower bound is returned. Use 1 for the first dimension, 2 for the second, and so on. If dimension is omitted, 1 is assumed

In your code you are using this:

Please Login or Register  to view this content.

So you are omitting the optional dimension parameter, thus it is assumed you want to return the lower or upper bound from the first dimension. As previously mentioned, the range you are picking the values from is Sheet1!B9:G9, so your tmparr variable actually has these dimesions once the values are assigned to it:

Please Login or Register  to view this content.

It is the second dimension you want to view the Lower and Upper bounds from so, in this particular case, you would want:

Please Login or Register  to view this content.

[barryleajo]

Hi Colin
Very many thanks for your response and your very careful and clear explanation.
Qualifying .Cells noted, should have spotted this.
Clearly my mental map of a one-dimensional array was not correct.
I doubt that I would have 'experimented' and discovered this.
I now feel that I understand the structure and how to work with it.
I will definitely be able to move on with this now.
SOLVED sir and thank you.
Barry