How to pass operators through a UDF

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,814
Office Version
  1. 2010
Platform
  1. Windows
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:

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.
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,814
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

That gives you a range and an array of Booleans.
 
Last edited:

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
=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

MrExcel MVP
Joined
May 7, 2008
Messages
21,814
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,814
Office Version
  1. 2010
Platform
  1. Windows
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)
 

Forum statistics

Threads
1,141,730
Messages
5,708,147
Members
421,549
Latest member
Dtcfire

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top