How to create function to take indefinite number of arguments but by pairs?

Question

I'm thinking of creating a VBA function similar to IFS formula wherein you could pass indefinite number of arguments but the second argument is always required.

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)

The requirement is to compare certain values and if all are the same then output True, otherwise False.

Let's say we have this table.

Item ID | Price | Description
1001    | 10.00 | Item A
1002    | 10.00 | Item A
1003    | 25.00 | Item A
1004    | 10.00 | Item B

And if I need to do something on certain items, I would simply call the function like this:

Function(criteria1, value1, criteria2, value2, ...)

' Mark items with Price = 10.00 and Description = Item A
Dim IsAdd As Boolean: IsAdd = FnCheckData(strCriteria2, "10.00", strCriteria3, "Item A")

' Mark items with Item ID = 1002
Dim IsAdd As Boolean: IsAdd = FnCheckData(strCriteria1, "1002")

' Mark items with Item ID = 1003, Price = 25.00 and Description = Item A
Dim IsAdd As Boolean: IsAdd = FnCheckData(strCriteria1, "1003", strCriteria2, "25.00", strCriteria3, "Item A")

' This would error out since the second argument is not supplied
Dim IsAdd As Boolean: IsAdd = FnCheckData(strCriteria1)

Is this possible? If so, how? Thanks in advance!

You possibly need [this](https://stackoverflow.com/questions/2630171/variable-number-of-arguments-in-vb) — Storax, Sep 18 '18 at 10:17
I covered this in a TEXTJOINIFS UDF [here](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050?s=1|85.1472#50719050). — , Sep 18 '18 at 10:29

score 3 · Accepted Answer · answered Sep 18 '18 at 10:28

3

Your function could look like that

Function NeedEvenNumber(ParamArray vStrings()) As Boolean

    On Error GoTo EH

    If IsMissing(vStrings) Then
        Debug.Print "Nothing passed"
    End If

    If (UBound(vStrings) Mod 2) - 1 = 0 Then
        Debug.Print "Correct number of params passed"
    End If

    Exit Function

EH:
    MsgBox "Something went wrong", vbOKOnly, "Error"


End Function

answered Sep 18 '18 at 10:28

Storax

11,158
3
16
33

1

Turn the return into a `Variant` and you can return `CVErr(xlErrNum)` (**#NUM!**) on errors. [CVErr function](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/cverr-function) – Darren Bartrup-Cook Sep 18 '18 at 10:44

How to create function to take indefinite number of arguments but by pairs?

1 Answers1