Array size limitation?

  • Thread starter Thread starter QuocAnh
  • Start date Start date
Q

QuocAnh

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.
 
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 >
 
Thanks for your prompt reply! So... I am not going cuckoo
after all.

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

Anh.
 
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
 
In fact, it's a bit simpler:

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

Alan Beban
 
A simpler possibility via the Evaluate Method:

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

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

Alan Beban

Nate said:
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
Click to expand...
Click to expand...
 
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
Click to expand...
link in Tom Ogilvy's post in this thread.
 
Back
Top