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