IF THEN ELSE statement in Excel VBA

Last Updated on January 11, 2023 by token

VBA IF THEN ELSE – What is the conditional statement?

In the simplest sense, VBA IF is the equivalent of the Excel IF(), but function used in the VBA code. Imagine that you want your program to make decisions based on the data you enter. Depending on what data you enter at the beginning, you can get a different results. The simplified operating model of the If Then Else VBA statement is written here:

If [Condition] Then
 [Code, that will be run, when condition is True]
 Else: [Code, that will be run, when condition is Not True]
End If

Comparing the instruction with the Excel standard function:

If (condition: code after the condition is true, the code after the condition is not true) 

What is important, your program, after fulfilling the condition, does not process the block of code stored in the ELSE clause. Program simply skips it. So if possible, in the THEN clause, you should enter blocks of code that will be executed with a higher probability. We save the resources of your computer in this way.

An example of using VBA If Then Else conditional statement

Imagine that you want to write a short program in Excel, which based on the entered age in cell A2 and will return word when a person age is under or below 18. The result should appear in cell B2.

Sub ITEExample()

If Range("A2") = 18 Then
 Range("B2") = "18 years old or more"
 Else: Range("B2") = "Less than 18 years old"
End If
End Sub

Move from the VBA editor to your workbook. You enter the sheet in which you want to check your program. In this case it will be “Sheet1”. Click on the “Developer’s tab” tab and select the program from the macros.

Extension of IF THEN ELSE instruction: IF THEN ELSEIF

The code from previous program is simple and includes the possibility of obtaining only two different results. It also does not include the possibility of entering dates, texts, negative numbers, etc. If you want your program to return 3 or more different results depending on entered data, you need to modify your code with the ELSE IF clause. ELSE IF is the instruction that can be used to nest two or more functions IF (CONDITION_1; CODE_1, IF (CONDITION_2; CODE2; CODE_BLOCK_3)). Let’s try to improve our program by the following conditions:

• Base your program on defined variables
• The data will be enter in the InputBox window , and the result will be displayed in the MsgBox message
• For negative numbers and more than 100, it will present a message about incorrectly entered data

At the very beginning, try to write a program that checks first 3 conditions. You can use the Variant data type here, so that you can enter both text and numeric data:

Sub ITEExample2()
 
'vars def
Dim VarAge As Variant
Dim StrResult As String
 
VarAge = InputBox("Enter your age")
If VarAge > 100 Then
StrResult = "Please enter right data"
ElseIf VarAge >= 18 Then
StrResult = "18 years old or more"
ElseIf VarAge < 0 Then
StrResult = "Please enter right data"
ElseIf VarAge < 18 Then
StrResult = "Less than 18 years old"
End If
 
MsgBox StrResult
End Sub

VBA Nested IF Statement using ELSEIF

Your code already check all the possibilities, if you put the age as a integer number. Let’s now try to improve your code with conditions such as:

• Enter a message with incorrectly entered data for non-numerical values
• The age with the decimal place is rounded to the integer number

To this time, you will learn IF THEN ELSEIF. If you round the number with a decimal place, you will use the simple Round () function. You to check the variable is numeric using the IsNumeric function .

Sub ITEExample3()
 
'vars def
Dim VarAge As Variant
Dim StrResult As String
 
VarAge = InputBox("Enter your age")
 
If IsNumeric(VarAge) = True Then
VarAge = Round(VarAge, 0) 'rounds to integer

 If VarAge > 100 Then
 StrResult = "Please enter right data"
 ElseIf VarAge >= 18 Then
 StrResult = "18 years old or more"
 ElseIf VarAge < 0 Then
 StrResult = "18 years old or more"
 ElseIf VarAge < 18 Then
 StrResult = "Less than 18 years old"
 End If 'end of nested statement
 Else: StrResult = "Please enter numeric value."

End If 'end of statement 
 
MsgBox StrResult
End Sub

Tasks (You can enter the solution in a comment)

1. Using the data in the sheet, write a program that determines whether cell A1 is empty or filled.
2. In analogy to the example in this chapter, write a program which, based on the date of birth entered, determines whether the person is of age or underage.
3. Modify in any way the example from this chapter using the logical operator “OR”.