IIf vs Custom VBA function If Then...Else....

[kilou]

Hi,

I've read that IIf function is somewhat slow since it has to compute both the true and false part of the equation everytime. Would it be better to build a custom VBA function something like this (example of string fields):

Function CustomIf(criteria as string, truepart as string, falsepart as string)as string

If Criteria Then
CustomIf=truepart
else
CustomIf=falsepart
End If

End Function

Would this be computed faster as using IIf(criteria,truepart,falsepart) as an expression???

Thanks

 

[Banana]

datAdrenaline and I talked about that before; I can't remember why, but if you search for thread talking about "overloading function", you should find his analysis on this one.

 

[kilou]

As far as I could understand, seems like that your first thread was about Jet IIf vs VBA IIf but not Jet IIf vs VBA If then Else.... But I learned that VBA will always evaluate both sides of the function... Is that right?

 

[Rich]

As far as I could understand, seems like that your first thread was about Jet IIf vs VBA IIf but not Jet IIf vs VBA If then Else.... But I learned that VBA will always evaluate both sides of the function... Is that right?

No the Iif function will always evaluate both sides of the equation it looks at first sight that a SelectCase might work here

 

[Banana]

As far as I could understand, seems like that your first thread was about Jet IIf vs VBA IIf but not Jet IIf vs VBA If then Else.... But I learned that VBA will always evaluate both sides of the function... Is that right?

Yes, it's the VBA Iif that always evaluate both sides, while Jet's native Iif() doesn't do this.

This also jogged my memory. datAdrenaline demonstrated this and you can repeat it yourself:

In query builder, add a function:

Iif(1=1, 1, (1/0))

Do the same in VBA function. Running the query will not return an error, while VBA will error on division by zero. So using Iif() in query should be fine *especially* for one-time go. When there are multiple conditions to evaluate in query, consider Switch() or Choose(). In VBA, Rich already mentioned Select Case statement.

HTH.