I understand that the limits on the number of elements of an array that
can be stored in a Name was 256 columns and 5461 elements in xl2000. In
xl2002 it seems to remain 256 columns, but the number of elements seems
to be upwards of 1,000,000. Is there some source I can go to for the
number of elements in 2002, and what the limits are in current versions?

Thanks,
Alan Beban

hi
not sure if this will help but in xl help type the word specifications. this
will give you the specs and limits of your version of xl. i think arrays are
under calculation specifications.

Regards
FSt1

"Alan Beban" wrote:

I understand that the limits on the number of elements of an array that
can be stored in a Name was 256 columns and 5461 elements in xl2000. In
xl2002 it seems to remain 256 columns, but the number of elements seems
to be upwards of 1,000,000. Is there some source I can go to for the
number of elements in 2002, and what the limits are in current versions?

Thanks,
Alan Beban

FSt1 wrote:
hi
not sure if this will help but in xl help type the word specifications. this
will give you the specs and limits of your version of xl. i think arrays are
under calculation specifications.

Regards
FSt1

"Alan Beban" wrote:


I understand that the limits on the number of elements of an array that
can be stored in a Name was 256 columns and 5461 elements in xl2000. In
xl2002 it seems to remain 256 columns, but the number of elements seems
to be upwards of 1,000,000. Is there some source I can go to for the
number of elements in 2002, and what the limits are in current versions?

Thanks,
Alan Beban

No help, but thanks for responding.

Alan Beban

Hi Alan,

The 5461 element limit in Xl2000 does not relate to Names but the number of
elements that can be handled by certain worksheet functions, though it also
limits say 'application.transpose' in vba in early versions.

I'm not aware of any difference in limits for Names between xl97-2003. As
you say the limit is 256 elements in a horizontal array (actually from
memory I thought 255), but many tens of thousands in a vertical array are
certainly possible (even in xl97). I've not tested the limit for vertical
elements in a Named array but would have guessed it's either 65536 or one
less than the number of rows, ie 65535, an inconvenience which also affects
certain worksheet functions.

As I say, I've not tested and you say you've defined a Named array of
1,000,000 elements, I assume that no single column had more than 65k
elements, ie rows (?), if so I would be surprised. Until thoroughly testing
I'd be nervous about relying on such a large Named array. If such a name
does prove reliable and manageable I suppose the practical limit would be
determined by system resources.

Note you can only create/edit a Named array programmatically (not
straightforward) if the total length of the array exceeds 255 characters.

Regards,
Peter T


"Alan Beban" wrote in message
...
I understand that the limits on the number of elements of an array that
can be stored in a Name was 256 columns and 5461 elements in xl2000. In
xl2002 it seems to remain 256 columns, but the number of elements seems
to be upwards of 1,000,000. Is there some source I can go to for the
number of elements in 2002, and what the limits are in current versions?

Thanks,
Alan Beban

Peter T wrote:
Hi Alan,

The 5461 element limit in Xl2000 does not relate to Names but the number of
elements that can be handled by certain worksheet functions . . . .

Sub abtest5()
Dim arr
x = 2730
'x = 2731
ReDim arr(1 To x, 1 To 2)
For i = 1 To x: For j = 1 To 2
arr(i, j) = i * j
Next: Next
Names.Add Name:="test", RefersTo:=arr
a = [test]
Debug.Print a(x, 1)
End Sub

In xl2000, the above returns 5460 for x = 2730; for x = 2731 it returns
an error message:

Application-defined or object-defined error

So the 5461 element limit in xl2000 does indeed seem to relate to the
number of elements stored in a Name.


As
you say the limit is 256 elements in a horizontal array (actually from
memory I thought 255). . .

In xl2000, the following returns 256 for x=256, an error message for x =
257:

Sub abtest6()
x = 256
'x = 257
Dim arr
ReDim arr(1 To x)
For i = 1 To x
arr(i) = i
Next
Names.Add Name:="test", RefersTo:=arr
a = [test]
Debug.Print a(x)
End Sub

, but many tens of thousands in a vertical array are
certainly possible (even in xl97). . . .

No; in xl2000 (so I assume xl97), the following returns 5461 for x=5461,
and an error message for x = 5462:

Sub abtest7()
Dim arr
'x = 5461
x = 5462
ReDim arr(1 To x, 1 To 1)
For i = 1 To x: For j = 1 To 1
arr(i, j) = i * j
Next: Next
Names.Add Name:="test", RefersTo:=arr
a = [test]
Debug.Print a(x, 1)
End Sub

I'm not aware of any difference in limits for Names between xl97-2003.

In xl 2002, the above 5461 element limit in a Name does not apply. The
limits are 256 columns and apparently 65536 rows, but I only tested to
65536 rows and 30 columns for 1,966,080 elements; when I tried 65536x256
I got a resource limitation.

. . .
Note you can only create/edit a Named array programmatically (not
straightforward) if the total length of the array exceeds 255 characters.

I'm not understanding you here. What do you mean by "the length of the
array"? And I'm not sure what you mean by "create/edit a Named array
programmatically". The above examples (that worked) all programmatically
created a Name and stored the array in it, and would certainly work on a
10x10 array.

Regards,
Peter T

Thanks for responding,
Alan

I would quite like to expunge my previous post from the archives! Looks like
I need to chase my memory, apologies for the misinformation concerning the
5461 limit with names.

Ironically, I have an addin that creates large named arrays, to quote from
the documentation (written a long time ago) -

"In Excel versions 97 and 2000 De-link to Named Arrays will fail to process
if more than 5461 points exist in any Series. The maximum number of points
limit has not been tested in later versions"

FWIW, the addin converts chart series data to named arrays as one method to
divorce (de-link) chart data from cells.

Note you can only create/edit a Named array programmatically (not
straightforward) if the total length of the array exceeds 255
characters.

I'm not understanding you here.
What do you mean by "the length of the array"?

={1;2;3;4;5}
This (vertical) array has 5 elements and is what you'd view in the refersto
box in if you press Ctrl-F3. It's total length including punctuation is 11
characters, 12 incl the '='.

You can create and/or edit such an array manually providing the "total
length" is less than 255 characters. That's an absolute max, I'm confident
length 230 will always work but 240 might fail. Any attempt to edit a named
array with a longer 'total length' will result in it becoming irreparably
corrupted.

Regards,
Peter T

In mitigation, in this post I described the same 5461 limitation with named
arrays in early versions (said similar in other posts too).

http://tinyurl.com/2km9ow

Peter T

Peter T wrote:
In mitigation, in this post I described the same 5461 limitation with named
arrays in early versions (said similar in other posts too).

http://tinyurl.com/2km9ow

Peter T


Hmmm, Sept 2006. Don't know whether that's a short term or long term
memory problem, but in the perfect world of the future, maybe we won't
lose any of it :-)

Alan