Replacement for VBA IIF function

Question

I am working on an Excel project that involves converting an large set of Excel 4.0 macrosheet functions to VBA. These functions make extensive use of the worksheet IF(condition,ifTrue,ifFalse) function.

Conversion has gone well, as most of the macrosheet statements convert directly to VBA, but there is one nagging issue. The worksheet IF(condition,ifTrue,ifFalse) only evaluates the necessary ifTrue or ifFalse clause, whereas The VBA IIF(condition,ifTrue,ifFalse) function always evaluates both clauses. The problem is that many of the IF statements in the macrosheet code are written to expect the worksheet function behavior. An example of the problem: for a=0, IF(a>0, a+1/a, 0) will return 0, but IIF(a>0, a+1/a, 0) will crash because there is division by 0 in the ifTrue clause, even though that clause is not used.

For some reason, the Excel WorksheetFunction object does not expose IF. Rewriting all of the IF(condition,ifTrue,ifFalse) statements using VBA's IF...Then...Else would be extremely tedious, given the number of times that function is used.

Is there a way to replace the IIF function with another function that behaves like the worksheet IF function, or is there some way to make the WorksheetFunction object expose IF?

Thanks!

Answer 1

As discussed in the comments, it's not possible to get a "lazy evaluation" of an expression passed as argument to a function: the expression will always be evaluated before the function starts. This means that the simplest option is probably to use the IF...Then...Else syntax.

However, for the sake of completeness, I'll mention some alternative ways this can be implemented using strings.

A) Using the Evaluate Function

If the values you are working with are from Excel cells, you could use the Evaluate function which allows you to calculate worksheet formulas. For instance, if the value you need to evaluate is in cell A1, the following would work even if A1 contains the value 0.

Evaluate("=IF(A1 > 0, A1 + 1 / A1, 0)")

B) Using the stdVBA library

If you import the stdLambda and stdCallable classes from the stdVBA library, you would be able to use lamda expressions defined with a string to avoid evaluating expressions passed to a function.

This would allow you to define the following function :

Function IfLazy(Condition As Boolean, EvalTrue As String, EvalFalse As String, ParamArray params() As Variant)

    If Condition Then
        IfLazy = stdLambda.Create(EvalTrue).RunEx(params())
    Else
        IfLazy = stdLambda.Create(EvalFalse).RunEx(params())
    End If

End Function 

Then the following expression would work even if the variable a has the value 0.

   
IfLazy(a > 0, "$1 + 1 / $1", "0", a)

Explanation:

A lambda expression is basically a way to define a function by simply providing what operations will be performed on the arguments of the lamda expression.

The arguments here are specified as $1 for the 1st argument, $2 for the second argument and so on.

Note the use of the ParamArray keyword in the function definition which would allow us to pass a varying number of arguments to the functions that will then be passed to the correct lambda expression. For example, you could have 2 variables (a and b) by specifying them in order at the end of the arguments. Eg.: IfLazy(a > 0, "$2 + 1 / $1", "$2", a, b).