The IF function in VBA is one of the most frequently used of all statements. The IF function checks if the specified condition is being met or not. The IF function in VBA works in a slightly different manner from how it works in Excel. In Excel, the IF function does two things : It checks whether the given condition evaluates to TRUE or FALSE and then returns a value to use based on the evaluation i.e IF(condition, return_value_if_condition_true, return_value_if_condition_false). However, the IF function in VBA does only the first part, i.e. check if the condition evaluates to TRUE or FALSE. The steps to take in either scenario as specified using the additional THEN statement.

Here are a few quick links :

Using a simple IF function in VBA
Using a IF function with ELSE in VBA
Using a IF function with ELSEIF and ELSE: in VBA

Using a simple IF function in VBA

Here’s an example of the IF function. This piece of VBA code is simply checking whether the condition specified (i.e. 1 > 4) evaluates to TRUE or FALSE. In this case, we have only specified the steps to be completed when the condition evaluates to TRUE. In this case, the result will be a message box being displayed on the screen. If the function were to evaluate to FALSE, the VBA code will do nothing.

The THEN statement is essentially a directive indicating that the steps immediately following it are to be executed if the condition just before if evaluate to TRUE.

The IF function typically ends with an END IF declaration which lets the application know that it is the last line of the IF function. Given below is a slightly shorter form of the IF function. Note that the END IF statement has been eliminated in this and thus three lines of code has been merged into one.

However this form should be used only when there are no ELSE or ELSE IF statements are needed. Let’s look at what they are and how they help enhance the IF function.

Using a IF function with ELSE in VBA

In the example given above, we saw that the IF function would be able to carry out steps only if the condition evaluated to TRUE. But what if we wanted to specify the steps to be carried out when either of the conditions were met. One way to achieve branching out of an IF function is to use the ELSE statement. Simply put, the ELSE statement causes the application to execute the steps mentioned in the ELSE block when the condition specified in the IF clause evaluates to FALSE.

In the VBA code example below, the condition in the IF function evaluates to false, the steps mentioned in the ELSE block are executed with the result that the message “1 is less than 4” gets displayed on the screen. Multiple steps can be added to the block to have them all execute one after another.

Using a IF function with ELSEIF and ELSE: in VBA

The ELSE statement may be supported by ELSEIF statements. They are typically used to check for specific conditions and if all of them evaluate to FALSE, the steps specified in the ELSE condition will finally get executed. The IF statement will check for a given condition. If that condition turns out to be FALSE, the condition specified in the first ELSEIF statement will be checked. If that also turns out to be FALSE, the condition specified in the second ELSEIF statement will be checked, and so on and so forth. If all conditions specified in the IF and ELSEIF statements turn out to be FALSE, by default the steps mentioned under the final ELSE: block will get executed. Please remember that if there are multiple ELSEIF statements, the first one that evaluates to TRUE will get executed and once completed, the code execution will move to the END IF statement. Even if there are multiple ELSEIF conditions that evaluate to TRUE, only the first one that evaluates to TRUE will be executed.

In the example shown below, the IF condition is checked first. Since it evaluates to FALSE, the first ELSEIF condition is evaluated, followed by the second. Since none of them evaluate to TRUE, the steps mentioned in the ELSE: condition are executed.

Quick Tip – How to make IF statements work faster

Both the pieces of code appear similar. The two ELSEIF conditions in the second function have been swapped. Which one of the following pieces of VBA code do you think executes faster?

OR

The answer is that the second one executes much faster than the first. Why? Because the second one needs to application to go through lesser lines of code before it finds a condition that evaluates to TRUE. Remember that the first ELSEIF condition that if found TRUE gets executed and none of the other conditions are evaluated, even if they were to also evaluate to TRUE. In the first piece of the VBA code, the ELSEIF function on line 6 evaluates to TRUE while in the second, line 5 meets the criteria. In essence, the more likely the condition is to get evaluated to TRUE, the earlier it should be placed in the VBA code, all else being the same.