7

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.

Community
  • 1
  • 1
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • This really depends on what you're doing with the parameter inside the function. The most useful thing I can think of to do with a conceptual operator passed as a parameter is to use it as a callback. For example, perhaps I have a function `get3op2()` which accepts a binary operator, such as `+` or `*`, and returns the result of using that operator on predefined operands, such as `3` and `2`. Then, hypothetically, `get3op2(+)` would return 5 (i.e. 3 + 2) while `get3op2(*)` would return 6 (i.e. 3 * 2). Is that what you're trying to do? – psmay Jan 23 '13 at 15:17
  • Note: an enum typed structure merely contains a list of descriptive constant names as value for Long typed constants. As for what concerns your question, I don't really see the added value of using an enum. As for the question if it's possible to pass an operator directly: it is not in VBA. As for the workaround, yes there is, where eval would be the best option as mentioned. Otherwise than that, I may not understand the real question... – html_programmer Jan 23 '13 at 15:51
  • @Kim there are two questions : `1.` the operators as parameters `2.` the mechanism behind `CountIFS` function's operator evaluation. – bonCodigo Jan 23 '13 at 16:52
  • Without being able to give a straight answer, Office was originally written in C. I suppose some people could guess what happens behind the scenes, perhaps it may be helpful to adapt the tags (?). – html_programmer Jan 23 '13 at 17:27

4 Answers4

3

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.

A. Webb
  • 26,227
  • 1
  • 63
  • 95
  • +1 for the class approach, you didn't have to delete the earlier answer though. For initial stage `Double` is sufficient, but thanks for giving the `Variant`. If one can explain the mechanism taking place behind a function like `CountIfs`, that's great as I am keen on performance aspect. I will test yours along and give a further comment =) – bonCodigo Jan 23 '13 at 16:21
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
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
2

How I would solve the matter

1) Reference the Microsof Script Controler 1.0 library

Option Explicit

Sub Base_Sub()

Dim iNumber1        As Integer
Dim iNumber2        As Integer
Dim iSum            As Integer
Dim iMultiply       As Integer
Dim dDivision       As Double
Dim iDifference     As Integer
Dim lPower          As Long

iNumber1 = 2
iNumber2 = 6

iSum = CalculateThis("+", iNumber1, iNumber2)
iMultiply = CalculateThis("*", iNumber1, iNumber2)
iDifference = CalculateThis("-", iNumber1, iNumber2)
dDivision = CalculateThis("/", iNumber1, iNumber2)
lPower = CalculateThis("^", iNumber1, iNumber2)

End Sub



Public Function CalculateThis(operator As String, iNumber1 As Integer, iNumber2 As Integer)

Dim script As ScriptControl

Set script = New ScriptControl
script.Language = "VBScript"

CalculateThis = script.Eval(iNumber1 & operator & iNumber2)

End Function
html_programmer
  • 18,126
  • 18
  • 85
  • 158
  • This is another way of wrapping the same `Eval` function which was mentioned by both the other answers. Also the Excel `Evaluate`. Nonetheless, would have been better if one had also shown the `logical` and `relational` operators evaluation.... – bonCodigo Jan 23 '13 at 16:48
0

You can pass the operator as a string. For example, if you want to compare a value with those existing in a column of an array using the operators >, <, =, >=, <=

In Sheet1.Range("A2:C11") we have 3 columns of 11 rows with the ranges of the school grades: This is a screenshot

With Sub CallFunction() current values, Inmediate window look like: Row: 6 ; 57,45 > 50

Option Explicit

Sub CallFunction()
'===================================================================================
'Purpose: call the CompareInArray function
'-----------------------------------------------------------------------------------
'Notes:
'prints in the immediate window the result of the comparison
'===================================================================================
    Dim vArray As Variant
    vArray = Sheet1.Range("A2:C11")
    Dim iType As Integer
    iType = 1
    Dim lColumnn As Long
    lColumnn = 2
    Dim vItem As Variant
    vItem = 57.45
    Dim sOperator As String
    sOperator = ">"
    
    Dim ArrRow As Long
    ArrRow = CompareInArray(vArray, iType, lColumnn, vItem, sOperator)
    
    Debug.Print "Row: " & ArrRow & " ; "; vItem & " " & _
      sOperator & " " & vArray(ArrRow, lColumnn)
    
End Sub

Public Function CompareInArray(ByVal vArray As Variant, ByVal iType As Integer, _
  ByVal lColumnn As Long, ByVal vItem As Variant, ByVal sOperator As String) As Long
'===================================================================================
'Purpose: compare vItem with the values in lColumn of vArray
'-----------------------------------------------------------------------------------
'Parameters:
'vArray: array to compare
'iType: 1 if we compare from LBound(vArray) ( > , >= )
'      -1 from UBound(vArray) ( < , <= )
'lColumnn: column where we compare
'vItem: item to compare
'sOperator: comparison operator: >, <, =, >=, <=
'-----------------------------------------------------------------------------------
'Return:
'the first row in the column that satisfies the comparison
'===================================================================================
    Dim Dimensions As Integer
    Dimensions = NumberOfArrayDimensions(vArray)
    
    Dim RowI As Long
    Dim RowF As Long
    Dim Incr As Integer
    If iType = 1 Then
        RowI = LBound(vArray)
        RowF = UBound(vArray)
    Else
        RowI = UBound(vArray)
        RowF = LBound(vArray)
    End If
    
    Dim ArrRow As Long
    For ArrRow = RowI To RowF Step iType
        Dim ActualItem As Variant
        If Dimensions > 1 Then
            'arrays with more than one dimension
            If IsNumeric(vArray(ArrRow, lColumnn)) Then
                ActualItem = CDbl(vArray(ArrRow, lColumnn))
            Else
                ActualItem = vArray(ArrRow, lColumnn)
            End If
        Else
            'arrays with one dimension
            If IsNumeric(vArray(ArrRow, lColumnn)) Then
                ActualItem = CDbl(vArray(ArrRow))
            Else
                ActualItem = vArray(ArrRow)
            End If
        End If
        
        Dim Result As Boolean
        Select Case sOperator
        Case ">"
            Result = vItem > ActualItem
        Case "<"
            Result = vItem < ActualItem
        Case "="
            Result = vItem = ActualItem
        Case ">="
            Result = vItem >= ActualItem
        Case "<="
            Result = vItem <= ActualItem
        End Select
        If Result = True Then
            CompareInArray = ArrRow
            Exit Function
        End If
    Next ArrRow

End Function

Public Function NumberOfArrayDimensions(arr As Variant) As Integer
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' https://stackoverflow.com/a/24613882/19777915
' NumberOfArrayDimensions
' This function returns the number of dimensions of an array. An unallocated dynamic array
' has 0 dimensions. This condition can also be tested with IsArrayEmpty.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Ndx As Integer
Dim Res As Integer
On Error Resume Next
' Loop, increasing the dimension index Ndx, until an error occurs.
' An error will occur when Ndx exceeds the number of dimension
' in the array. Return Ndx - 1.
Do
    Ndx = Ndx + 1
    Res = UBound(arr, Ndx)
Loop Until Err.Number <> 0

Err.Clear

NumberOfArrayDimensions = Ndx - 1

End Function
Andres
  • 19
  • 3