Th eaphersand only works when you use it in VBA, not the worksheet. Because
your arays are starting at zero I think you need to subtract 1 in the code
below. I not sure what the limits are in 2007 fo sizes of arrays. You may
want to check the worksheet help under "specifications and Limits"

Public Function testdata(size As Long) As Variant
Dim BigArray() As Long
Dim i As Long

ReDim BigArray(0 To size - 1, 0 To 1) As Long

For i = 0 To (size - 1)
BigArray(i, 0) = i
Next

testdata = BigArray
End Function
"twaddell" wrote:

If i type
= testdata(65536&)
into the formula bar I get a formula error in excel

So I tried creating a new macro as follows
Public Function BigData () As Variant
BigData = testdata(65536&)
End Function

Upon completing the line with the ampersand VB editor removes the ampersand.
When using the BigData function in excel i still get #Value in all cells

"Joel" wrote:

Try using 65536& with aphersand at the end.

"twaddell" wrote:

In Excel 2007 i am trying to make use of the new row limits and have writen
the following macro

Public Function testdata(size As Long) As Variant
Dim BigArray() As Long
Dim i As Long

ReDim BigArray(0 To size, 0 To 1) As Long

For i = 0 To size
BigArray(i, 0) = i
Next

testdata = BigArray
End Function

I then try to set use the formula in excel. If i use
= testdata(65535)
the data is returned correctly. However as soon as I use 65535 for example
= testdata(65536)
all the cells fill with #Value

Any help would be appreciated as I would like to make use of the 1M row
limit in excel 2007