Size limit on transferring Variant to Range in Excel 2007

[nu1]

As part of a VBA function I am outputting a large array by implicitly converting an array to a variant which I then output in an array formula. Although I am using Excel 2007, I seem to get #VALUE! errors when I dimension this with any more than 65,536 rows (also when I dimension using more than 65536 columns but only output part of the array so that it fits on the worksheet I also get these errors). I attach the code below. Is this a limit in Excel, or is there a workround?

Thanks for anyone who might be able to help

Function genarray(rows As Long, columns As Long) As Variant
Dim r As Long
Dim c As Long
ReDim x(1 To rows, 1 To columns) As Double
For r = 1 To rows
For c = 1 To columns
x(r, c) = r + c
Next c
Next r
genarray = x
End Function

 

[pgc01]

Hi nu1
Welcome to the board

I just used your code and had no problem.

I tested your code with:

Sub Test()
Range("A1").Resize(100000, 100).Value = genarray(100000, 100)
End Sub

Please try it.

 

[nu1]

Thanks very much for this, pgc01 - it works brilliantly.

I'm assuming that there must be a limit on the size of the variant which can be returned when you enter the function directly into a range via an array formula - what I had tried to do was to highlight a range (say) 100000 rows * 100 cols (or even just a small range of 30 rows * 30 cols - assuming that a small subset of the array would be returned in this case) and put the formula genarray(100000,100) in and hit Ctrl-Alt-Enter, but whatever the size of the range I highlighted to receive the array formula, I got back an error unless the parameters I supplied to genarray were less than 65K.

Your solution has helped me enormously - thanks very much.

 

[pgc01]

I'm glad it helped.

You are right. If you want to use the function as a UDF, calling it directly from the worksheet in an array formula, you do get that limitation in terms of the number of rows of the array.

Remark:

... by implicitly converting an array to a variant ...

Notice that you only have to do that if you want to be compatible with the MAC. If you are using windows you can use a typed array.

For ex., in your example you are working with Longs:

Function genarrayL(rows As Long, columns As Long) As Long()
Dim r As Long
Dim c As Long
 
ReDim x(1 To rows, 1 To columns) As Long
 
For r = 1 To rows
    For c = 1 To columns
        x(r, c) = r + c
    Next c
Next r
genarrayL = x
End Function

 

[nu1]

Thanks once again - another great tip. Why don't they tell you that in the books?! Or maybe I'm just reading the wrong books... Is there a book you'd recommend which covers this sort of stuff at this level - most of the things I do involve interfacing with DLLs and optimising array/loop type calculations and basic range/array outputs rather than exploring all the other things (charts, etc) which you can control from VBA (and which in my experience most books tend to move onto quite quickly without going through the real detail on the basics).

 

[Richard Schollar]

I don't think there's one specific resource for what you want - the stuff on typed arrays (and lots of other VBA specific but not necessarily Excel specific) can be found in the VBA Developer's Handbook. Other useful stuff in Excel 2007 Programmer's Reference.

Otherwise, this (and other) forum(s) are your best bet.

 

[nu1]

Thanks Richard - much appreciated.

 

[snowblizz]

Thanks once again - another great tip. Why don't they tell you that in the books?!

Because they didn't figure you needed to know about it!

That's one reason books often fail to give you answers and why I'm investigating the ins and outs of on-line forums as a method of support.

Another thread going straight into the thinking pot.