Downloads |
---|

45816.zip |

A VBScript Version of VBA's IIf Function

The IIf (short for Immediate If) function is a simple but efficient function that's native to Visual Basic for Applications (VBA). You can, however, create a VBScript function that has the same functionality. Although VBScript versions of this function have been in use for a while, I thought I'd share my version for those of you who don't know about this helpful function.

The IIf function evaluates an expression and returns a specific value, depending on whether the expression evaluates to True or False. Thus, you can use the IIf function in place of VBScript's If...Then...Else statement.

As Listing 1 shows, the function contains only three lines of code, two of which are the Function and End Function statements. You place this function in your script, then call the function, using the syntax

IIf(expr, truepart, falsepart)

where *expr *is the expression you want to evaluate, *truepart* is the value or expression returned if expr is True, and *falsepart* is the value or expression returned if expr is False. For example, the calling code might look like

returnValue = _ IIf(a = b, "a equals b", _ "a is not equal to b")

If the value of what is stored in variable *a* equals the value of what's stored in variable *b,* then returnValue would contain the string "a equals b". Otherwise, returnValue would contain the string "a is not equal to b".

In my experience with using this function, I've found that you can use pretty much any type of expression and return any type of value (e.g., character, number, date). The expressions in the argument list can include calls to other functions and nested IIf functions. You can even nest IIf within other VBScript functions. For example, you can use the statement

UCase(IIf(a=b,"a is equal to b", "a is not equal to b"))

to return uppercase results or the statement

Trim(IIf(a=b," a is equal to b ", " a is not equal to b "))

to trim leading and trailing spaces.

The IIfSample.vbs script includes examples of how you can use the IIf function. For example, the script demonstrates how you can nest IIf functions. Nesting lets you expand your ability to evaluate an expression beyond only two possible results. Listing 2 shows two examples of nested IIf functions. In these examples, when the first expression is False, another IIf function is evaluated. As the latter example shows, you can nest two IIf functions to evaluate all possibilities of a, b, and c being equal or not equal. Because multiple nested IIf functions can be difficult to read and debug, I recommend that you nest IIf functions only one level deep.

You can download IIfSample.vbs from the *Windows Scripting Solutions* Web site. Go to http://www.windowsitpro.com/windowsscripting, enter 45816 in the InstantDoc ID text box, then click the 45816.zip hotlink. When you run IIfSample.vbs, try changing the values of a, b, and c and try different TestVar settings.