Return an Excel Array Constant from VBA User-Defined Function

Question

I have a spreadsheet like this:

A1: APPLE
A2: BANANA
A3: ORANGE
A4: APPLE
A5: BANANA
A6: ORANGE

(This repeats until A20)

So this formula would count all instances of "APPLE":

=COUNTIF(A1:A20, "APPLE")

And This formula does the same, except with a non-contiguous range:

=SUM(COUNTIF(INDIRECT({"A1:A6", "A8:A20"}), "APPLE"))

The INDIRECT function is taking an array constant with the ranges defined as strings. Note the curly braces and the quotes around each range. (That's based on the technique described here.)

However, if I define a VBA function that returns an array of strings and pass that into INDIRECT, it seems that only the first range is respected.

This is my VBA function:

Function TestFn() As Variant
    TestFn = Array("A1:A6", "A8:A20")
End Function

This is my formula:

=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))

If I use Excel's "Evaluate Formula" button, I can see that TestFn() resolves to a single string "B1:B6". However, if I use the VBA debugger, I see that both strings are in there.

Expression      Value       Type
TestFn                      Variant/Variant(0 to 1)
 - TestFn(0)    "B1:B6"     Variant/String
 - TestFn(1)    "B8:B20"    Variant/String

How can I return an array of strings from an Excel VBA function that can be passed into INDIRECT in the same manner?

I think this comes down to: what internal object is created by using curly braces when constructing the formula without VBA, and can I create that same internal object from within VBA?

I've also tried returning an array of strings, rather than a variant containing a string array.

Function TestFn() As String()
    TestFn = Split("B1:B6,B8:B20", ",")
End Function

These are the types in the debugger:

Expression      Value       Type
TestFn                      String(0 to 1)
 - TestFn(0)    "B1:B6"     String
 - TestFn(1)    "B8:B20"    String

But the result is the same. I think I need to use some type of container type, but I'm not sure what to use or how to figure out what to use.

I don't actually need to use the SUM...INDIRECT hack if there is a better way, and I'd be happy to use an array formula if that's appropriate.

If I could let my function return ranges directly, rather than strings representing ranges, that would be even better than my current approach.

If I could set up an array formula something like this (doesn't have to be exactly like this):

{=COUNTIF(TestFn(), "APPLE")}

Then let my UDF look something like this:

Function TestFn() As Range()
    TestFn = Array(Range("A1:A6"), Range("A8:A20"))
End Function

Or maybe something like this:

Function TestFn() As Range
    TestFn = Union(Range("A1:A6"), Range("A8:A20"))
End Function

That would suit my needs.

The key here is that I just want my VBA to only define which cells are operated on, not what the operation is, and i want to deal with non-contiguous ranges. I want the actual operations to be defined in the excel formulas, because these will be modified by people who aren't expected to understand how to read and write VBA code. (Heck, I don't want to ever look at VBA code again either.) In this case, I'm using COUNTIF, but my VBA function will be used with other excel formula functions as well.

in case anybody is wondering "why?": my real data is in a filter table. the VBA function will take a range as input and return the subset of that range that is currently visible (i.e. not filtered out). i want to use as little VBA as possible and keep most of the logic in excel. i will be using the SUM...INDIRECT trick with a few different excel functions, not just COUNTIF, so simply making a "variant" of COUNTIF isn't very sustainable. — Woodrow Barlow
Did you enter =SUM(COUNTIF(INDIRECT(TestFn()), "APPLE")) as an array formula with Ctrl+Shift+Enter? Because that works for me with your original version of TestFn. — BigBen
The difference is that you need to enter it as array formula in excel sheet (it will pick up both ranges) and in vba, if you would use .evaluate this same formula without curly brackets in vba it is automatically recognized as being an array formula. — JvdV
@BigBen i didn't, just as a normal formula. i didn't know array formulas exist. i'm going to read up on array formulas and see if they fit my purpose. however, i'm still curious about why the non-VBA formula works as a normal formula and whether i can replicate that with VBA. — Woodrow Barlow
@JvdV i'm trying to understand what you're describing but i'm afraid it has gone over my head. — Woodrow Barlow

JvdV JvdV · Accepted Answer · 2019-05-16T16:53:21

Surely someone can explain this better, but let me try. You have used both:

=SUM(COUNTIF(INDIRECT({"A1:A6","A8:A20"}), "APPLE"))

And...

=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))

About the first formula; you have basically used the INDIRECT() functions to translate text strings into a real range. It's actually a very nice use of INDIRECT() I must say :).

But doing so using the curly brackets you told Excel that it is more than just one string, you have fed the function with multiple data, an array! Typically , an array in Excel is a simple set of data. This data can be text, numbers, or both. You have used text.

In the second formula you have fed the formula with an array through an UDF but it's lacking the curly brackets. Excel doesn't know you want to compare multiple ranges and will only evaluate the first item in your array.

However using CtrlShiftEnter you telling Excel you want to feed the formula with an array, being a set of data/ranges to compare.

So:

{=SUM(COUNTIF(INDIRECT(TestFn()), "APPLE"))}

Will work :)

I'm sure someone else is better in explaining ;)

Return an Excel Array Constant from VBA User-Defined Function

1 Answers