How to pass operators through a UDF

[MrKowz]

I am looking to expand my knowledge of UDFs, and am wanting to know how to write basic conditional statements as an argument in the UDF. Something similar to how SUMIF/COUNTIF works, where I can select a range, and then say it is ">10", "<>Joe", "="&A1, etc.

Thanks in advance!

 

[RoryA]

You'll have to either parse the string argument and specifically evaluate each case, or use Evaluate in your code after building the applicable formula. (I can't honestly say I've ever needed to do this in a UDF.)

 

[shg]

Those would get received by a UDF as a string, and you would have to evaluate them internal to the UDF.

If you had this:

Function myFunc(avb As Variant)

Then called it like this:

=myFunc(myRange=1)

... then avb would be a 2D Variant array of Booleans with the dimensions of myRange.

 

[MrKowz]

Thanks for the reply Rorya. (Edit: And Shg)

Ideally, what I want to do with this is build a solid ConcatinateIf formula that can be used in a variety of applications. I just notice a lot of people posting about needing a formula to "concatenate this if this", and I want to be able to provide a single solution that will work for many users. I have a good working one that is for a direct equality, but I want to add in the capability to say something like "Concatenate if this is greater than that".

I'll play around with the parsing and the evaluate methods to see which ones would work better.

 

[shg]

=CatIf(A1:B10, A1:B10>1)

That gives you a range and an array of Booleans.

 

[MrKowz]

=CatIf(A1:B10, A1:B10>1)

That gives you a range and array of Booleans.

Awesome, that's getting closer. Would there be a way to omit that second A1:B10? Trying to get this to be as user-friendly and as close to native excel formulas as possible. So, ideally, that would be reduced to something like =CatIf(A1:B10,">1")

 

[shg]

You could, but as Rory says, youd have to do the evaluation internal to the UDF.

Also, the second range might be different:

=CatIf(A1:B10, C1:D10="dog")

 

[MrKowz]

Nevermind - the Evaluate method is working great.

I tested it with this, and it seems to be working. Now to integrate it into my code.

Public Function operatortest(rng As Range, teststr As String)
operatortest = Application.Evaluate(rng.Value & teststr)
End Function

 

[MrKowz]

You could, but as Rory says, youd have to do the evaluation internal to the UDF.

Also, the second range might be different:

=CatIf(A1:B10, C1:D10="dog")

There should be no issue with evaluating internally.

Aye, the arguments for this function will be:

=ConcatIf(range_to_look_at, condition_for_range, [range_to_concatenate],[delimiter])

The range_to_concatenate and the delimiter are optional. range_to_concatenate defaults to range_to_look_at if it is empty, and delimiter defaults to "" if it is empty as well.

 

[shg]

operatortest = Application.Evaluate(rng.Value & teststr)

Don't think that's going to work. Maybe this:

operatortest = Evaluate(r.Address(External:=True) & sCond)