If vba excel description. Methodical instructions and tasks. If condition then expression

If ... Then ... Else statement.

This lesson will be devoted to such a topic as - operators. In particular, we will get acquainted with the If ... Then ... Else operator, as well as get acquainted with the arithmetic and comparison operators, without which the If ... Then ... Else operator can not be applied to the maximum.

Visual Basic has a fairly large number of different operators, some of them can boil the brain of even a seasoned programmer. In order not to "boil", we will consider only the most basic operators on which 95% of the programs work, we will study the remaining operators as needed, so you do not have to memorize a whole textbook and you can immediately go on to practical exercises.

By posting this lesson, I mean that you have knowledge of elementary school mathematics.
What kind of knowledge? These are the very examples in which we subtracted, added, multiplied, ... Do you remember that? Fine! So 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 the If ... Then ... Else operator cannot be applied from the point of view of deep practice. Okay, enough boring instructions and digressions, sit back and get down to business!

Arithmetic operators
With the help of these operators, you can; divide, multiply, add, etc.
Everything 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 do not confuse
\ (integer division, no remainder)

Comparison Operators
These are pretty important operators. With these operators, you can 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 of the form True (if the condition is true) and False (if the condition is not true).
True translated from English means - True
False in translation from English means - False
On the move, it may seem incomprehensible, but do not 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

< (smaller)
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 to)
The value of the first expression is less than or equal to the value of the second

>= (more or equal)
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 scared, in fact, there is nothing terrible!
Read on and you will see that everything is pretty simple.
Below we will thoroughly back everything up with examples and in practice.
And so, we examined the arithmetic and logical operators, now you can go directly to the If ... Then ... Else operator itself.

If ... Then ... Else Statement
First, let's see what the operator's words mean when translated from English into Russian.
If translates as If
Then translates as To
Else translates to Else

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 the computer handle 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 above condition 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 the MsgBox.

Attention!

To study examples it is necessary.
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 positioned in the place where you need to insert the code from the examples below.
After inserting the code, you must also look at the result by clicking on the button located on the form.

Examples of
The following will be examples in the following sequence.

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)

- Subtraction:

"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)

* Multiplication:

"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)

/ Division:

"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)
< (smaller)
> (more)
<= (less than or equal to)
>= (more or equal)

Spoiler: Comparison Operators - CLICK

= Equal to:

"The example shows how you can find out if the text in a variable is equal to another text that we have specified. Dim stroka As String =" Hello NubClub! "" If stroka 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're looking for!") End If

<>Inequality:

"The example shows how you can find out if the text in a variable is equal to another text that we have specified Dim stroka As String =" Hello NubClub! "" If stroka is not equal to the text in double quotes, then show me MsgBox If stroka<>"I'm a beginner programmer" Then MsgBox ("The text in the stroka variable 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

> More:

"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

<= Меньше или равно:

"An 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

> = Greater than or equal:

"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
The above have been pretty simple examples using If ... Then conditions, i.e. no branching and no Else.
Now let's take a look at some examples with branching, they are a little more complicated, but without them nowhere.
In essence, with the use of Else, all examples will remain the same as above, only a small branching of the program will be added.
What will be added?
If earlier 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 if the text in a variable is equal to another text that we have specified. Dim stroka As String =" Hello NubClub! " "If stroka is NOT EQUAL to the text in double quotes then show me the MsgBox with the text - Variable is not equal to the text we are looking for! If stroka =" 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. You can complicate our condition using ElseIf and thus check as many lines as you like, within reasonable limits, of course. If there are a lot of lines, then loops will come to the rescue, with which we will work in the next lessons.

Let's look at an example.
Let's imagine that we need to create logic for a street traffic light to make it work and show colors.
How to do it?
It's actually not difficult, an example.

In Visual Basic for Application (VBA), there are several ways to change the order of execution of statements. VBA control structures are very similar to similar structures in other programming languages.

Basic VBA control structures:

If Then Else "End If

For Next

While wend

Do loop

Select Case "End Select

For Each Next

If Then Else "End If Control Statement
This statement changes the order of execution of statements depending on the results of checking a given condition (or a set of conditions)

Example:

Sub TestIfThen ()

iData = "pashulka"

If iData = "Excel" Then
MsgBox "You will never see this message !!!"
ElseIf iData = "Office" Then
MsgBox "Unfortunately, you won't see this message either !!!"
Else
MsgBox "This message will appear anyway", iData
End If

Comments:
If - keyword, indicating the start of the check
iData- a variable whose value will be checked
Then- a keyword indicating the end of the check
MsgBox- message
ElseIf- a keyword meaning the start of a new check
Then- a keyword indicating the end of an additional check
MsgBox- message
Else- a keyword denoting the end of a block of instructions executed with a positive result of the check, and the beginning of a block of instructions executed with a negative result.
MsgBox- message
End If

In the If Then Else "End If statement, the keywords ElseIf and Else are optional.

For Next Control Statement
This instruction allows you to perform certain actions the required number of times. Such instructions are commonly referred to as loops.

Example:

Sub TestForNext ()

For iCount = 1 To 100 Step 2
i = i + 1
Next

Comments:
For
iCount- a variable that determines the number of repetitions of the cycle.
1 - the initial value of the counter
To- a keyword separating the start and end value of the counter
100 - the final value of the counter
Step- a keyword that defines the step of the counter
2 - counter step (number)
Next- a keyword, upon reaching which the counter value changes by the step size, after which the control again goes to the beginning of the cycle.
MsgBox

In the For Next statement, the keyword Step is optional. If it is absent, the step of the counter will be equal to 1.

important The counter step must be a number. If the step of the counter is specified as a negative number, then the value of the counter will decrease with each cycle.

Example, with a countdown:

Sub TestForNext2 ()

For iCount = 300 To 1 Step -3
i = i + 1
If iCount = 153 Then Exit For
Next iCount

MsgBox "Number of repetitions:" & i

Pay attention to:
If iCount = 153 Then- in this VBA code, we have applied the already known If Then Else statement
Exit For
Next iCount- the code contains a variable (counter), which is located after the keyword. This syntax is usually used for visual definition end of the loop if there are enough such instructions in the VBA code.

While Wend Control Statement
This instruction allows you to perform certain actions as long as a given condition is met.

Example:

Sub TestWhileWend ()

While iCount iCount = iCount + 1
Wend

Comments:
While- a keyword that means the beginning of a cycle
iCount - the condition under which this instruction will be executed
Wend
MsgBox- message about the number of repetitions

Do Loop Control Instruction
This instruction allows you to perform certain actions not only as long as the specified condition is met, but also most of the time until the specified condition is met.

Example:

Sub TestDoLoop ()

Do Until iCount> = 100
iCount = iCount + 1
Loop

MsgBox "Number of repetitions:" & iCount

Comments:
Do- a keyword that means the beginning of a cycle
Until- a keyword denoting the presence of a condition
iCount> = 100- the condition under which this instruction will be executed
Loop- a keyword that means the end of the cycle
MsgBox- message about the number of repetitions

Sub TestDoLoop2 ()

Do
iCount = iCount + 1
If iCount = 50 Then Exit Do
Loop Until iCount = 100

MsgBox "Number of repetitions:" & iCount

Pay attention to:
If iCount = 50 Then- in this VBA code, we again applied the already known If Then Else statement
Exit Do- an instruction meaning an early exit from the cycle
iCount = 100- the condition under which this instruction will be executed

Note:
The condition specified at the end of the loop guarantees that it will be executed at least once. Be careful with the instructions While Wend, Do Loop, because if the condition is set incorrectly, you can almost get a perpetuum mobile.

Select Case "End Select Control Statement
This statement, depending on the results of the value of the variable or expression, executes one of several pieces of code.

Example:

Sub TestSelectCase ()

Select Case iNumeric
Case 1

Case 2, 5
MsgBox "You shouldn't see this message"
Case 10 To 13
MsgBox "You shouldn't see this message"
Case Is> 14
MsgBox "This is the message you should see"
Case else
MsgBox "This message will appear if the variable is End Select

Comments:
Select Case
iNumeric- a variable or expression, the value of which determines the choice of a fragment of VBA code for execution
Case 1- the first variant of the value to be compared.
If the value of the variable coincides with it, then the code fragment following the Case will be executed, but before the next key Case, then the statement will complete its work. Otherwise, the check will continue.
Case 2, 5- the second variant of the value.
Case 10 To 13- the third variant of the meaning.
Case Is> 14- the fourth variant of the meaning.
MsgBox- the message that will be displayed in this case
Case else- keywords denoting code execution if none of the previous options matched.
End Select- a keyword denoting the end of the instruction

In the Select Case statement "End Select, the keyword Case else is optional.

For Each Next Control Statement
This instruction allows you to perform certain actions with each object of the family or array element.

An example with a Range object:

Sub TestForEachNextRange ()

i = i + 1
iCell.Value = "(! LANG: Cell #" & i !}
Next

MsgBox "Number of cells:" & i

Comments:
For Each- keywords denoting the beginning of the instruction
iCell- a variable to which the values ​​of elements of a group (array or family) are assigned. To work with elements of an array, the variable must be of the Variant type.
In- a keyword separating a variable from a group
Range ("A1: C5")- group, i.e. an array or family of objects. The number of iterations of the loop depends on the number of elements included in the group. At the first execution of the loop, the variable is assigned the value of the very first element of the group, and then, of all subsequent ones.
Next- a keyword, upon reaching which the control again goes to the beginning of the cycle.
MsgBox- message about the number of elements in the group.

In this case, using such a counting of the number of cells in the Range object is not the best way, since this object has a property that will cope with this task much better: Range object: its properties and methods

An example of manipulating the Range object:

Sub TestForEachNextRangeChanges ()

For Each iCell In Range ("A1: C5")

i = i + 1: iCell.Value = i

If iCell.Value 5 Then
iCell.Font.Size = 15
iCell.Interior.ColorIndex = 50
ElseIf iCell.Value = 13 Or iCell.Value = 5 Then
iCell.Font.Size = 20
iCell.Interior.ColorIndex = 3
Else
iCell.Clear
End If

Pay attention to:
- in this VBA code, to check the values ​​in the cell, we used the already known If Then Else statement.
- depending on which condition passed the check, a certain piece of code is executed that uses various properties of the Range object.

An example with the WorkSheets family:

Sub TestForEachNextSheet ()

For Each iList In Worksheets
i = i + 1
iNameList = iNameList & Chr (10) & iList.Name
Next

MsgBox "Number of worksheets:" & i & Chr (10) & _
"Worksheet names:" & iNameList

In this case, using such a count of the number of worksheets is not the best way, since this object also has a property that will cope with this task much better.

Conditions are very useful in programming, as they allow us to perform actions, depending on the established criteria (the same principle is used as in IF Excel functions).

The most important function that sets the condition is IF and now we'll see how it works:

If [CONDITION HERE] Then "=> IF the condition is true, THEN" Instructions if "true" Else "=> OTHERWISE" Instructions if "false" End If

Let's get into practice and go back to the example we used in the variable tutorial. The purpose of this procedure was to open a dialog box that would contain the value from the string specified in the cell F5:

If you enter a letter into a cell F5, this will result in an error. We want to prevent this from happening.

Sub variables () "Declaring variables Dim last_name As String, first_name As String, age As Integer, row_number As Integer" Assigning values ​​to variables row_number = Range ("F5") + 1 last_name = Cells (row_number, 1) first_name = Cells (row_number , 2) age = Cells (row_number, 3) "MsgBox dialog last_name &" "& first_name &", "& age &" years "End Sub

Let's add a condition that will check if the entered value is in the cell F5 a number before the code is executed.

We will use the function IsNumeric to check the condition:

Sub variables () "If the value in brackets (cell F5) is numeric (AND THIS IS THE IF CONDITION CORRECT) then" follow the instructions that follow THEN If IsNumeric (Range ("F5")) Then "Declare Variables Dim last_name As String , first_name As String, age As Integer, row_number As Integer "Assigning values ​​to variables row_number = Range (" F5 ") + 1 last_name = Cells (row_number, 1) first_name = Cells (row_number, 2) age = Cells (row_number, 3) "MsgBox dialog box last_name &" "& first_name &", "& age &" years "End If End Sub

We also need to write instructions if the condition we set is not met:

Sub variables () If IsNumeric (Range ("F5")) Then "If the condition is met" Declaring variables Dim last_name As String, first_name As String, age As Integer, row_number As Integer "Assigning values ​​to variables row_number = Range (" F5 ") + 1 last_name = Cells (row_number, 1) first_name = Cells (row_number, 2) age = Cells (row_number, 3) "MsgBox dialog last_name &" "& first_name &", "& age &" years "Else" If condition does not execute "Dialog Box: Warning MsgBox" The entered value "& Range (" F5 ") &" is not valid! " "Deleting the contents of cell F5 Range (" F5 "). ClearContents End If End Sub

Now a non-numeric value will not cause any problems.

Working with our array that contains 16 rows of data, our next step will be to check if the variable row_number is "greater than or equal to 2" and "less than or equal to 17".

But first, let's take a look at the comparison operators:

and these useful operators:

Now let's add one of the above conditions AND between comparison operators:

Sub variables () If IsNumeric (Range ("F5")) Then "If numeric value Dim last_name As String, first_name As String, age As Integer, row_number As Integer row_number = Range (" F5 ") + 1 If row_number> = 2 And row_number

If we want to make our macro more practical, we can replace 17 to a variable that would contain the number of lines. This would allow us to add and remove rows from the array without having to change this limit every time.

In order to do this, we must create a variable nb_rows and add this function.

In this case, we use the function WorksheetFunction.CountA, which is analogous to the function COUNTA in Excel itself.

We want this function to count the number of non-empty cells in the first column by writing the resulting value to a variable nb_rows:

Sub variables () If IsNumeric (Range ("F5")) Then "IF NUMBER Dim last_name As String, first_name As String, age As Integer, row_number As Integer Dim nb_rows As Integer row_number = Range (" F5 ") + 1 nb_rows = WorksheetFunction.CountA (Range ("A: A")) "Function for counting the number of rows If row_number> = 2 And row_number

ElseIf

ElseIf makes it possible to add additional conditions after the IF command:

If [CONDITION 1] Then "=> IF condition 1 is true, THEN" Statements 1 ElseIf [CONDITION 2] Then "=> IF condition 1 is false, but condition 2 is true, THEN" Statements 2 Else "=> ELSE" Statements 3 End If

If CONDITION 1 performed, Instruction 1 will execute and leave the operator IF(which starts with IF and ends with End If). If CONDITION 2 takes on the meaning " Lying ", then it will be executed Instruction 2, and if it in turn returns " Lying ", then Instruction 3(under Else) will be executed.

Sub scores_comment () "Variables Dim note As Integer, score_comment As String note = Range (" A1 ")" Comments based on the score received If note = 6 Then score_comment = "Great score!" ElseIf note = 5 Then score_comment = "Good score" ElseIf note = 4 Then score_comment = "Satisfactory score" ElseIf note = 3 Then score_comment = "Bad score" ElseIf note = 2 Then score_comment = "Bad score" ElseIf note = 1 Then score_comment = "Terrible score" Else score_comment = "Zero score" End If "Comment in cell B1 Range (" B1 ") = score_comment End Sub

Select

There is an alternative to using If with many ElseIf instructions, namely the command Select which is more suitable for this kind of situations.

Let's consider an example of a macro with the operator Select:

Sub scores_comment () "Variables Dim note As Integer, score_comment As String note = Range (" A1 ")" Comments based on the score received Select Case note "

It's worth noting that we could have used other comparison operators as well:

Case Is> = 6 "if value> = 6

Examples with different meanings:

Case Is = 6, 7 "if value = 6 or 7 Case Is 6, 7" if value is not 6 or 7 Case 6 To 10 "if value = any number from 6 to 10

Basic VBA Operators

Comments (0)

3.1. Operator recording rules

When recording operators, you must adhere to the following rules:

Each new statement is written on a new line.

To write multiple operators on one line, separate them with a colon (:).

If the operator does not fit on one line, then you must put a space and an underscore (_) at the end of the line, and then continue the part that does not fit on the next line.

3.2. Assignment operator

The assignment operator is used when a variable needs to be assigned a new value. It has the following syntax:

VARIABLE NAME= EXPRESSION

The expression on the right side is evaluated first, and then the result is assigned to the variable on the left side.

For example... Write the following mathematical expression using the assignment operator:

In VBA, this expression can be written as the following operator:

Y = a ^ (1/3) + (a ^ 2 + Exp (-b)) / (Sin (a) ^ 2-Log (b))

3 .3. I / O operators

3 .3.1. MsgBox operator and function

Operator MsgBox carries out information output in the dialog box and sets the waiting mode for the user to press the button.

It has the following syntax:

MsgBox Message [, Buttons] [, Title]

Arguments:

Message - obligatory argument specifying the information message to be displayed in the window. Can consist of several text lines, concatenated by a sign & ... Usage in this argument Chr (13) leads to a new line when displaying information.

Buttons - the value of this argument determines the categories of buttons that appear in the window. The value of the argument of the button also determines whether any icon appears in the window. If you do not specify which buttons should be displayed in the message window, then the default value corresponding to the OK button is used. Table 3.1 shows the possible combinations of buttons and icons in the message window.

Heading - sets the title of the window.

Function MsgBox returns an Integer indicating which button was clicked in the dialog box.

Table 3.1. Valid values ​​for a button variable

Display	Argument
OK button
OK and Cancel buttons
Yes and No buttons
Yes, No and Cancel buttons
Stop, Redo, and Ignore buttons	VbAbortRetryIgnore
Redo and Cancel buttons.
Information sign
Question mark
Exclamation mark

For example... Display a message about the current date.

MsgBox "Today is on the calendar" & Date, "Attention"

As a result, the following window will be displayed (Figure 3.1).

After clicking the OK button, the message box will close and the program will resume execution from the statement immediately behind the call to MsgBox.

3.3.2. Function InputBox

Function InputBox enters variable values ​​using the input window and has the following syntax:

VariableName = InputBox (Message [, Title] )

Arguments:

Message - required argument. Sets an informational message in the window, usually explaining the meaning of the entered value

Heading - sets the title of the window.

For example, Enter the value of the variable N from the keyboard, with a default value of 10.

To do this, you can use the following operator:

N = InputBox ("Enter N", "Input of initial data", 10)

As a result, the following window will be displayed for entering the value of the variable N (Figure 3.2).

If the default value suits the user, then after clicking the OK button, the input window will close, the variable N will be assigned the value 10, and the program execution will resume from the operator immediately behind the call. InputBox.

If the default value does not suit the user, then before clicking on the OK button, you must enter desired value variable N.

3 .4. Conditional IF statement

To implement the branching computational process in VBA, the operator is used If… Then… Else, which is the simplest form of condition checking. It has the following syntax:

IfCONDITIONThenOPERATOR_1ElseOPERATOR_2

OPERATOR_1 is executed if CONDITION true, otherwise executed OPERATOR_2. In this case, the If… Then… Else statement is written in one line.

CONDITION is a boolean expression. The result of an expression is always boolean. Expression can be simple or complex. When writing simple conditions, all possible operations of the relationship indicated in Table 1 can be used. 3.2.

table3 .2. Logical relationship

Operation	Name	Expression	Result
			True if A is equal to B
			True if A is not equal to B
			True if A is greater than B
			True if A is less than B
	More or equal		True if A is greater than or equal to B
	Less than or equal to		True if A is less than or equal to B

Complex conditions are formed from simple ones by using logical operations and parentheses. The list of logical operations is given in table. 3.3.

table3 .3. Logical operations

	Name	Expression			Result
	Logical negation
	Logical AND
	Logical OR

In a conditional statement, it is allowed to use a statement block instead of any of the statements. In this case, the conditional operator is:

IfCONDITIONThen

OPERATOR_BLOCK_1

OPERATOR_BLOCK_2

End If

Several conditions can be checked in a conditional statement. In this case, the conditional operator is:

IfCONDITION_1Then

OPERATOR_BLOCK_1

ElseIfCONDITION_2Then

OPERATOR_BLOCK_2

Else

EndIf

Example 1... Write part of the program for the algorithm in Fig. 3.3.

Example 2. Write part of the program for the algorithm in Fig. 3.4.

3.5. Select Case statement

The Select Case statement is convenient to use when, depending on the value of some expression that has a finite set of valid values, you need to perform different actions. It also refers to conditional statements, but has a different form:

Select CaseTESTED_EXPRESSION

CaseVALUES_1

OPERATORS_1

CaseVALUES_ 2

OPERATORS_ 2

. . .

CaseVALUES_N

OPERATORS_N

[ CaseElse

OTHERWISE_ OPERATORS]

End Select

TESTED_EXPRESSION can be of any scalar type except real. VALUES consist of an arbitrary number of values ​​or ranges, separated by commas.

Type of VALUES must match the type TESTED_EXPRESSION.

First, it computes TESTED_EXPRESSION... If its value matches one of the values VALUES_I, then OPERATORS_I End Select... If its value does not match any of the values VALUES_I, then OTHERWISE_OPERATORS and control is transferred to the operator standing after End Select

For example. Write part of the program for the algorithm in Fig. 3.5, which determines the value of the variable S depending on the value of the variable n.

3.6. Loop Operators

To implement a cyclic computational process, i.e., multiple execution of one or more operators, the loop operator is used For… Next which has the following syntax:

ForCOUNTER = START_VALUEToCON_VALUEStepSTEP

OPERATOR BLOCK

OPERATOR BLOCK

NextCOUNTER

The For ... Next loop iterates over the values ​​of the variable COUNTER, which is a parameter of the cycle, from the start to the end value with the specified step of change. This ensures that the block of statements of the loop body is executed at each new value of the counter. If StepSTEP is absent in the construction, then by default it is assumed that the step is 1. According to the operator Exit For it is possible to exit the loop statement before COUNTER reaches the last value. *

To iterate over objects from a group of similar objects, for example, cells from a range or array elements, it is convenient to use the loop operator For ...Each… Next.

For EachElementInGroup

BLOCK_ OPERATORS

OPERATOR BLOCK

NextElement

In VBA, other loop operators are used to organize loops with a predetermined number of repetitions:

loops with precondition - DoWhile … Loop,

DoUntil … Loop;

loops with postcondition - Do … LoopWhile,

Do … LoopUntil.

Following is the syntax for these loop statements:

"Loop with preconditionDo While … Loop

Do whileCONDITION

OPERATOR BLOCK

OPERATOR BLOCK

"Loop with preconditionDo Until … Loop

DoUntilCONDITION

OPERATOR BLOCK

OPERATOR BLOCK

"Loop with postconditionDo … Loop while

BLOCK_ OPERATORS

OPERATOR BLOCK

Loop whileCONDITION

"Loop with postconditionDo … Loop Until

BLOCK_ OPERATORS

OPERATOR BLOCK

Loop UntilCONDITION

Operator DoWhile ... Loop provides multiple repetitions of a block of statements as long as CONDITION is respected, and the operator

Other news

Using 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 means for solving various problems. I will say right away that the article is boring and uninteresting. Since it is physically impossible to squeeze out anything new from 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 test conditions, which can exist in several variations. But first, a little humor….

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

• M: Do you have any matches?
• Z: No
• M: So you don't smoke
• Z: Yes!
• M: If you do not smoke, then there is no waste of money
• Z: Right!
• M: So you walk around the girls?
• Z: Of course!
• M: And since you walk around the girls, then you are not impotent!
• Z: Right again!

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

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

Well, yes, he retold the anecdote as he remembered

Ok, now let's get down to the theory ...

Option 1... The easiest way to use the VBA if conditional statement is when a condition is checked, and if it is satisfied, then one expression is executed:

If condition then expression

Option 2... A situation may arise when, after checking the condition, several expressions must 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 if… end if block is used, it is also relevant in the case when the statements are nested.

Option 3: To avoid creating nested conditional operators, the following construction is used:

If condition then

Expression 1

Expression 2

Everything is extremely simple here, if the condition is true, the first expression is executed, if not, the second. It is worth remembering that after checking the condition, several operators can follow, as in the variant 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 logic of the work is as follows: first, the first condition is checked, if it is true, then the first expressions are executed and then a transition to the operator occurs, which follows the End If. If the first condition is not met, the second is checked, and so on, the use of the Else expression at the end is optional, it can be followed by a statement that must be executed if none of the previous conditions were 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, write:

Private Sub GetSub () Dim MySumm As Integer Dim MyVar As Byte MyVar = 0 Do While MySumm<= 10000 MySumm = MySumm + 1 If MySumm = 10 Then Label1.Caption = "Amount has reached 10" ElseIf MySumm = 100 Then Label2.Caption = "Amount has reached 100" ElseIf MySumm = 1000 Then Label3.Caption = "Amount has reached 1000" Else Label4.Caption = "Value Reached"& MySumm End If Loop End Sub Private Sub CommandButton1_Click () Call GetSub End Sub Private Sub UserForm_Initialize () Label1.Caption = "" Label1.FontSize = 13 Label1.ForeColor = vbRed Label2.Caption = "" Label2.FontSize = 13 Label2. ForeColor = vbGreen Label3.Caption = "" Label3.FontSize = 13 Label3.ForeColor = vbBlue Label4.Caption = "" Label4.FontSize = 13 CommandButton1.Caption = "Execute" End Sub

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