Returning a RANGE from a UDF

[From Charles: ]

I always wanted to write a UDF that returned a Range rather than the usual things like numbers. It sounded like a cool idea, and so when I was writing the FSUMIFS UDF for my talk at the forthcoming Cambridge UK Excel Users Conference I realised that this was my chance!

(FSUMIFS is a VBA UDF that handles multiple condition SUMs faster than SUMPRODUCT)

I could write a UDF that returned a range (well OK actually for error handling reasons it returns a variant containing a Range Object), and then the user could embed it in any other Excel function that handled a range (SUM, MEDIAN, INDEX, STDEV, MIN, MAX, SMALL …) without needing a special variation of FSUMIFS for each case.

I wrote the FSUMIFS version first, and then did the FRANGEIFS version.
Of course in a lot of cases the returned Range is a multi-area range created by a loop using UNION, which has some disadvantages:
- UNION is quite slow
- Some Excel functions like OFFSET do not work with multi-area ranges.

It all works well and the performance of the current beta version in almost every case so far except for small ranges is substantially (typically 5 to 30 times, up to 100 times in extreme cases) faster than the equivalent ubiquitous SUMPRODUCT formula.

BUT, having done all this work, I am now wondering why return a multi-area range rather when you could just return a (single-area) array (ARRAYIFS)?

ARRAYIFS would probably work just as well if not better (would avoid all those slow UNIONs and problems with function that don’t like multi-area ranges).
 
What do you think the pros and cons of returning Arrays rather than Ranges from UDFs are?

Charles Williams