Worksheet names to variant arrays

[Alasdair Stirling]

I have a worksheet with the numbers 1 to 8 in range d8:g9 and have named
range d8:g8 "SheetNumbers" and d8:g9 "SheetNumbers2". I have also defined
two workbook names as follows: "NameNumbers" ={1,2,3,4} and "NameNumbers2"
={1,2,3,4;5,6,7,8}.

I have a vba procedure as follows:

Sub alpha()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers2")
Y = [NameNumbers2]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 2
1 2

So far so good, but I also have another procedure as follows:

Sub bravo()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers")
Y = [NameNumbers]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 1
1 4

For some reason Excel seems to interpret NameNunbers2 as an array of 2 rows
and 4 columns, but interprets NameNumbers as a single column array of 4 rows.
Does anybody know why?

 

[Bob Phillips]

Because that is the way you created them. By adding a semi-colon after the 4
in NameNumbers2, you created a second dimension to the array.

 

[Alasdair Stirling]

The question is why does excel interpret NameNumbers as a single column array
of 4 rows, rather than an array of 1 row and foru columns?

Because that is the way you created them. By adding a semi-colon after the 4
in NameNumbers2, you created a second dimension to the array.

--
HTH

Bob Phillips

I have a worksheet with the numbers 1 to 8 in range d8:g9 and have named
range d8:g8 "SheetNumbers" and d8:g9 "SheetNumbers2". I have also defined
two workbook names as follows: "NameNumbers" ={1,2,3,4} and "NameNumbers2"
={1,2,3,4;5,6,7,8}.

I have a vba procedure as follows:

Sub alpha()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers2")
Y = [NameNumbers2]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 2
1 2

So far so good, but I also have another procedure as follows:

Sub bravo()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers")
Y = [NameNumbers]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 1
1 4

For some reason Excel seems to interpret NameNunbers2 as an array of 2 rows
and 4 columns, but interprets NameNumbers as a single column array of 4 rows.
Does anybody know why?

 

[Tom Ogilvy]

It interprest NameNumbers as a 1 dimensional array. You are interpreting
it as four rows. Excel interprets it as 4 columns (A horizontal array is
the common term).

VBA doesn't care. It sees it as a 1 dimensional array and a one dimensional
array has neither rows nor columns - just 4 elements.

To see Excels interpretation, select A1:A4 and enter

=NameNumbers
Ctrl+shift+Enter

and you will see a column of 1's (the first element of the array listed 4
times)

Select B1:E1 and do the same. You will see 1 2 3 4

--
Regards,
Tom Ogilvy

The question is why does excel interpret NameNumbers as a single column array
of 4 rows, rather than an array of 1 row and foru columns?

Because that is the way you created them. By adding a semi-colon after the 4
in NameNumbers2, you created a second dimension to the array.

--
HTH

Bob Phillips

message news:[email protected]...

I have a worksheet with the numbers 1 to 8 in range d8:g9 and have named
range d8:g8 "SheetNumbers" and d8:g9 "SheetNumbers2". I have also defined
two workbook names as follows: "NameNumbers" ={1,2,3,4} and "NameNumbers2"
={1,2,3,4;5,6,7,8}.

I have a vba procedure as follows:

Sub alpha()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers2")
Y = [NameNumbers2]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 2
1 2

So far so good, but I also have another procedure as follows:

Sub bravo()

Dim X As Variant, Y As Variant

X = Sheet1.Range("SheetNumbers")
Y = [NameNumbers]

Debug.Print LBound(X, 1); UBound(X, 1)
Debug.Print LBound(Y, 1); UBound(Y, 1)

End Sub

That produces the following result in the intermediate window:

1 1
1 4

For some reason Excel seems to interpret NameNunbers2 as an array of 2 rows
and 4 columns, but interprets NameNumbers as a single column array of

4
rows.

Does anybody know why?