VBA statements. VBA Comparison Operators Vba Negation

Arguments:

Message - obligatory argument specifying the displayed informational message in the window. May consist of several text lines joined by the sign & . Usage in this argument Chr(13) causes a line break when outputting information.

Buttons - the value of this argument determines the categories of buttons that appear in the window. The value of the button argument also determines whether an icon appears in the window. If you do not specify which buttons to display in the message box, the default value corresponding to the OK button is used. In table. 3.1 shows possible combinations of buttons and icons in the message window.

header - sets the title of the window.

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

Table 3.1. Valid Button Variable Values

for instance. Display a message about the current date.

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

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

When the OK button is clicked, the message box will close and program execution will resume from the statement immediately following the MsgBox call.

3.3.2. Function InputBox

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

Variable_Name = InputBox(Message[, Title] )

Arguments:

Message is a required argument. Specifies an informational message in the window, usually explaining the meaning of the input value

header - sets the title of the window.

for instance, Enter the value of the variable N from the keyboard, providing a default value of 10.

You can use the following operator for this:

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

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

If the default value is suitable for the user, then after clicking the OK button, the input window will close, the variable N will be assigned the value 10, and program execution will resume from the statement immediately following the call. InputBox.

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

3 .4. Conditional IF statement

To implement a 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 performed 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 of boolean type. The expression can be simple or complex. When writing simple conditions, all possible relational operations indicated in Table 1 can be used. 3.2.

table3 .2. Logical Relations

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

table3 .3. Boolean operations

In a conditional statement, you can use a block of statements instead of any of the statements. In this case, the conditional operator looks like:

IfCONDITIONThen

OPERATOR_BLOCK_1

OPERATOR_BLOCK_2

End If

Multiple conditions can be tested in a conditional statement. In this case, the conditional operator looks like:

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 applies to conditional statements, but has a different form:

Select CaseEXPRESSION_CHECKED

caseVALUES_1

OPERATORS_1

caseVALUES_ 2

OPERATORS_ 2

. . .

caseVALUES_N

OPERATORS_N

[ caseElse

OTHERWISE_ OPERATORS]

End Select

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

A type VALUES must match the type EXPRESSION_CHECKED.

First computed EXPRESSION_CHECKED. 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 ELSE_OPERATORS and control is transferred to the operator standing after End Select

For instance. 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 statements

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

ForCOUNTER=INIT_VALUEToEND_VALUEstepSTEP

OPERATOR_BLOCK

OPERATOR_BLOCK

NextCOUNTER

For…Next loop iterates through the values ​​of a variable COUNTER, which is a loop parameter, from start to end value with the specified change step. This ensures that the block of statements of the loop body is executed with each new value of the counter. If stepSTEP is absent in the construction, then by default it is considered that the step is equal to 1. According to the operator Exit For you can exit the loop statement before COUNTER reaches the last value.*

To iterate over objects from a group of similar objects, such as 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 statements are used to organize loops with an unknown number of repetitions in advance:

loops with precondition - DoWhile … loop,

DoUntil … loop;

loops with postcondition - Do … loopWhile,

Do … loopUntil.

Following is the syntax of 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 ensures that a block of statements is repeated many times until CONDITION is observed, and the operator

Other news

A VBA program is a sequence of statements.

There are a number of conventions to be followed in programming. So, you can place several statements on one line. A colon is placed between statements on the same line.

Any line can be split into two by placing the characters "Space" + "Underscore" (_) at the end of the first, in which case the second line will be considered a continuation of the first.

Comments are used to make the program easy to read. There are two ways to enter comments in VBA: using an apostrophe (‘), which can be placed anywhere in a line, and using the reserved word Rem instead of an apostrophe.

1. Dim Operator is for declaring variable types.

Dim A As Integer - variable A is declared as an integer, i.e. it will store only integer values .

Dim D As Date - variable D is declared to store dates.

· Dim Surname, Name As String – variables are declared. Surname and Name, intended for storing text.

Dim B(12) As Integer - a one-dimensional array (vector) is declared, consisting of 12 integers, and by default the first element of the array will be B(0), and the last B(12).

Dim B(3,3) As Single – a two-dimensional 3x3 array (matrix) is declared, consisting of real numbers.

If the variable type is not specified, then the Variant type is used by default. However, specifying a specific variable type makes the program more reliable and speeds up its work, because VBA doesn't have to spend time recognizing an undeclared variable each time it is accessed.

If the size of the array M is not known in advance and is determined during the program, then when describing the array, the number of elements is not indicated, and the array is defined as follows:

Dim M() As Integer

After determining the number of array elements, for example, N, you need to write the operator

2. assignment operator is used to assign a value to a variable.

Syntax:

Variable (or object property) = expression.

a=5 - variable A to assign the value 5 ;

b="Manager" - variable b assign meaning "Manager";

Address=Sheets("Organizations").Cells(2,2) – assign the contents of cell B2, which is located on the Organization sheet in the current workbook, to the Address variable;

LastName=UserForm1.TextBox1.Text - variable Last name to assign the contents of the TextBox1 field of the user form UserForm1.

3. With/End with statement saves the programmer from a large number of repetitions of the name of the same object.

Syntax:

With object

operator1

operator2

operatorN

For example, instead of a sequence of statements

UserForm1.TextBox1.Text = Date

UserForm1.TextBox2.Text = " "

UserForm1.ComboBox1.Text = " "

can be written like this

TextBox1.Text = Date

. TextBox2.Text = " "

. ComboBox1.Text = " "

REM On sheet Sheet1 in column A, starting from the second line, the ‘rates of employees’ are recorded. Populate the ComboBox1 combo box in the UserForm1

‘The first line of the program is on sheet Sheet1 in column A ‘the number of filled cells is counted, the result is ‘assigned to the variable N

N=Application.CountA(Sheets("Sheet1").Range("A:A")).

D=”A2:A”&Cint(N)

Sheets("Sheet1").Range(D).Name="Rates"

TextBox1.Text = Date

. TextBox2.Text = " "

. ComboBox1.Text = " "

. ComboBox1.Rowsource = "Rates"

4. Conditional If/Then/Else Statement- allows you to check a certain condition and, depending on the results of the check, perform one or another action

Syntax:

If condition Then operators1 [ Else operators2]

If the condition is true, then statements1 are executed, otherwise statements2 are executed.

It is also allowed to use a complex conditional operator, which is written as a block:

If condition1 Then

operators1

ElseIf condition2 Then

VBA Operators: Arithmetic, Boolean, Comparisons, Assignments

Operator is the smallest unit of VBA code that can run. A statement can declare or define a variable, set a VBA compiler option, or perform some action in a program.

There are only 7 arithmetic operators in VBA. Four standard ones: addition (+), subtraction (-), multiplication (*), division (/) and three more:

• exponentiation (^), for example 2^3 = 8 ;
• integer division (\). Divides the first number by the second, discarding (not rounding) the fractional part. For instance, 5\2 = 2 ;
• modulo division (Mod). Divides the first number by the second, returning only the remainder of the division. For instance, 5 Mod 2 = 1.

The assignment operator in VBA is the equals sign. It can be written like this:

Let nVar = 10

or even simpler:

nvar = 10

In the second case, don't confuse the equals sign with the equals operator.

Expression

nvar = 10

means "set the variable nVar to 10", and if the line looks like this:

If (nVar = 10)

it means "if the value of the variable nVar is equal to 10".

If you need to assign an object to a variable, then this is done in other ways.

There are only 8 comparison operators in VBA:

• equality (=), for example, If (nVar = 10);
• greater than and less than (> and<), например, If (nVar > 10);
• greater than or equal to and less than or equal to (>= and<=), например, If (nVar >= 10);
• not equal (<>), For example, If(nVar<>10) ;
• comparison of objects (Is). Determines whether object variables refer to the same object or to different ones, for example, If(obj1 is obj2);
• similarity (Like). Compares a string object to a pattern and determines if the pattern matches.

Comparison operators always return true or false - true if the assertion is true and false if false.

A little about comparing string values:

• when comparing string values, case is sensitive;
• spaces in string values ​​are also taken into account;
• when comparing text strings by more/less, by default, just binary character codes are compared - which is more or less. If you need to use the order that goes in the alphabet, you can use the command

Option Compare Text

A little more about the Like operator. Its general syntax looks like

Expression1 Like Expression2

In this case, Expression1 is any VBA text expression, and Expression2 is a template that is passed to the Like operator. You can use special wildcards in this pattern (see Table 3.1)

Tab. 3.1 Wildcards for the LIKE operator

Very often, when testing several conditions, logical operators are used:

• AND - logical AND, both conditions must be true;
• OR - logical OR, at least one of the conditions must be true;
• NOT - logical negation, returns TRUE if the condition is false;
• XOR is a logical exception. In an E1 XOR expression, E2 returns TRUE if only E1 = TRUE or only E2 = TRUE otherwise FALSE;
• EQV - equivalence of two expressions, returns TRUE if they have the same value;
• IMP - implication, returns FALSE if E1 = TRUE and E2 = FALSE, otherwise TRUE.

You need to remember about AND, OR, NOT, other logical operators are rarely used.

Almost every VBA program uses concatenation operators. There are two of them in VBA - + or &. It is recommended to always use & because:

• when using &, the automatic conversion of numeric values ​​​​to strings is performed - there is no danger of making a mistake;
• when using the + operator, adding a string value to a value of type Null yields Null.

MsgBox "Message to user" & vUserName

The order in which operators are applied can be controlled using parentheses.

Related Tips

Setting conversion options

How to Convert FLAC Files to MP3, AAC, WMA, WAV, etc.

wps extension. How to open a .WPS file? Other Causes of Problems Opening WPS Files

How to Convert VOB File to AVI

How to change image format Utility converter from png to bmp

Convert DOC to EPUB