VBA IIf Function

The IIf() function is a built-in function in VBA (Visual Basic for Applications) that allows for conditional statements. It is useful for writing concise and efficient code, as it evaluates an expression and returns one of two possible values based on whether the expression is true or false.

VBA IIf Function – Purpose, Syntax and Arguments

Syntax:

IIf(expression, truepart, falsepart)

Arguments:

• expression: This is a required argument that evaluates to a Boolean value (True or False).
• truepart: This is the value that will be returned if the expression is true.
• falsepart: This is the value that will be returned if the expression is false.

Example:

Suppose we have a workbook containing a list of employees and their salary information. We want to create a new column that categorizes each employee as either “High” or “Low” based on their salary. We can use the IIf() function to do this.

Function EmployeeCategory(salary As Integer) As String
    EmployeeCategory = IIf(salary > 50000, "High", "Low")
End Function

In this example, if the employee’s salary is greater than 50000, the function will return “High”, otherwise it will return “Low”. We can then apply this function to the entire column using a loop or by dragging the formula down, to categorize each employee based on their respective salaries.

Remarks and Important Notes:

• The arguments truepart and falsepart can be any valid expression, including a string, number, or another function call.
• The IIf() function is also commonly used in conjunction with the VBA Select Case statement, where the expression can be evaluated to determine which code block to execute.
• If the expression returns a Null value, the IIf() function will return a Null value as well.
• The IIf() function can also be used in Excel formulas by simply removing the “Function” keyword and the return type declaration.

The IIf() function in VBA is a convenient and versatile tool for handling conditional statements. It allows for a more concise and readable code, and can significantly improve the efficiency of your VBA projects.

Understanding VBA IIf Function with Examples

The IIf function allows you to evaluate a condition and return one value if the condition is true, or a different value if the condition is false. In this blog post, we will explore the IIf function in depth, including its syntax, usage, and examples.

Example 1: Using the IIf function to return an answer based on a condition

The basic syntax for the IIf function is as follows:

 IIf(condition, true value, false value)

Let’s use an example to better understand how this function works. Imagine we have a dataset that contains the names and ages of students, and we want to add a column that indicates whether the student is a teenager or not. We can use the IIf function to evaluate the age of each student and return either “Yes” or “No” depending on whether they are between the ages of 13 and 19.
The code for this example would be:

Sub CheckTeenager()
    Dim age As Integer
    Dim teenager As String
    
    For i = 2 To 10 'assuming the data starts on row 2
    
        age = Range("B" & i).Value
        
        teenager = IIf(age >= 13 And age <= 19, "Yes", "No")
        
        Range("C" & i).Value = teenager
        
    Next i
    
End Sub

• The first line of the code declares two variables, age and teenager.
• Next, we use a ‘For’ loop to go through each row of data (assuming it starts on row 2) and get the value of the age for each student.
• Inside the loop, the IIf function is used to evaluate the age of the student and return either “Yes” if they are a teenager or “No” if they are not.
• The value of the variable teenager is then assigned to the corresponding cell in column C.

Example 2: Nesting IIf functions

The IIf function can also be nested within another IIf function to handle more complex logical conditions. Let’s say we want to assign a grade to each student based on their age. Our conditions are as follows:

• If the student is 13 or younger, their grade is “C”
• If the student is between 14 and 16, their grade is “B”
• If the student is 17 or older, their grade is “A”

We can use the following code to achieve this:

Sub AssignGrade()
    Dim age As Integer
    Dim grade As String
    
    For i = 2 To 10 'assuming the data starts on row 2
    
        age = Range("B" & i).Value
        
        grade = IIf(age <= 13, "C", IIf(age <= 16, "B", "A"))
        
        Range("C" & i).Value = grade
        
    Next i
    
End Sub

• Similar to the previous example, we declare the age and grade variables.
• In this example, the IIf function is nested within itself, with the first condition checking if the student is 13 or younger.
• If the student is 13 or younger, the IIf function returns “C”. If not, the second IIf function is evaluated, checking if the student is less than or equal to 16.
• If the student’s age is between 14 and 16, the grade “B” is returned. Otherwise, the default grade of “A” is returned.
• The value of the grade variable is then assigned to the corresponding cell in column C.

Example 3: Using IIf with other functions

The IIf function can also be combined with other functions to perform more complex actions. Let’s say we want to calculate the average of a student’s test scores, but only for students who are in grade “B”. We can use the following code to achieve this:

Sub AverageScore()
    Dim grade As String
    Dim average As Double
    
    For i = 2 To 10 'assuming the data starts on row 2
    
        grade = Range("C" & i).Value
        
        average = IIf(grade = "B", AVERAGE(Range("D" & i & ":G" & i)), 0)
        
        Range("H" & i).Value = average
        
    Next i
    
End Sub

• In this example, the ‘AVERAGE’ function is nested within the IIf function.
• The IIf function first checks if the student’s grade is “B”. If it is, then the average of their test scores (columns D to G) is calculated and stored in the average variable. If not, a value of 0 is returned.
• The calculated average is then assigned to the corresponding cell in column H.

Conclusion

The IIf function in VBA is a powerful and versatile tool that allows you to evaluate logical conditions and return different values based on the result. It can be used in a variety of scenarios, from simple data manipulation to more complex problem-solving. Understanding the syntax and usage of the IIf function will help you to write more efficient and effective VBA code for your projects. So the next time you are faced with a logical condition in your VBA code, remember to reach for the IIf function.