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