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

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

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

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

I looked at the help pages and array sizes appear to be limited by the amount
of memory, however my excel process memory use is only about 17M and I have
about 400MB memory free on the system.
Interestingly I added a break point on the End Function line of the
testdata2 macro and used
= testdata2 (100000)
in the formula bar

At the end function line both BigArray and testdata2 are arrays with 100000
elements in all with correct data. However excel cells are still filled with
#value


"Joel" wrote:

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