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


One Response to “Returning a RANGE from a UDF”

  1. dougaj4 Says:

    Charles – In general I like to convert worksheet range values into an array for use in VBA, then in a UDF I just finish with UDFname = Arrayname, having declared the UDF as a variant.

    You might find these comments from Daily-DoseofExcel interesting:

    Also I’ve just posted some stuff on arrays and ranges on my new Blog, which is fairly basic, but you might like to look anyway:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: