Array size limitation?
 

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements) into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't know
why this number?), any number larger than this, the error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in the
following form: vTmp(i,1), which screws up my subsequent
calc's, and I don't really want to go back and recode the
calculation functions.

Thanks in advance

Anh.

Array size limitation?
 

somebody recently mentioned this:

Be aware that application.transpose and application.index fail for some
versions of excel when the number of elements exceeds 5461.
(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index(myArray, 0, 1))
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements) into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't know
why this number?), any number larger than this, the error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in the
following form: vTmp(i,1), which screws up my subsequent
calc's, and I don't really want to go back and recode the
calculation functions.

Thanks in advance

Anh.

Array size limitation?
 

Thanks for your prompt reply! So... I am not going cuckoo
after all.

Once again, Thanks, and is there any suggested work around?

Anh.
-----Original Message-----

somebody recently mentioned this:

Be aware that application.transpose and application.index
fail for some
versions of excel when the number of elements exceeds
5461.
(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index
(myArray, 0, 1))
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements)
into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't
know
why this number?), any number larger than this, the
error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in
the
following form: vTmp(i,1), which screws up my
subsequent
calc's, and I don't really want to go back and recode
the
calculation functions.

Thanks in advance

Anh.


.

Array size limitation?
 

Hi Anh,

You could always copy the elements of the two-dimensional array into a
one-dimensional array.

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

"QuocAnh" wrote in message
...
Thanks for your prompt reply! So... I am not going cuckoo
after all.

Once again, Thanks, and is there any suggested work around?

Anh.
-----Original Message-----

somebody recently mentioned this:

Be aware that application.transpose and application.index
fail for some
versions of excel when the number of elements exceeds
5461.
(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index
(myArray, 0, 1))
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements)
into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't
know
why this number?), any number larger than this, the
error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in
the
following form: vTmp(i,1), which screws up my
subsequent
calc's, and I don't really want to go back and recode
the
calculation functions.

Thanks in advance

Anh.


.

Array size limitation?
 

This lays out the limits pretty well:

http://support.microsoft.com/?id=177991
XL: Limitations of Passing Arrays to Excel Using Automation (Q177991)

--
Regards,
Tom Ogilvy

"keepitcool" wrote in message
...

somebody recently mentioned this:

Be aware that application.transpose and application.index fail for some
versions of excel when the number of elements exceeds 5461.
(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index(myArray, 0, 1))
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements) into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't know
why this number?), any number larger than this, the error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in the
following form: vTmp(i,1), which screws up my subsequent
calc's, and I don't really want to go back and recode the
calculation functions.

Thanks in advance

Anh.

Array size limitation?
 

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Dim arr
arr = Range("A1:A10000")
arr = MakeArray(ArrayTranspose(arr), 1)

Alan Beban

QuocAnh wrote:
Thanks for your prompt reply! So... I am not going cuckoo
after all.

Once again, Thanks, and is there any suggested work around?

Anh.

-----Original Message-----

somebody recently mentioned this:

Be aware that application.transpose and application.index

fail for some

versions of excel when the number of elements exceeds

5461.

(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index

(myArray, 0, 1))

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:


Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements)

into

a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't

know

why this number?), any number larger than this, the

error

message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in

the

following form: vTmp(i,1), which screws up my

subsequent

calc's, and I don't really want to go back and recode

the

calculation functions.

Thanks in advance

Anh.


.

Array size limitation?
 

In fact, it's a bit simpler:

Dim arr
arr = Range("A1:A10000")
arr = MakeArray(arr, 1)

Alan Beban

Alan Beban wrote:
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook

Dim arr
arr = Range("A1:A10000")
arr = MakeArray(ArrayTranspose(arr), 1)

Alan Beban

QuocAnh wrote:

Thanks for your prompt reply! So... I am not going cuckoo after all.

Once again, Thanks, and is there any suggested work around?

Anh.

-----Original Message-----

somebody recently mentioned this:

Be aware that application.transpose and application.index


fail for some

versions of excel when the number of elements exceeds


5461.

(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant
'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index


(myArray, 0, 1))

End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:


Hello All,

I have a strange situation and I hope that this is the appropriate
forum.

I want to extract a column of data (10,000 elements)


into

a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't


know

why this number?), any number larger than this, the


error

message is: "Type mismatch"

Has anyone ran across such a situation? and how did you get around it?

fyi, I know that I can get all 10,000 elements if I use the
following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in


the

following form: vTmp(i,1), which screws up my


subsequent

calc's, and I don't really want to go back and recode


the

calculation functions.

Thanks in advance

Anh.


.

Array size limitation?
 

A simpler possibility via the Evaluate Method:

Dim arr As Variant
arr = [transpose(A1:A10000)]

Regards,
Nate Oliver

-----Original Message-----
In fact, it's a bit simpler:

Dim arr
arr = Range("A1:A10000")
arr = MakeArray(arr, 1)

Alan Beban

Array size limitation?
 

Apparently this doesn't work in the OP's version. See the link in Tom
Ogilvy's post in this thread.

Alan Beban

Nate Oliver wrote:
A simpler possibility via the Evaluate Method:

Dim arr As Variant
arr = [transpose(A1:A10000)]

Regards,
Nate Oliver


-----Original Message-----
In fact, it's a bit simpler:

Dim arr
arr = Range("A1:A10000")
arr = MakeArray(arr, 1)

Alan Beban

Array size limitation?
 

Hello again,

I believe there's a difference in what the OP is
attempting, versus what I'm saying might work. I'm using
2000, which has the same constraints per Tom's link as '97.

Initially I had written this off as the Evaluate Method
miracle, but your response prompted me to reconsider this
some more. Evaluate upon further consideration does not
seem to be the deciding factor here and it will fail with
a data-type array as well.

I am using xl 2000, so f, g & h from Tom's posts apply,
and as I understand it now, this is correct, a data-type
array greater than 5461 elements is not transposable via
WorksheetObject nor the Evaluate method. But, it seems
the same software can transpose more that 5461 range
objects, which it then coerces into a variant data-type
array. So with respect to the original post, in xl 2000 I
see the following:

Sub Does_Not_Work_Xl2000()
Dim rTmp1 As Range
Dim vTmp1() As Variant
Set rTmp1 = Range(Cells(1, 1), Cells(5462, 1))
vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)
End Sub

Sub Does_Work_Xl2000()
Dim rTmp1 As Range
Dim vTmp1() As Variant
Set rTmp1 = Range(Cells(1, 1), Cells(5462, 1))
vTmp1 = Application.WorksheetFunction.Transpose(rTmp1)
Debug.Print rTmp1(5462)
End Sub

The deciding factor being rTmp1 vs. rTmp1.Value. I also
see the following:

Sub No_Work1()
Dim arr As Variant
arr = Range("a1:a10000")
arr = Application.WorksheetFunction.Transpose(arr)
End Sub

Sub No_Work2()
Dim arr As Variant
arr = Range("a1:a10000")
arr = Evaluate("transpose(" & arr & ")")
End Sub

Sub No_Work3()
Dim arr As Variant
arr = Evaluate("transpose(" & Range("a1:a10000").Value2
& ")")
End Sub

Sub Work1()
Dim arr As Variant
arr = Evaluate("transpose(a1:a10000)")
End Sub

Sub Work2()
Dim arr As Variant
arr = [transpose(a1:a10000)]
End Sub

Sub Work3()
Dim arr As Variant
arr = WorksheetFunction.Transpose(Range("a1:a10000"))
End Sub

And:

Sub Does_Not_Work_Xl2000()
Dim Rng As Variant, arr As Variant
Dim Lst As Long
Lst = 10000
Rng = Evaluate("row(1:" _
& Lst & ")/row(1:" & Lst & ")")
arr = Application.Transpose(Rng)
Debug.Print UBound(arr) & ": " & _
arr(UBound(arr))
End Sub

Sub Does_Work_Xl2000()
Dim Rng As Variant, arr As Variant
Dim Lst As Long
Lst = 10000
Rng = Evaluate("transpose(row(1:" _
& Lst & ")/row(1:" & Lst & "))")
arr = Rng
Debug.Print UBound(arr) & ": " & _
arr(UBound(arr))
End Sub

For the same reasons. So if you want to populate an array
with transpose range object values and you want to
overcome the 5461 limit in xl 2000, transpose the range in
a single step before it gets coerced into a variant data-
type array.

Have a nice weekend.

Regards,
Nate Oliver

-----Original Message-----
Apparently this doesn't work in the OP's version. See the
link in Tom Ogilvy's post in this thread.