Thursday, April 24, 2014

VBA - If Then Else Endif


Do you remember the Excel built-in function IF? You can provide a condition to be evaluated by IF and provide a calculation / value if result is TRUE and provide a different calculation / value if result is FALSE. In similar lines, VBA also provides a feature i.e.. IF statement. Syntax for this is as below.

Form 1 – In One Line – Without Else Condition

If condition Then statement

If: is the key word to call this feature
Condition: what do you want to evaluate. You can give one or more conditions to evaluate (we will see how)
Statement: what if condition is met.
We are not defining what to do if condition is not met so macro will do nothing (moves to next available line of code).

Example: If ActiveCell.Value = 5 Then MsgBox(ActiveCell.Value)

This form is best used if you just want to evaluate one condition followed by single line action if condition is true. No action if condition is false.

Form 2 – In One Line - With Else Condition

If condition Then statement [Else elsestatement]

Other key words are similar as above form.
Elsestatement: What if condition is not met

Example: If ActiveCell.Value = 5 Then MsgBox(ActiveCell.Value) Else MsgBox(“No Match”)

This form is best used if you just want to evaluate one condition followed by single action if condition is true and a single line action if condition is false.

Form 3 – Multiple Lines - Without Else Condition

If condition Then
statement
Endif

Example:
If ActiveCell.Value = 5 Then
    MsgBox(ActiveCell.Value)
End If
Form 4 – Multiple Lines – With Else Condition

If condition Then
statement
Else
elsestatement
End If

Example:
If ActiveCell.Value = 5 Then
     MsgBox(ActiveCell.Value)
Else
     MsgBox(“No Match”)
End If
Form 5 – Multiple Lines - Multiple Else conditions
If condition Then
     statement
Elseif condition Then
     Elsestatement
Elseif condition Then
     Elsestatement
……………………….
Else
statement
End If
Example:
If ActiveCell.Value = 5 Then
      MsgBox(ActiveCell.Value)
Elseif ActiveCell.Value = 4 Then
      MsgBox(Application.UserName)
Elseif ActiveCell.Value = 3 Then
      MsgBox(“Welcome To The World”)
Else
      MsgBox(“Bye Guys”)
End If
Above examples are pretty much self explanatory. Copy them to a VBA module, enter a value in a cell and execute the macro. We will use many If Then Else statements in our forth coming macros. Stay tuned!!