Calculate the larger number among three on VBA, Macros by using if and elseif statement
Answers
Syntax – IF Then Else
Below is the generic syntax of If Then Else construct in VBA
IF condition Then true_code [Else false_code]
Or
IF condition Then
true_code
Else
false_code
End IF
Note that the Else part of this statement is optional.
Now if you’re wondering what’s the difference between the two syntaxes, let me clarify.
The first syntax is a simple one-line IF THEN ELSE statement where you don’t need to use the END IF statement.
However, in the second syntax, the true_code part is in the second line. This is helpful when the code that you need to run in case the IF condition is true is long and consists of multiple lines.
When you split the IF statement into multiple lines, you need to tell VBA where the IF Then construct ends.
Hence you need to use the End IF statement.
In case you don’t use End IF when required, VBA will show you an error – “Block IF without END IF”
IF Then Else in VBA- Block IF without End If error
Examples of Using IF Then Statement in VBA
To give you an idea of how the IF-THEN statement works in VBA, let me start with some basic examples (some practical and more useful examples are covered later in this tutorial).
Suppose you have a student’s score in cell A1 and you want to check whether the student passed the exam or not (passing marks threshold being 35).
Then you can use the following code:
Sub CheckScore()
If Range("A1").Value >=35 Then MsgBox "Pass"
End Sub
The above code has a single line of IF statement that checks the value in cell A1.
If it’s more than 35, it shows the message – “Pass”.
If it’s less than 35, nothing happens.
But what if you want to show a message in both the cases, whether a student passed or failed the exam.
The below code would do this:
Sub CheckScore()
If Range("A1").Value >= 35 Then
MsgBox "Pass"
Else
MsgBox "Fail"
End If
End Sub
The above code uses the IF as well as the ELSE statement to execute two different conditions. When the score is more than (or equal to) 35, the IF condition is true, and the code right below it gets executed (everything before the Else statement).
But when the IF condition is FALSE, the code jumps to the Else part and executes the code block in it.
Note that when we use a single line of IF Then statement, we don’t need to use End IF. But when we split it into more than one line, we need to use the End If statement.
Nested IF Then (Multiple IF Then statements)
So far we have used a single IF Then statement.
In case you have multiple conditions to check, you can use:
Multiple IF conditions
If Then Else statement
IF Then ElseIf Else construct
Let me show you how these differ and how to use this in Excel VBA.
Multiple IF Then Statements
Let’s take the same example of using a student’s score.
If the student scores less than 35, the message to display is ‘Fail’, if the score is more than or equal to 35, the message to display is ‘Pass’.