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.
1 2 3 4 5 | Sub IF_FUNCTION() If 7 > 1 Then MsgBox "7 is greater than 1" End If End Sub |
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.
1 2 3 | Sub IF_FUNCTION_SHORT() If 7 > 1 Then MsgBox "7 is greater than 1" End Sub |
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.
1 2 3 4 5 6 7 | Sub IF_ELSEIF_FUNCTION() If 1 > 4 Then MsgBox "1 is greater than 4" Else: MsgBox "1 is less than 4" End If End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 | Sub IF_ELSEIF_ELSE_FUNCTION() If < <condition_1>> Then MsgBox "1 is greater than 4" ElseIf < <condition_2 if condition_1 = FALSE >> Then MsgBox "2 is greater than 4" ElseIf < <condition_3 if condition_2 = FALSE >> Then MsgBox "3 is greater than 4" Else < <If_Everything_Fails>>: MsgBox "1, 2 or 3 are lesser than 4" End If End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 | Sub IF_ELSEIF_ELSE_FUNCTION() If 1 > 4 Then MsgBox "1 is greater than 4" ElseIf 2 > 4 Then MsgBox "2 is greater than 4" ElseIf 3 > 4 Then MsgBox "3 is greater than 4" Else: MsgBox "1, 2 or 3 are lesser than 4" End If End Sub |
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?
1 2 3 4 5 6 7 8 9 10 11 | Sub IF_NEED_FOR_SPEED_1() t = Timer For i = 1 To 100000000 If 1 > 4 Then ElseIf 3 > 4 Then ElseIf 5 > 4 Then Else: End If Next i MsgBox Timer - t End Sub |
OR
1 2 3 4 5 6 7 8 9 10 11 | Sub IF_NEED_FOR_SPEED_2() t = Timer For i = 1 To 100000000 If 1 > 4 Then ElseIf 5 > 4 Then ElseIf 3 > 4 Then Else: End If Next i MsgBox Timer - t End Sub |
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.
well, count spaces between lines and show the list it is what would be intrensting, ex: in a string A1:A78 show me the list of gaps of number x, v.g. 3,5,7,12, I mean found gaps of the any number I choose and drop the list of the spaces.
October 21st, 2011 at 10:46 amHow can I select the best seven subjects with best passes among ten subjects
May 2nd, 2013 at 2:25 pmi like it
June 7th, 2013 at 8:58 pmNice Info in this websites
June 28th, 2013 at 1:26 amthanks
my answer like show when G1 is fond then go to G2 and when G2 is not found then go to G3
September 18th, 2015 at 3:18 amso plz which type of condition i use.
i like tutorial
February 6th, 2017 at 10:55 amSo if I wanted to modify a piece of VBA code for an if/then statement like the one below so that it will end that specific VBA command if the results were false, what statement would I need to tack onto the end of it? My problem is that I have multiple if/then statements similar to this for showing and hiding selections that share rows with other VBA commands. I need a way to keep them from conflicting and still show/hide the entire parts of the document specified. As the code is now, it only hides rows to the next VBA command.
Private Sub Worksheet_Change(ByVal Target As Range)
March 29th, 2017 at 9:09 amIf Range(“B332”).Value = NoText Then
Rows(“334:616”).EntireRow.Hidden = True
Else
Rows(“334:616”).EntireRow.Hidden = False
End If
End Sub
wow very helpful
January 31st, 2018 at 1:26 pmThank you so much 🙂
I have question regarding filter
February 9th, 2019 at 3:29 amRange (“c1”). Select
There are 4 number in the C column like 1, 2,10,5
If number 10 is not found in the c column then program will be executive.
So what will be the code.