Arguments:

Message - a required argument that specifies the information message to be displayed in the window. Can consist of several text lines joined by a sign & . Usage in this argument Chr(13) leads to a new line 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 any 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.

Heading - sets the window title.

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

Table 3.1. Valid Button Variable Values

For example. Display a message about the current date.

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

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

After clicking OK, 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 enters variable values ​​using the input window and has the following syntax:

Variable_Name = 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 window title.

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

To do this, you can use the following operator:

N = InputBox("Enter N", "Input input",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 acceptable to the user, then after clicking the OK button, the input window will close, the variable N will be set to 10, and program execution will resume from the statement immediately behind the call InputBox.

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

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 executed if CONDITION true, otherwise executed OPERATOR_2. In this case, the If...Then...Else statement is written on one line.

CONDITION is a boolean expression. The result of an expression is always Boolean. An expression can be simple or complex. When writing simple conditions, all possible relational operations listed in table can be used. 3.2.

Table3 .2. Logical relations

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

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

IfCONDITIONThen

BLOCK_OPERATORS_1

BLOCK_OPERATORS_2

End If

A conditional statement can test multiple conditions. In this case, the conditional operator looks like:

IfCONDITION_1Then

BLOCK_OPERATORS_1

ElseIfCONDITION_2Then

BLOCK_OPERATORS_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 operator

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

Select CaseEXPRESSION UNDER TEST

CaseVALUES_1

OPERATORS_1

CaseVALUES_ 2

OPERATORS_ 2

. . .

CaseVALUES_N

OPERATORS_N

[ CaseElse

OTHERWISE_ OPERATORS]

End Select

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

Type VALUES must match the type TEST_EXPRESSION.

First it is calculated EXPRESSION UNDER TEST. If its value matches one of the values VALUES_I, then they will be fulfilled OPERATORS_I End Select. If its value does not match any of the values VALUES_I, then they will be fulfilled ELSE_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 statements

To implement a cyclic computing process, i.e., repeated 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 loops through the values ​​of a variable COUNTER, which is a loop parameter, from the initial to the final value with the specified change step. This ensures that the block of loop body statements is executed with each new counter value. If StepSTEP is absent in the design, then by default it is assumed 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 through 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 an unknown number of repetitions:

loops with precondition - DoWhile … Loop,

DoUntil … Loop;

loops with postcondition - Do … LoopWhile,

Do … LoopUntil.

Below 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 multiple times until CONDITION is observed, and the operator

Other news

Language instructions (or operators)– these are program units that perform some actions or describe data.

The statement contains one or more keywords, and possibly parameters. Several statements located on the same program line are separated from each other by a colon. If the statement is too long, you can break it into multiple lines by using the underscore _ character to break it.

ABOUT P erator assignments

The assignment operator is used to assign a new value to a variable during program execution. The assignment sign "=".

For example, operator:

x = Sqr(5 + Tan(1.8)^2)

assigns variable x the value of expression . As a result of calculating the expression written on the right side, we obtain a real number. But the value that will be assigned to variable x depends on how the type of that variable was declared. So, if the variable x is of type Single, it will be assigned the result 4.834464, if it is Double, then 4.83446368725481, and if it is Integer, then 5.

The data type of the variable x must be compatible with the data type of this expression. In addition to implicit conversion from one type to another during assignment, Visual Basic provides the ability to convert types using functions. For example, the function CDbl converts data to Double type, CInt– to the Integer type, Clng– to the Long type, CSng– to the Single type, CStr– to type String, etc.

The CInt and Clng functions round the result. Moreover, if the fractional part of a number is 0.5, rounding is done to the nearest even number.

Assignment operator allows you not only to assign a value to a variable, but also to set the values ​​of properties of VBA objects. For example, the operator

Rows("1:1").Font. Bold = True

Makes the font on the first line of the worksheet bold.

LINE BREAK

Combination<Пробел> + <Знак подчеркивания>at the end of a line ensures that the next line is a continuation of the previous one. At the same time, we must remember that:

§ You cannot break string constants by wrapping

§ No more than seven continuations of the same line are allowed

§ The line itself cannot contain more than 1024 characters

Example.

Incorrect transfer Correct transfer

Line = "Visual Basic for _ Line = "Visual Basic" _

Applications" & "for Applications"

COMMENTS

The text following the symbol (") in a program up to the end of the line is ignored by the compiler and is a comment. Comments are used to add explanations and descriptions to the program. Comments are useful when debugging a program: they allow you to temporarily disable program lines while debugging.

Conditional operator

A conditional statement executes certain instructions (statements) depending on the value of the condition expression. The block diagram for checking the condition looks like this:

Rice. 1. Branching may be incomplete (this structure is also called bypass).

Rice. 2. The conditional statement has two forms of syntax: line and block.

Lowercase form

If condition Then [ operators_if_true]

The algorithm presented in Fig. 1, in lowercase form will be written as

If condition Then operators_if_true Else operators_if_false

Figure 2 corresponds to the entry

If condition Then operators_if_true

Figure 3 corresponds to the entry

If condition Then Else operators_if_false

Block form

If condition-1 Then

[operators_if condition–1_true]

[operators_if_all_conditions_false]]

The conditions listed in the If and ElseIf parts are relational expressions or logical expressions. When one of the conditions is satisfied, the statements following its corresponding Then keyword are executed, and the remaining statements are ignored (that is, no further tests are performed and control is transferred to the statement following the End If). If none of the logical conditions are true, then operators_if_all_previous_conditions_false, or, if this part is omitted, control is transferred to the program line following the conditional statement.

The block form of the If statement is preferable if:

When a condition is met or not met, several statements are executed (the line form in this case can also be used, but the line will be too long and the program less clear);

Several conditions are checked sequentially, and when the next condition is met, it is inappropriate to check subsequent conditions (this is why the ElseIf keyword is used).

Example

The company provides discounts to wholesale buyers.

Based on the known volume of the order, it is necessary to determine its cost.

To calculate the order cost we use the function:

Public Function Order_Cost(Quantity As Long) As Double

If Quantity<= 999 Then

Order_cost = Quantity * 5

ElseIf Quantity<= 1999 Then

Order_cost = Quantity * 4.8

Order_cost = Quantity * 4.75

In this case, it was possible to use the lowercase form of the IF operator:

Public Function Order_Cost1(Quantity As Long) As Double

If Quantity<= 999 Then Стоимость_заказа1 = Количество * 5

If Quantity >= 1000 And Quantity<= 1999 Then Стоимость_­ заказа1 = Количество * 4.8

If Quantity >= 2000 Then Order_Cost1 = Quantity * 4.75

If we do not consider the disadvantages that the lines are long, and for any order volume all checks are performed sequentially (the first procedure works faster for small order volumes), then the program is written correctly and, perhaps, even more clear.

However, it is possible to give examples where, if one of the conditions is met (or not met), the others simply cannot be checked.

For example, some operators must be executed when the conditions are met jointly: http://po-teme.com.ua/images/adIIIin/image014_e652e49476c6f0eb9cf40b68cc090828.gif" alt="" width="56" height="27 src=">. If for checks use operator

If x>0 and y

then if x is negative, an error will occur when calling the sqr function (the root is a negative number).

This error can be avoided by using the design

If x > 0 Then If y< Sqr(x) Then

In the last form of the entry, if there is an Else keyword, then it refers to the last If statement.

Selection operator

Select Case expression

[instructions_else]]

The expression parameter is any numeric or string expression. Instead of evaluating a logical condition, the value of the expression is compared with each of the values ​​specified by the parameter expression_list-n.

Values ​​for comparison included in expression_list-n, can be specified in the form:

– values;

– range of values ​​in the form initial_value To final_value;

– comparison expressions in the form Is comparison_operator value;

– a list of any of the listed types of expressions (separator – comma).

An instruction can contain an arbitrary number of Case blocks. If none of the conditions are true, then the statements in the Case Else block are executed.

For example, if the condition is a score above three, then this condition can be written: Case 4, 5 or Case Is >3 or Case Is >= 4 or Case 4 To 5.

Note. Keyword Is need not be entered; it will be added automatically.

Example

The above example with a discounted price using the Select Case construction can be solved as follows:

Public Function Order_Cost2(Quantity As Long) As Double

Select Case Quantity

Order_cost2 = Quantity * 5

Case 1000 To 1999

Order_cost2 = Quantity * 4.8

Case Is >= 2000

Order_cost2 = Quantity * 4.75

A VBA program is a sequence of statements.

There are a number of conventions that should be followed when writing programs. Thus, several statements can be placed 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 on a line, and using the reserved word Rem instead of an apostrophe.

1. Dim operator is intended for declaring variable types.

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

2. Dim D As Date – variable D is declared to store dates.

3. Dim Last Name, Name As String – variables are declared. Last name and Name, intended for storing text.

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

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

If a variable type is not specified, the default type is Variant. However, specifying a specific type of variable makes the program more reliable and speeds up its operation, because VBA doesn't have to waste time recognizing an undeclared variable every 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 intended to assign a value to a variable.

Syntax:

Variable (or object property) = expression.

1. a=5 – assign variable A the value 5 ;

2. b=“Manager” – variable b assign meaning "Manager";

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

Last Name=UserForm1.TextBox1.Text – variable Last name is assigned to the contents of the TextBox1 field of the user form UserForm1.

o 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, ‘employee tariffs’ are written down. Fill in the combo box ComboBox1 in the user form UserForm1

‘The first line of the program – on the 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=”Tariffs”

TextBox1.Text = Date

. TextBox2.Text = “ “

. ComboBox1.Text = “ “

. ComboBox1.Rowsource = “Tariffs”

1. Conditional statement If/Then/Else– 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 possible to use a complex conditional operator, which is written as a block:

If condition1 Then

operators1

ElseIf condition2 Then

operators2

operators3

Last Name=.TextBox1.Text

If Last Name =”“ Then MsgBox”You have not entered a last name”

REM When checkbox number 1 is checked, the client is given a 5% discount

‘ Amount – amount of money paid by the client

‘ The cost of the product is stored in the Cost variable

If UserForm1.CheckBox1.Value = True Then

Amount=Cost-Cost*0.05

Else Amount=Cost

1) REM Assume that Tariff is the established tariff, and Time is the time worked. Time and tariff are entered into the input fields ‘in TextBox1 and TextBox2, respectively. Salary is calculated using the formula Tariff*Time. Let's write a program to display the calculated salary in the form. Label4 – inscription prepared for ‘displaying the salary value’

If IsNumeric(TextBox1.Text)=True And _

IsNumeric(TextBox2.Text)=True Then

Tariff=TextBox1.Text

Time=TextBox2.Text

Label4.Caption=Tariff*Time

In the first example, we work with user form number one. Variable Surname the contents of input field number one are assigned. It then checks to see if anything has been entered into the input field (whether the variable is empty Surname). If the variable Surname is empty, a message box is displayed on the screen.

In the second example, the first lines starting with the word REM and apostrophes are comments explaining the purpose of the variables. The conditional operator determines the amount paid by the client.

The third example checks whether numbers are entered in the input fields. If numbers are entered into two fields, then the variables are assigned the values ​​of the input fields and the label is assigned the value of the result of multiplying the tariff by time.

1) Unconditional jump operator GoTo is intended for specifying a transition to a specified line within a program.

Syntax:

GoTo String

Required argument String can be any line label or line number.

If IsNumeric(TextBox1.Text)=False Then GoTo Error

Error: MsgBox “Error entering numbers!”

The example above checks to see if a number has been entered in input field one. If the entered number is not a number, then the line with the Error label is moved and a message is displayed on the screen.

2) Loop operator For/To/Next designed for programming repeating fragments, i.e. to describe cyclic algorithms.

Syntax:

For variable=M1 To M2 [ Step M3]

operators

M1, M2, M3 – expressions. A loop statement repeats the execution of a group of statements while the variable (counter) changes from the initial value M1 to the final value M2 with the specified step M3. If the step is not specified, then it is assumed to be 1.

Last Name = Sheets(“Employees”).Cells(I,1)

UserForm1.ComboBox1.AddItem Last Name

In the example given, it is assumed that on the “Employees” sheet, the first column contains the names of employees who need to be included in the list of combo box number one of user form number one. Program operation algorithm:

1) Variable I is assigned the value 2.

2) The Last Name variable is assigned the value of cell A(I,1), located on the “Employees” sheet. (When the loop is executed for the first time, this is cell A(2,1))

3) An element from the Last Name variable is added to the list of the ComboBox1 combo box.

4) 1 is added to the value of the variable I (if the step is not specified, then it is assumed to be equal to 1). A check is performed to see if the value of I has exceeded 10 (the final value of the variable I, in the example M3=10)? If the value of I is even less than or equal to 10, then steps 2-4 are performed, otherwise the cycle ends.

Thus, the above program allows you to fill the list of ComboBox1 with data.

Basic algorithms used in solving
economic tasks

Let's look at the basic algorithms using the following example. Employees from different departments participated in the execution of work under the contract. The table on sheet “Sheet1” presents data on the number of hours worked by each employee (Fig. 8).

Fig. 8. Data on the number of hours worked

company employees

The world of free programs and useful tips
2024 whatsappss.ru