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)
- Using the data in the sheet, write a program that determines whether cell A1 is empty or filled.
- 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.
- Modify in any way the example from this chapter using the logical operator “OR”.