top of page

VBA CODE: IF-ELSE Statement | Microsoft Excel

IF is one of the most helpful and highly used statement to check for the logical tests and manipulate the data into required information from the dataset.


The condition in IF statement can only be true or false. Either it will pass the logical test or will not. Often there is a choice of two possibilities, only one of which will be executed, depending up on the logical test.


Suppose, in our excel worksheet, we have different values of a and b in column B, C and D as shown below. And we want to know whether a is less than b, more than b or equal to b using an IF-ELSE statement.


VBA Code: IF Statement

The first line is an IF statement- which represents the logical test and the second line gets executed if the first line passes the logical test after referring to the values of a and b. If the logical test fails, then it will skip the second line and will directly end our IF statement.


In our example, the choice is between executing something (if it passes the logical test) or do nothing (if it fails the logical test). Since it passes the logical test (were a is actually less than b), it will execute the print command.

Sub IfStatement()
    If Range("B1").Value < Range("B2").Value Then
    MsgBox ("a is less than b.")
    End If
End Sub

VBA Code: IF-ELSE Statement

The first line is an IF statement- which represents the logical test and the second line gets executed if the first line passes the logical test after referring to the values of a and b. If the logical test fails, then it will skip the second line and will execute the ELSE statement.


In our example, the choice is between executing either the second line under IF statement (if it passes the logical test) or second line under ELSE statement (if it fails the logical test). Since it fails the logical test (were a is not less than b), it will execute the print command.

Sub IfElseStatement()
    If Range("C1").Value < Range("C2").Value Then
    MsgBox ("a is less than b.")
    Else
    MsgBox ("a is more than b.")
    End If
End Sub

VBA Code: Multiple IF-ELSE Statements

The first line is an IF statement- which represents the logical test and the second line gets executed if the first line passes the logical test after referring to the values of a and b. If it fails the first logical test, then it will skip the second line and execute whatever specified in the ELSE statement. Now in the ELSE statement, we again have an IF-ELSE statement that will follow the same process as mentioned in the above example.


However, in this example, the choice is between executing either the second line under first IF statement (if it passes the first logical test) or the second line under the second IF statement (if it fails the first logical test but passes the second logical test) or the second line under ELSE statement (if it fails both- first and the second logical test). Since it fails both the logical test under IF statements (where a is equal to b), it will refer to the code under ELSE statement and execute the print command.

Sub MultipleIfElseStatement()
    If Range("D1").Value < Range("D2").Value Then
    MsgBox ("a is less than b.")
    Else
        If Range("D1").Value > Range("D2").Value Then
        MsgBox ("a is more than b.")
        Else
        MsgBox ("a is equal to b.")
        End If
    End If
End Sub

One can also use ELSEIF Statement wherein- instead of writing a second IF statement, user can specify the condition with in the ELSEIF statement. And will have a ELSE statement which will get executed if the IF statement and all the ELSEIF statement fails.

Sub IfElseElseIfStatement()
    If Range("D1").Value < Range("D2").Value Then
    MsgBox ("a is less than b.")
    ElseIf Range("D1").Value > Range("D2").Value Then
    MsgBox ("a is more than b.")
    Else
    MsgBox ("a is equal to b.")
    End If
End Sub

The best part of ELSEIF statement is that user can use any number of “ELSEIF” in the code allowing user to specify any number of conditions under the IF statement.

The message box will contain whatever specified in the code. User has to specify the message inside the open and close parentheses with double quotes as shown in the above examples.

 

CHEAT SHEET

Disclaimer: We, in our example, only cover how to perform such functions and less focusing on the type of example we take. Our motive is to deliver the exact solution to the queries on "How To" in the most simplest way. Of course, the application of these function can be seen in our advanced modules with more complex examples and datasets.

 

Feel free to share your views and ask for models in the #comment section. If you like our content, please hit a #like button.

18 views0 comments
bottom of page