Is there a way to pass arithmetic and logical operator as a method parameter in VBA?

Question

Came across couple of scenarios that one would want to pass operators as a parameter in a function or a method. According to this post Java doesn't have that ability, hence need to create an Enum as the primary workaround.

E.g.

Function doCalcs(ByRef AND as LogicalOperator, ByRef greater ArithmeticOperator)

Although VBA has much lesser libraries compared to .Net, Java, creating Enum is well supported. Perhaps I am not aware, so if there's a possibility of that VBA has operator types or any other workarounds we could pass an operator, shoot that in. (other than if else/case to check a string that contains the operator paramter.. =) ) What I am asking is different from what's mentioned here.

• Question is asked in terms of reducing codes, optimization.

E.g. If you look at CountIFS, it has the ability to take in operators. Even if someone can explain the possible back-end work within this function,

1. How does it convert these strings into a proper operator?
2. Is that an Enum structure or anything more efficient than that or lesser than that?

An answer to these questions are still acceptable.

Answer 1

An approach using classes:

Define a interface class

Op

Public Function eval(operand1, operand2)
End Function

For each desired operator, define a implementation. For example

OpMinus

Implements Op

Private Function Op_eval(operand1 As Variant, operand2 As Variant) As Variant
    Op_eval = operand1 - operand2
End Function

OpPlus

Implements Op

Private Function Op_eval(operand1 As Variant, operand2 As Variant) As Variant
    Op_eval = operand1 + operand2
End Function

Now some test routines in a module:

Sub test()
    Dim Minus As New OpMinus
    Dim Plus As New OpPlus
    Dim o, v1, v2

    For Each o In Array(Minus, Plus)
        For Each v1 In Array(1, 2, 3)
            For Each v2 In Array(1, 2, 3)
                operate o, v1, v2
            Next
            Debug.Print ""
        Next
        Debug.Print ""
    Next
End Sub

Sub operate(ByVal operator As Op, operand1, operand2)
    Debug.Print operator.eval(operand1, operand2),
End Sub

Output:

 0            -1            -2            
 1             0            -1            
 2             1             0            

 2             3             4            
 3             4             5            
 4             5             6            

Note you may wish to use e.g. Double instead of Variant in the interface if you know on which type you'll be operating.

Answer 2

There is no LogicalOperator type, ArithmeticOperator type, or anything similar. About the closest you can come is to use the Eval function in MS Access VBA or the (similar but different) Evaluate function in Excel VBA.

In fact, you've probably used the Evaluate function in Excel without even realizing it. From the Excel help file:

Note Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument. For example, the following expression pairs are equivalent.

[a1].Value = 25
Evaluate("A1").Value = 25