Vba excel condition or. Conditional statement in VBA. If condition then expression

The use of the conditional operator is an integral part of almost any programming language. And in fact, everything comes from mathematics, since initially, programming languages ​​were formed as tools for solving various problems. I’ll say right away that the article is boring and uninteresting. It’s simply physically impossible to squeeze anything new out of programming, in particular from the description of operators, but I’ll come up with something. And so, in the VBA language, the if statement is used to check conditions, which can exist in several variations. But first, a little humor...

A hare runs through the forest and we see a bear reading a book. The hare asks: “Clubfoot, what are you reading?” He answers with a smart look: “Logic.” Kosoy, looking surprised, asks, “What is logic?” “Well, look,” says the bear:

• M: Do you have matches?
• Z: No
• M: So you don't smoke
• Z: Yes!
• M: If you don’t smoke, then there’s no extra spending money
• Z: Right!
• M: So you go after girls?
• Z: Of course!
• M: And since you go after girls, it means you are not impotent!
• Z: Correct again!

The hare was delighted with such a clever book and asked the clubfoot to read it. He walks through the forest contentedly and rejoices, then a wolf comes towards him. The hare decided to try logic on gray and asks him:

• Z: Do you have matches?
• B: Yes
• Z: So you are impotent!

Well, yes, I retold the joke as I remembered

Okay, now let's get down to theory...

Option 1. The simplest use of the VBA if conditional statement is when a condition is checked, and if it is true, then one expression is executed:

If condition then expression

Option 2. A situation may arise when, after checking a condition, several expressions need to be executed, in which case the if statement takes the form:

If condition then

Expression 1

Expression 2

Expression N

As you can see, in this case the closing block if ... end if is used; it is also relevant in the case when statements are nested.

Option 3: To avoid creating nested conditional statements, use the following construction:

If condition then

Expression 1

Expression 2

Everything here is extremely simple: if the condition is true, the first expression is executed, if not, the second. It is worth remembering that after checking the condition, several statements may follow, as in the option above.

Option 4: Checking multiple conditions:

If condition 1 then

Expressions 1

ElseIf condition 2 then

Expressions 2

ElseIf condition 3 then

Expressions 3

The operating logic is as follows: first, the first condition is checked, if it is true, then the first expressions are executed and then the transition to the operator occurs, which follows End If. If the first condition is not met, the second is checked, and so on, the use of an Else expression at the end is optional, it can be followed by a statement that must be executed if none of the previous conditions are met.

Okay, now let's write a simple example of using the VBA if then construct. Create a form in the Visual Basic for Applications editor and add three text labels () and one button () to its surface. In the code editor we will write:

In the GetSub procedure, a loop is used, the condition for executing the loop is that the value of the MySumm variable must be less than or exactly 10000. The body of the loop itself uses VBA if then statements with several options. The conditions are checked: if the amount reaches the value of 10, then write one information in the first text field, if 100 - another information, if 1000 - the third information, otherwise - write the data in the Caption property of the fourth text field.

Single-line and multi-line If...Then...Else statement constructs and IIf function used in VBA Excel code - syntax, components, examples.

If...Then...Else Statement

Operator If...Then...Else is designed to transfer control to one of the operator blocks depending on the results of the conditions being checked.

One-line construction

Operator If...Then...Else can be used in a one-line construct without keywords Else, End If.

Syntax of the single-line If...Then...

One-Line Components If...Then...

• condition True or False;
• operators condition returns value True;

If the component condition returns value False, block of construction operators If...Then...

Example 1

Multiline construction

Syntax of multiline If...Then...Else construct

Components of a multi-line design If...Then...Else:

• condition- numeric or string expression following the keyword If or ElseIf and returning a boolean value True or False;
• operators- a block of VBA Excel code statements that is executed if the component condition returns value True.
• dotted line denotes additional building blocks from a string ElseIf [condition] Then and strings [operators];
• block of statements after the keyword Else executed anyway, but the code block from the line Else and strings [operators] is optional and can be omitted.

If the component condition returns value False, the following block of construction operators If...Then...Else is skipped and program control is transferred to the next line of code.

The simplest version of a multi-line design If...Then...Else:

Example 2

Sub primer2() Dim d As Integer, a As String d = InputBox("Enter a number from 1 to 40", "Example 2", 1) If d< 11 Then a = "Число " & d & " входит в первую десятку" ElseIf d >10 And d< 21 Then a = "Число " & d & " входит во вторую десятку" ElseIf d >20 And d< 31 Then a = "Число " & d & " входит в третью десятку" Else a = "Число " & d & " входит в четвертую десятку" End If MsgBox a End Sub

IIf function

Function IIf checks a given condition and returns a value depending on the result of the test.

Function syntax

Components of the IIf function

• condition- a numeric or string expression that returns a Boolean value True or False;
• if True IIf, If condition returned value True;
• if False- the value that the function returns IIf, If condition returned value False.

Example 3

When you click the “Cancel” button or close the InputBox dialog box from the examples with a cross, an error is generated, since in these cases it returns an empty string. Assigning an empty string to an Integer variable d causes an error. When you click the “OK” button in the dialog box, VBA Excel automatically converts the numbers entered into the input field in text format into the numeric format of the variable d.

The most important conditional operators used in Excel VBA are the operators If...Then And Select Case. Both of these expressions test one or more conditions and, depending on the result, will perform different actions. We'll talk more about these two conditional operators next.

"If...Then" Statement in Visual Basic

Operator If...Then checks the condition and, if it is TRUE, then the specified set of actions is performed. A set of actions can also be defined that should be performed if the condition is false (FALSE).

Operator syntax If...Then like this:

If Condition1 Then
Actions if Condition1 is met
ElseIf Condition2 Then
Actions if Condition2 is met
Else
Actions if none of the Conditions are met
End If

In this expression the elements ElseIf And Else operator conditions may not be used if they are not necessary.

Below is an example in which, using the operator If...Then The fill color of the active cell changes depending on the value it contains:

If ActiveCell.Value< 5 Then ActiveCell.Interior.Color = 65280 "Ячейка окрашивается в зелёный цвет ElseIf ActiveCell.Value < 10 Then ActiveCell.Interior.Color = 49407 "Ячейка окрашивается в оранжевый цвет Else ActiveCell.Interior.Color = 255 "Ячейка окрашивается в красный цвет End If

Note that once the condition becomes true, the execution of the conditional statement is interrupted. Therefore, if the value of the variable ActiveCell less than 5, then the first condition becomes true and the cell is colored green. After this, execution of the statement If...Then is interrupted and the remaining conditions are not checked.

Select Case Statement in Visual Basic

Operator Select Case similar to operator If...Then in that it also checks the truth of the condition and, depending on the result, chooses one of the options.

Operator syntax Select Case like this:

Select Case Expression
Case Value1
Actions if the result of the Expression matches Value1
Case Value2
Actions if the result of the Expression matches Value2
…
Case Else
Actions if the result of the Expression does not match any of the listed options Values
End Select

Element Case Else is optional, but is recommended to handle unexpected values.

In the following example, using the construct Select Case The fill color of the current cell changes depending on the value in it:

Select Case ActiveCell.Value Case Is<= 5 ActiveCell.Interior.Color = 65280 "Ячейка окрашивается в зелёный цвет Case 6, 7, 8, 9 ActiveCell.Interior.Color = 49407 "Ячейка окрашивается в оранжевый цвет Case 10 ActiveCell.Interior.Color = 65535 "Ячейка окрашивается в жёлтый цвет Case 11 To 20 ActiveCell.Interior.Color = 10498160 "Ячейка окрашивается в лиловый цвет Case Else ActiveCell.Interior.Color = 255 "Ячейка окрашивается в красный цвет End Select

The example above shows how you can set a value for an element in different ways Case in design Select Case. These are the methods:

As soon as one of the conditions is found, the corresponding actions are performed and the structure is exited Select Case. That is, in any case, only one of the listed branches will be executed Case.

If...Then...Else statement.

This lesson will focus on the topic of operators. In particular, we will get acquainted with the If...Then...Else operator, and also get acquainted with arithmetic operators and comparison operators, without which we will not be able to use the If...Then...Else operator to the maximum extent possible.

Visual Basic has a fairly large number of different operators, some of which can boil the mind of even an experienced programmer. In order not to “boil”, we will consider only the most basic operators on which 95% of programs work, we will study the remaining operators as needed, this way you will not have to memorize an entire textbook and can immediately move on to practical exercises.

By posting this lesson, I assume that you have knowledge of primary school mathematics.
What kind of knowledge? These are the same examples in which we subtracted, added, multiplied,... Do you remember this? Great! This means there is still gunpowder in the flasks.

Before you start studying the If...Then...Else operator, you need to get acquainted with arithmetic and logical operators, without which we cannot apply the If...Then...Else operator from the point of view of deep practice. Okay, enough of the boring instructions and digressions, sit back, let's get down to business!

Arithmetic operators
Using these operators you can; divide, multiply, add, etc.
Everything here is as simple as in elementary school.

+ (addition)
- (subtraction)
* (multiplication)
/ (division with remainder) at school the division sign was this sign : remember these differences and don't get confused
\ (integer division, without remainder)

Comparison Operators
These are quite important operators. These operators can be used to compare numbers, strings, and other data. Without comparison operators, it is impossible to build logic or branching in a program.
It is important to remember that comparison operators compare two expressions and return a Boolean value in the form of True (if the condition is true) and False (if the condition is not true).
True translated from English means - Truth
False translated from English means - Lie
Right off the bat, this may seem incomprehensible, but don’t worry, a little later you will understand what all this devilry means.

= (equal)
The value of the first expression is equal to the value of the second expression, or it can be the result of addition/subtraction/... of two calculations.

<> (inequality)
The value of the first expression is not equal to the value of the second expression

< (less)
The value of the first expression is less than the value of the second expression

> (more)
The value of the first expression is greater than the value of the second

<= (less than or equal)
The value of the first expression is less than or equal to the value of the second

>= (greater than or equal to)
The value of the first expression is greater than or equal to the value of the second

I see, I see that you are already feeling a little scared, in fact there is nothing scary!
Read on and you will see that everything is quite simple.
Below we will thoroughly support everything with examples and practice.
And so, we have considered arithmetic and logical operators, now we can go directly to the If...Then...Else operator itself

If...Then...Else Statement
First, let's look at what the operator's words mean when translated from English into Russian.
If is translated as If
Then is translated as That
Else is translated as Otherwise

What is the If...Then...Else statement used for?
Using this operator, you can branch programs; without this operator it is impossible to write a serious program.
Let's move on to typical examples.

How does a computer process the If... statement?
Let's say we have two variables:

Dim var1 = 10 Dim var2 = 5

and let's say we have a condition:

If var1 > var2 Then MsgBox("Yes, var1 is greater than var2") End If

How does the condition shown above work?
The computer will look at the variables var1 and var2, if var1 is indeed greater than var2, then the condition is true (True) and the computer will show MsgBox.
If the condition is not true (False), the computer will not show MsgBox.
You can verify this by changing the values ​​of the variables to other numbers so that the condition is not true, then we will not see MsgBox.

Attention!

To study the examples you need.
After creating a project on the form, you must then double-click on the button with the left mouse button, the code editor will open, and the cursor will be placed in the place where you need to insert the code from the examples given below.
After inserting the code, you need to look at the result by clicking on the button located on the form.

Arithmetic operators
+ (addition)
- (subtraction)
* (multiplication)
/ (division)

Spoiler: Arithmetic examples - CLICK

+ Addition:

"The example shows how you can add two numbers Dim chislo1 As Integer = 5 Dim chislo2 As Integer = 5 Dim resultat As Integer resultat = chislo1 + chislo2 MsgBox(resultat)

"The example shows how you can get the difference of two numbers (subtraction) Dim chislo1 As Integer = 5 Dim chislo2 As Integer = 5 Dim resultat As Integer resultat = chislo1 - chislo2 MsgBox(resultat)

"The example shows how you can multiply one number by another Dim chislo1 As Integer = 5 Dim chislo2 As Integer = 5 Dim resultat As Integer resultat = chislo1 * chislo2 MsgBox(resultat)

"The example shows how you can divide one number by another Dim chislo1 As Integer = 5 Dim chislo2 As Integer = 5 Dim resultat As Integer resultat = chislo1 / chislo2 MsgBox(resultat)

Comparison Operators
= (equal)
<> (inequality)
< (less)
> (more)
<= (less than or equal)
>= (greater than or equal to)

Spoiler: Comparison Operators - CLICK

= Equal to:

"The example shows how you can find out whether the text in a variable is equal to another text that we specified Dim stroka As String = "Hello NubClub!" "If string is equal to the text in double quotes then show me MsgBox If stroka = "Hello NubClub!" Then MsgBox("The variable is equal to the text we are looking for!") End If

"The example shows how you can find out whether the text in a variable is equal to another text that we specified Dim stroka As String = "Hello NubClub!" "If string is not equal to the text in double quotes then show me MsgBox If string<>"I'm a novice programmer" Then MsgBox("The text in the variable stroka is not equal to the text in quotes!") End If

"The example shows how you can compare two numbers Dim chislo1 As Integer = 10 Dim chislo2 As Integer = 5 "If chislo2 is less than chislo1 then show me MsgBox If chislo2< chislo1 Then MsgBox("Значение переменной chislo2 меньше значения переменной chislo1") End If

"The example shows how you can compare two numbers Dim chislo1 As Integer = 10 Dim chislo2 As Integer = 5 "If chislo1 is greater than chislo2 then show me MsgBox If chislo1 > chislo2 Then MsgBox("The value of the variable chislo1 is greater than the value of the variable chislo2") End If

"The example shows how you can compare two numbers Dim chislo1 As Integer = 10 Dim chislo2 As Integer = 5 "If chislo2 is less than or equal to chislo1 then show me MsgBox If chislo2<= chislo1 Then MsgBox("Значение переменной chislo2 меньше или равно значению переменной chislo1") End If

"The example shows how you can compare two numbers Dim chislo1 As Integer = 10 Dim chislo2 As Integer = 5 "If chislo1 is greater than or equal to chislo2 then show me MsgBox If chislo1 >= chislo2 Then MsgBox("The value of the variable chislo1 is greater than or equal to the value of the variable chislo2" ) End If

If...Then...Else Statement - Branching
Above were fairly simple examples of using If...Then conditions, i.e. without branching and without using Else.
Now let's look at examples with branching, they are a little more complicated, but you can't do without them.
At its core, using Else, all examples will remain the same as above, only a small branching of the program will be added.
What will be added?
If previously MsgBox appeared only when the condition was true, then using Else you can call MsgBox even when the condition is not true.

And so, an example.

"The example shows how you can find out whether the text in a variable is equal to another text that we specified Dim stroka As String = "Hello NubClub!" "If string is equal to the text in double quotes then show me the MsgBox with the text - The variable is equal to the text we are looking for! "If strka is NOT equal to the text in double quotes then show me the MsgBox with the text - The variable is not equal to the text we are looking for! If strka = "Hello NubClub!" Then MsgBox("The variable is equal to the text we are looking for!") Else MsgBox( "The variable is not equal to the text we are looking for!") End If

There are times when we have one variable and several values ​​with text that need to be checked. What to do then? Take off your pants and run?!
No, this is not our method
There is a more elegant solution. We can complicate our condition using ElseIf and thus check as many rows as we like, within reasonable limits, of course. If there are a lot of lines, then loops will come to the rescue, which we will work with in the next lessons.

Let's look at an example.
Let's imagine that we need to create logic for a street traffic light so that it works and shows colors.
How to do this?
It's actually not difficult, here's an example.

Conditional operator

Conditional operator VBA allows you to check a certain condition and, depending on the results of the check, perform one or another action. Thus, the conditional operator is a means of branching the computational process.

In VBA There are 2 types of conditional statements: linear and block.

1. Linear conditional statement is used to execute any one statement if some condition is true.

The syntactic construction of a linear operator has two forms: non-alternative, alternative.

Structure of a non-alternative conditional operator (short version):

If<условие>Then<оператор 1>

Structure of the alternative conditional statement (full version):

If<условие>Then<оператор 1>Else<оператор 2>

Where

If, Then, Else - reserved words (if, then, otherwise);

<условие>- arbitrary expression of logical type;

<оператор 1>, <оператор 2>- any language operators VBA.

Job. First the conditional expression is evaluated<условие>. If there is a result True (true), then fulfilled<оператор 1>, A<оператор 2>skipped. If there is a result False (false), on the contrary,<оператор 1>is skipped and executed<оператор 2>.

2. A block conditional operator is used when, if the condition is true, it is necessary to execute several program statements (a block of statements). The block operator has two forms: unopposed and alternative.

Structure of the uncontested block operator (short version)

If<условие>Then

<оператор1>

<оператор2>

…………….

<оператор n >

End If

Where

End If - indicates the end of a statement block If.

Alternate block statement structure:

If<условие>Then

<оператор1>

<оператор2>

…………….

<оператор n >

Else

<оператор1>

<оператор2>

…………….

<оператор n >

End If

Example 1.

Statement of the problem. Create a custom procedure for calculating an equation of the form in a standard module ax 2 + bx + c = 0.

1. Initial data:

a , b , c  R

Result: x1, x2  R .

2.Type the following custom procedure in the standard project module:

Private Sub yravnenie ()

a = InputBox("a=", a)

b = InputBox("b=", b)

c = InputBox("c=", c)

d = b^2 - 4 * a * c

If d >= 0 Then

x1 = (-b + Sqr(d)) / (2 * a)

x2 = (-b - Sqr(d)) / (2 * a)

MsgBox (x1)

MsgBox (x2)

Else

MsgBox("No solutions")

End If

End Sub

Alternative block operator If is used in cases where, when a condition is met, it is necessary to carry out one set of program operators, and when it is not met, another.

IF statements can be nested inside each other. This operator nesting is used when you need to test a condition against another condition that is true.

Nested Statement Format If:

If<условие1>Then

If<условие2>Then

<оператор1>

<оператор2>

…………….

<оператор n >

Else

<оператор1>

<оператор2>

…………….

< оператор n>

End If

End If

Example 2.

Statement of the problem. Create a custom function in a standard module for finding the maximum among three given numbers y 1 = a +2* b ; y 2 = a * b + c ; y 3 = c 2 + 1.

Task execution technology:

1. Initial data:

a , b , c  R

Result: Max  R.

2.Type the following custom function in the standard project module:

Function y (a,b,c)

y1 = a+2*b

y2 = a*b+c

y3 = c^2+1

If y1 > y2 Then

If y1 > y3 Then y = y1 Else y = y3

Else

If y2 > y3 Then y = y2 Else y = y3

End If

End Function

3.Calculate the roots of a quadratic equation with arbitrary initial data.

When using nested statements If It is important not to confuse the options for combining conditions. The rule to remember is: alternative Else is considered to belong to the nearest operator If , which does not have a branch Else.

In VBA Designed for multi-operator operation If . These operators are used in cases where more conditions need to be considered in addition to the original one. This is what the design does: If...Then...ElseIf . Unlike nested statements, multiple statement construction If allows you to check an additional condition if the original condition evaluates to False.

Recording format:

If<условие1>Then

<оператор1>

ElseIf<условие2>Then

<оператор2>

Else

<.оператор3>

EndIf

Example 3.

Statement of the problem. A sales manager needs to develop a function that allows him to calculate commissions. The commission percentage depends on the volume of goods sold and is calculated according to the following rule presented in table 15.

Commission calculation rules

Task execution technology:

1. Initial data:

Sales  Z .

Result: Commissions R .

2. Create a custom function to calculate commissions in the standard module:

Function Commission (Sales)

If Sales<= 9999 Then

Commission = Sales * 0.08

ElseIf Sales<= 19999 Then

Commission = Sales * 0.1

ElseIf Sales<= 39999 Then

Commission = Sales * 0.12

Else

Commission = Sales * 0.14

End If

End Function

3. Calculate.