IF THEN ELSE statement in Excel VBA

What is the conditional statement If Then Else VBA

In the simplest sense, this is the equivalent of the ” If () ” function used in the VBA code. Imagine that we want our program to make decisions based on the data we enter. Depending on what data you enter at the beginning, we can get a lot of different results. The simplified operating model of the If Then Else VBA statement is given below:

Comparing the instruction with the standard function ” If (condition: code after the condition is met, the code after the condition is not met) “, the analogy of the phrases will look as follows:

What is important, our program, after fulfilling the condition, does not process the block of code stored in the Else clause. He 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 our computer in this way.

 

An example of using If Then Else conditional statement

Imagine that we want to write a short macro in Excel, which, based on the entered age in cell A2, will determine verbally whether a person is of age or underage. The result should appear in cell B2.

We move from the VBA editor to our workbook. We enter the sheet in which we want to make our program. In my case it will be “Sheet1”. Click on the “Developer” tab and select the program from the macros.

 

Extension of IF THEN ELSE instruction: IF THEN ELSEIF

The code from our previous program is obviously simplified and includes the possibility of obtaining only two different results from each other. It also does not include the possibility of entering dates, texts, negative numbers, etc. If we want our program to return 3 or more different results depending on the data, we modify our instructions for the ELSE IF clause We can compare the instruction to nested two or more functions IF (CONDITION_1; CODE_10, IF (CONDITION_2; CODE2; CODE_BLOCK_3)). Let’s try to improve our program by following the additional conditions below:

  • We base our program on entering a defined variable
  • The data is entered in the InputBox window , and the result itself is displayed in the form of the MsgBox message
  • For negative numbers and more than 100, we will present a message with incorrectly entered data

To work !

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

 

VBA Nested IF Statement

Our code already covers all the possibilities, if we introduce the age as a natural number. Let’s now try to improve our code with conditions such as:

  • We will enter a message with incorrectly entered data for non-numerical values
  • The age with the decimal place is rounded to the whole number

To this end, we will introduce IF THEN ELSEIF in the IF THEN ELSE manual. If you round the number with a decimal place, we will use the simple Round () function . We check whether the variable is numeric using the IsNumeric function .

 

Tasks (You can enter the solution in a comment)

  1. Using the character function and the If Then Else statement, write a program that determines whether it is a woman or a man based on the last letter of the name you enter. Asking for the task, let’s focus on the fact that the female names end with the letter “a”.
  2. Using the data in the sheet, write a program that determines whether cell A1 is empty or filled
  3. 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.
  4. Modify in any way the example from this chapter using the logical operator “OR”.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: