# How to pass operators through a UDF

#### MrKowz

##### Well-known Member
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.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.)

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

Code:
``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.

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.

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

That gives you a range and an array of Booleans.

Last edited:
=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")

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")

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.
Code:
``````Public Function operatortest(rng As Range, teststr As String)
operatortest = Application.Evaluate(rng.Value & teststr)
End Function``````

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.

Code:
``operatortest = Application.Evaluate(rng.Value & teststr)``
Don't think that's going to work. Maybe this:
Code:
``operatortest = Evaluate(r.Address(External:=True) & sCond)``

Replies
1
Views
180
Replies
2
Views
310
Replies
5
Views
677
Replies
3
Views
581
Replies
4
Views
359

1,221,522
Messages
6,160,308
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back