CODEWITHSUNDEEP
vbavariablesdynamicoperator-keyword
Johnson Jason
Johnson Jason

Reputation: 721

Dynamic operator in VBA. How can I?

How can I create a dynamic operator using VBA?

swt = True
op = IIf(swt = True, "<", ">")
a = 10
B = 20
IF a op B then
MsgBox ("a is greater than B")
End If

Obviously this fails but can anyone make it work?

Upvotes: 5

Views: 3975

Answers (4)

John Coleman
John Coleman

Reputation: 52008

As I said in the comments and others have mentioned as well, VBA syntax doesn't allow you to define new infix operators.

You never said what you wanted to do this for, but I couldn't help but think that you had a custom sort in mind where a comparison operator is passed to a sort sub. Think about how such things are handled in C (which also doesn't allow you to define new infix operators). The standard library has an implementation of qsort which needs to be passed a pointer to a comparison function. You can do something similar in VBA.

VBA lacks function pointers -- but Application.Run (which is in some ways more flexible than Application.Evaluate) allows you to use the name of the function almost as if it is a pointer. For example, say you write a compare function that takes to doubles and either returns the larger of them or the smaller of them, depending on the state of a global parameter, like thus:

Public AZ As Boolean 'a global sort-order flag

Function compare(x As Double, y As Double) As Boolean
    compare = IIf(AZ, (x < y), (y < x))
End Function

To illustrate how Application.Run allows you to pass a custom compare function, I wrote a function which takes the name of a compare function as well as an array, and returns the element of the array which is largest according to the sort order determined by the compare function. The compare function can be rather arbitrary, e.g. it could return the tree of greatest height in an array of trees:

Function Most(comp As String, items As Variant) As Variant
    Dim i As Long, candidate As Variant, item As Variant
    Dim lb As Long, ub As Long

    lb = LBound(items)
    ub = UBound(items)
    candidate = items(lb)
    For i = lb + 1 To ub
        item = items(i)
        If Application.Run(comp, candidate, item) Then
            candidate = item
        End If
    Next i
    Most = candidate
End Function

The following sub illustrates how this is called:

Sub test()
    Dim A As Variant
    A = Array(3, 6, 2, 8, 11, 15, 1, 10)
    AZ = True
    Debug.Print Most("compare", A) 'prints 15
    AZ = False
    Debug.Print Most("compare", A) 'prints 1
End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

You're thinking it wrong: the VBA language grammar doesn't work like that, operators are well defined, and when this line gets tokenized:

If a op B Then

Seeing the If keyword it will expect this:

If [BoolExp] Then

...and then bump into a op B and throw a fit, because a is an identifier, op is an identifier, and B is another identifier - there's no logical operator there, and that can't be evaluated as a [BoolExp] - hence the compile error.

But you know this already.

swt = True
op = IIf(swt = True, "<", ">")

IIf works similarly: IIf([BoolExp], [ValueStmt], [ValueStmt]) - here swt being assigned to the Boolean literal True, it constitutes a Boolean expression all by itself. Thus, the assignment for op can be simplified to this:

op = IIf(swt, "<", ">")

Now that's prettier, but op is still a String variable, and that just won't work.

Short of making Excel do the work with Application.Evaluate, the only way is to branch in VBA code that works in all Office hosts is, well, to branch:

If swt Then
    If a < b Then msg = "a is smaller than b"
Else
    if a > b then msg = "a is greater than b"
End If
MsgBox msg

The edge case where a = b also needs to be handled, of course.

Upvotes: 3

SierraOscar
SierraOscar

Reputation: 17637

Use the Evaluate() method.

swt = True
op = IIf(swt = True, "<", ">")
a = 10
B = 20
If Evaluate(a & op & B) Then
    MsgBox ("a is greater than B")
End If

Upvotes: 2

user4039065
user4039065

Reputation:

Use basic string concatenation methods to build a simple math formula bringing the operator(s) in as a string character. Once the formula is constructed from string parts, it can be resolved with Application Evaluate.

Dim swt As Boolean, op As String
Dim a As Long, b As Long

swt = False
op = IIf(swt, "<", ">")
a = 10
b = 20
If Application.Evaluate(a & op & b) Then
    MsgBox ("a is " & IIf(swt, "less", "greater") & " than b")
Else
    MsgBox ("a is " & IIf(swt, "greater than or equal to", "less than or equal to") & " b")
End If

Upvotes: 5

Related Questions