Search code examples
vbavariablesdynamicoperator-keyword

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?

Solution

  • 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.