Conditional Functions

Conditional functions let you evaluate different conditions and return a value based upon the result. These are very similar to the conditional structures If Then and Select because both allow you to evaluate different conditions and perform some action. Conditional functions are different in that they evaluate all the conditions and return a value using only one line of code. Conditional structures like the If Then and Select Case statement need multiple lines of code to evaluate a condition and return a value.

Conditional functions have another benefit in that since they return a value, they can be used within another function! This gives you the ability to create a function that performs its operations using a number that changes depending upon certain conditions.

Let’s use an example to try to help illustrate it. Say you have an If statement that evaluates a condition and returns the result. This number is later used in another calculation. To make this work, there would have to be a function just for the If statement and this function would be called elsewhere in the report. You can replace this code with a single IIF() function (discussed next) that evaluates those conditions within the function and returns the value for use in the function. Everything is cleanly written with one line of code. An example that demonstrates how this works is within the discussion of the IIF() function.

Although conditional functions sound pretty good, they do have a drawback. If you get carried away with their use, your code will be harder to read and maintain. For example, an IIF() function is good at replacing a single IF statement. But if you want to replace a nested If statement, you will have to write nested IIF() functions. Although this will compile and run, it can be pretty hard for you or another person to read and understand. Use caution when deciding what is appropriate for the task at hand.

Being able to test conditions and return a result within a single function is very powerful. This section describes three conditional functions: IIF(), Choose(), and Switch().