The Functions in Microsoft Excel: IF AND ELSE Function in Macro

Namaste🙏😊 My Dear friends.

Hope you all are doing well and ready for another Macro thing.

Friends, today’s topic is something very important and useful function in Macro.

Before we start, I have to discuss a situation with you.

Suppose u had a fight with your close friend and you are responsible for the same. Due to fight you don’t talk with each other.

Assuming he’ll not come back and talk to you.

Now you have been left with Two Options: -

1) You’ll call him/her and say sorry.

2) Let him/her go.

IF you'll choose 1^st Option then "you’ll preserve your friendship",

OR

ELSE "you’ll break the friendship".

Similarly, in Macros also we can put options and result will come accordingly.

The function to do so will be cover in Today’s Topic i.e.

IF AND ELSE.

So, let’s Begin.

We’ll take two examples in this blog.

First, we will use if else function for the above situation that we discussed.

As you can see in above image, we have to type answer as ‘a’ or ‘b’.

So, first we’ll write the code for the same in VBA.

If we type ‘a’ then response should be ‘You’ll Preserve Your Friendship’.

If we type ‘b’ then response should be ‘ You’ll Break the Friendship’.

1) Open VBA Editor

2) From the tabs given in VBA Editor click on Insert and Select Module.

3) This will create a Module in which we’ll write the codes.

4) All the codes in VBA starts with “Sub ‘Macro_Name’ ()” and ends with “End Sub” without quotations.

5) Copy or write the following code:-

Sub IF_ELSE()

If Range("B6").Value = a Then

Range("B9").Value = "You’ll Preserve Your Friendship"

Else

Range("B9").Value = "You'll Break the Friendship"

End If

End Sub

Learning Points from above Codes: -

§ In above code as you can see it starts with Sub and ends with End Sub

§ Range(“B6”).value represent the value of cell “B6” where we’ll type our answer.

§ Range(“B9”).value represent the value of cell “B9” where we’ll get the response.

§ The If function starts with ‘If’ (condition) Then (Answer If Condition fulfills) Else (Answer if condition not fulfilled) and ends with ‘End if’.

§ We can read above code as, If “B6” value is ‘a’ then response will be ‘You’ll Preserve Your Friendship’ otherwise ‘You'll Break the Friendship’,

Now let’s check our code.

We’ll type “a” in Answer cell i.e. “B6”.

Now go to VBA editor and press F5 to run the macro.

Yes, it’s showing the correct response.

Now, let’s type “b” as answer and press F5 in VBA.

Yes, the response is changing.

We can see the code working perfectly.

We can also set the Shortcut Key for the above macro: -

a) Go to Developer tab and click on Macro.

b) The Macro window will be open and we can see our macro on left side.

c) Select the same and click on “Options” tab given on the right side.

d) Here we can set shortcut key and description of the Macro.

So, this is one condition example where only single If Else is used.

Now, we’ll take another example, where we’ll cover multiple conditions and If.

Suppose we have list of students with their respective marks: -

Sr. No.	Name of Student	Marks
1	John	84
2	Sachin	37
3	Anna	-32
4	Sania	92
5	Amir	38
6	Monica	90

And we need to find out their Grades as per the following Grading Criteria: -

Marks	Result
0-39	D Grade
40-60	C Grade
61-79	B Grade
80-100	A Grade

For this we’ll use multiple IF Conditions. Let’s Start: -

1) Open VBA Editor and insert new Module as we did in above example.

2) Write or copy the following code: -

Sub MULTIPLE_IF()

If ActiveCell.Previous.Value >= 0 And ActiveCell.Previous.Value < 40 Then

ActiveCell.Value = "Fail"

ElseIf ActiveCell.Previous.Value >= 40 And ActiveCell.Previous.Value <= 60 Then

ActiveCell.Value = "C Grade"

ElseIf ActiveCell.Previous.Value >= 61 And ActiveCell.Previous.Value < 80 Then

ActiveCell.Value = "B Grade"

ElseIf ActiveCell.Previous.Value >= 80 And ActiveCell.Previous.Value <= 100 Then

ActiveCell.Value = "A Grade"

Else

ActiveCell.Value = "Invalid"

End If

End Sub

Learning Points from above Codes: -

§ In above code as you can see it starts with Sub and ends with End Sub

§ ActiveCell.value represent the value of active or selected cell i.e. Grade Columns Cells.

§ ActiveCell.Previous.value represent the value of cell before the active cell.

Eg.: for “D5” previous cell will be “C5”.

§ We have used “And” function for applying two conditions under single IF.

§ We have used ElseIF for using multiple options

§ We can read above code as, If the marks are between 0 – 39 then give result as “Fail”, if it is between 40 – 60, then as “C Grade”, if it is between 61 - 79, then “B Grade”, if it is between 81 – 100 then Result as “A Grade” and if any other input is there then result as “INVALID”.

Now, let’s try out our code, before that we will set a shortcut key for our Macro by following the previously mentioned steps.

We have set the shortcut key as “Ctrl+Shift+R” as shown below.