VBA Programming : If Then Statement

.

This Article is going to teach you about Conditional Programming. By Conditional Programming, I mean, the execution of certain statements based on certain conditions. Sometimes in VBA programming, we want to execute certain statements only when a specific condition is met. If Then Statement helps you achieving this.

Now we will discuss about all aspects of If Then Statements. We can use If Then statements in many ways.

If Then Statement

If Then Statement - VBA

If Then Statement – VBA



As the above image illustrates, a set of conditions will be checked and If that is True, then a Set of statements will be executed otherwise it will not be executed.

Syntax:

If Condition Then

Statement 1….
Statement 2….
….
….
End If

Example:


Private Sub CommandButton1_Click()
	Dim Score As Integer
	Score = InputBox("Enter your Score", "Enter Score")
	If Score >= 45 Then
		MsgBox ("You are Passed")
	End If
End Sub

Explanation of the Above Code:

1. On Clicking on the Command Button, You will be asked to provide the Score in Input Box.
2. Score will be Compared if it is Greater than or Equal to 45 (>=45)
3. If the above condition is true( if you have entered some greater number than 45) then a Message will be displayed as You are passed
4. If you have entered less than 45, then the condition will fail and in that case No message will be displayed.

From the above example, It is clear that, if the condition is True then the message is Displayed. If it is False then nothing is happening.
Sometimes, we are in a situation, where we want to execute certain Statements, if the condition is True and a set of Different Statements, when it is False. To achieve this, we use If Else Statement.

If else Statement:

If else Statement - VBA

If else Statement – VBA

As the above image illustrates, a set of condition will be checked and If that is True, then a Set of statements will be executed and if Condition is False then a different set of Statements will be executed.

Syntax:

If Condition Then

Statement 1….
Statement 2….
….
….
Else
Statement 1….
Statement 2….
….
….

End If

Example:


Private Sub CommandButton1_Click()
	Dim Score As Integer
	Score = InputBox("Enter your Score", "Enter Score")
	If Score >= 45 Then
		MsgBox ("You are Passed")
	Else
		MsgBox ("You are Failed")
	End If
End Sub

Explanation of the Above Code:

1. On Clicking on the Command Button, You will be asked to provide the Score in Input Box.
2. Score will be Compared if it is Greater than or Equal to 45 (>=45)
3. If the above condition is true( if you have entered some greater number than 45) then a Message will be displayed as You are passed
4. If you have entered less than 45, then the condition will fail and in that case the message which is there in Else part will be displayed. You are Failed

Nested If Else Condition (Multiple If else Statement)

You can use multiple If Else statements within If else statements. This is called Nested If else or Multiple If else Conditions Refer the below example:


Private Sub CommandButton1_Click()
	Dim Score As Integer
	Score = InputBox("Enter your Score", "Enter Score")
	If Score >= 45 Then
		If Score >= 60 Then
			MsgBox ("You are Passed in First Division")
		Else
			MsgBox ("You are Passed")
		End If
	Else
		MsgBox ("You are Failed")
	End If
End Sub

In the above example you can see, in the first If Part, there is another If else statement is executed. It means when the outer If condition is satisfied then Control will get inside and check the Condition of another If Else statement and based on that it will execute the statements.

Note:

In Nested If else statements, Control goes to Inner If Else, only when outer If Else is satisfied, otherwise it will ignore the inner If else, even though the condition is satisfied.

It is not a good Idea to use Nesting of If Else Conditions for more than 3-4 times. It becomes very difficult to understand the code. Also it takes long time to execute all the conditions. To overcome this problem, you should use Select Case Statement.

To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

3 Comments

  1. nur

    Dear Vish Bhaia, you did excellent job !!! keep it up please ..

    Nur …Bangladesh

    Reply
    • Vishwamitra Mishra

      Thanks Nur !!!

      Reply
  2. santhosh

    hi vishwa, i have gone thru ur site you did amazing job,

    can you post any code on the following requirement
    Addhar no company First name last name
    120 abc xxxx yyyy
    150 bcd ooo rrrr
    180 efg uuu ssss
    120 kfc xxxx yyyy
    200 udg xxxx yyyy
    222 iii xxxx yyyy
    444 ppp xxxx yyyy
    here xxxx yyyy we have adhar no 120, but the same person addhar was reflecting in different companies with different adhar no. but it is not correct, it should be 120 for the same person in all the companies,
    i had too many records in in my excel like this senarios, how to find this distinct records can you provide me any code for this.
    my mail santhoshlaxmi111@gmail.com
    final resul is we need to get in separete sheet,
    this record is correct,
    120 abc xxxx yyyy
    the following are not correct
    200 udg xxxx yyyy
    222 iii xxxx yyyy
    444 ppp xxxx yyyy

    Reply

Trackbacks/Pingbacks

  1. Learn Excel Macro VBA Programming : Decision - Select Case - [...] friends in the last article of VBA Programming Tutorial you have learnt the use of If..else statement. In this…

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest