Worksheet names to variant arrays

  • Thread starter Alasdair Stirling
  • Start date
A

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?
 
B

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.
 
A

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?

Bob Phillips said:
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

Alasdair Stirling said:
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?
 
T

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



Alasdair Stirling said:
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?

Bob Phillips said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top