Categorized | excel macro and vba

VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

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

VBA IF THEN ELSEIF ELSEIn 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.

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. jeyner wrote:

    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.

  2. kindimba wrote:

    How can I select the best seven subjects with best passes among ten subjects

  3. venu wrote:

  4. anonymous wrote:

    Nice Info in this websites
    thanks

  1. Prevent Users Ctrl Break (ing) Your VBA Code During Execution | Excel & VBA – da Tab Is On wrote:

    [...] sheets, rows and columns that the code generates and subsequently deletes before exiting. If the user stops the code execution in between, they are left with a pretty ugly spreadsheet. [...]

  2. What is the difference between Else and ElseIf? wrote:

    [...] Re: What is the difference between Else and ElseIf? Short answer: use ELSEIF when you want to check another condition with multiple options that you want to handle differently. Use ELSE if you don't care about any other options and just want to return or do one thing if your main condition check is not met. Long answer: read this: VBA IF Function – Using IF, ELSE:, ELSEIF, IF THEN in VBA code | Excel & VBA – Datab… [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel