Any procedure consists of three parts: input, execution and output. Enter - this is the information necessary to perform the procedure; conclusion - this is what happened as a result of the procedure.
Object properties, cell values, and variable values are all examples of input. Input data can be obtained during procedure execution from the user. It is called user input.
Most simple form user input is a click on a button in a message box. A little more complicated is entering a single value into the input field of the dialog box.
4.1.1 Message window
Using the message box of the MsgBox() function, you can ask a question and then, depending on the answer, perform one or another action.
The MsgBox() function has five arguments:
MsgBox(Prompt:=[, Buttons:=] [, Title:=] [, HelpFile:=, Context:=])
The Prompt argument specifies the message that appears in the dialog box. Type text (in quotes). Use type variable string or united string variables and strings with & sign, for example:
“This screen is "&AppIication.UsableHeight&" points high"
The Buttons argument determines which buttons will appear in the message box. Specify them using constants such as vbExclamation or vbOK. VBA represents these constants as numbers. Although instead of constants Can use numbers to designate buttons, but we recommend using constants, since you will easily get readable code. Button argument parameters can be divided into several groups. The two most important are pictograms And buttons. You can place four types of icons and six types of buttons in the message box. Other settings determine which button is considered pressed by default and which application is paused when the dialog box appears—Excel only or all applications. Table 4.1 shows these parameters.
Table 4.1 – Values of the Button argument constant of the MsgBox function
| Constant | Meaning | Description |
| VbOKonly | Displays a button OK | |
| VbOKCancel | Displays buttons OK And Cancel | |
| VbAbortRetryIgnore | Displays buttons Stop, Repeat And Ignore | |
| VbYesNoCancel | Displays buttons Not really And Cancel | |
| VbYesNo | Displays buttons Yes And No | |
| VbRetryCancel | Displays buttons Repeat And Cancel | |
| VbCritical | Displays the Critical Message icon | |
| VbQuestion | Displays a Question mark icon | |
| VbExclamation | Displays the Exclamation icon | |
| VbInformation | Displays the Information icon | |
| VbDefaultButton1 | Takes the first button by default | |
| VbDefaultButton2 | Accepts the second button by default | |
| VbDefaultButton3 | Accepts the third button by default | |
| VbApplicationModal | Excel stops working when closing message box | |
| VbSystemModal | Pauses all applications until the message window is closed |
To display multiple buttons in a message box, connect the constants with a plus sign. You cannot display two icons in a message window, but Can control both the icon and the type of buttons that appear. The message box always appears only in the center of the screen. If no other button is specified, only the OK button is displayed in the message box.
The Title argument specifies the title of the message box. Like Prompt, this argument must be a string, a string variable, or a union of strings and string variables. You can omit this argument, in which case the title of the message box will default to Microsoft Excel.
To specify the current help, use the fourth and fifth arguments (or ignore them). The HelpFile argument is the name of the help file, and the Context argument specifies the topic within it. If you specified one of these arguments, you must also provide the second. This will cause Excel to automatically add a help button to the message box.
The following code displays two message boxes: the first with the title - "System_inform" - contains an icon with an exclamation mark and a message about the screen height being used; the second with the title - "System information" - contains a message about the used screen width.
Sub ShowMessageOK()
MsgBox Prompt:="The height of this screen is"_
& Application.UsableHeight & " dots", Buttons:=vbExclamation, Title:="System_inform"
MsgBox "The width of this screen " & Application.UsableWidth & _
" points", vbInformation, "System information"
4.2 Decision making
It's very easy to display multiple buttons: use the constants shown in Table 3.1. You can display message boxes with Yes and No buttons, Yes, No, and Cancel buttons, and Stop, Retry, and Ignore buttons. By analyzing the value returned by the MsgBox function in the code (Table 4.2), you can determine which button is pressed and direct program execution along the desired path.
Table 4.2 – Values returned by the MsgBox function
| Constant | Meaning | Pressed button |
| VbOK | OK | |
| VbCancel | Cancel | |
| VbAbort | Abort | |
| VbRetry | Repeat | |
| VbIgnore | Skip | |
| VbYes | Yes | |
| VbNo | No |
The following code example displays a message box with Yes and No buttons. If the user clicks the Yes button, another message box will appear that says “Continuing...”. If the user clicks No, a "Process has been interrupted" message appears. The second argument uses vbQuestion plus vbYesNo: the message box will contain a question mark and Yes and No buttons.
Sub QuestionsYesNo()
Dim Indik As Integer
Indik = MsgBox("Do you want to continue?", vbQuestion + vbYesNo, "Question to the user")
If Indik = vbYes Then
MsgBox "Continue...", vbInformation, "System message"
MsgBox "Process interrupted", vbCritical, "System message"
This code example shows two ways to use the MsgBox function. In the first case, parentheses are placed immediately after the word MsgBox and after the third argument. This means that the return value of the MsgBox function is used. The next two functions do not use a return value, so the parentheses are not included. The buttons located in the message window determine the value of the return value. The return value of the MsgBox function is a constant that begins with vb and ends with the word written on the button, such as OK or Cancel. If the message box contains Yes and No buttons, the MsgBox function returns vbYes or vbNo.
In this code, the Indik variable is equal to the result of the MsgBox function. The If command checks whether the value of Indik is equal to vbYes; if so, it takes appropriate action.
4.3 Input window
The InputBox() function is a little more complex than MsgBox() and allows you to enter a real value or text string. Since InputBox() receives a value, it is pointless to use it without using the input value. Therefore, this function is always written with parentheses.
The InputBox() function can take up to seven arguments:
InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])
Only the first one is necessary - Prompt, which specifies a message in the window. The second is Title.
The third is the default value of the input window. The fourth and fifth arguments indicate the position of the input window. If they are skipped, the window will be located in the middle of the screen.
The sixth and fifth arguments specify the help file and its topic. If you use one of them, you must use the other as well. Excel will then add a Help button to the input window.
The InputBox() function always returns a string. Even if the user enters a number, it is actually a string. In most cases, VBA converts a string to a number. However, sometimes the translation is carried out incorrectly. In this case, you need to convert the return value to another data type. To do this, use one of the translation functions, such as the Cint(value) function, which converts the return value to an integer.
When you click Cancel, you expect the return value to be vbCancel. However, this is not true. Since InputBox() returns a string, clicking the Cancel button will cause it to return empty line(set of quotes - ""). Consider the following example, if nothing is entered (empty line), then the subroutine exits.
Sub Vvod_lnputBox()
Dim s As String, sreal As Single
s = InputBox(Prompt:="What salary?:", _
Title:="Question", Default: =550)
If s = "" Then Exit Sub
sreal = CSng(s)
MsgBox "Salary is" &s & "taxes" &sreal * 0.13
Sometimes it is necessary for the user to enter only a specific type of data. In this case, use the InputBox() method instead of the InputBox() function. By installing Application. before InputBox(), you tell VBA to use Excel's InputBox() method rather than the VBA function. The InputBox() method also has an eighth argument - Type. By specifying a type, you are requiring (the user) to enter only a specific type of data. To allow multiple data types, add values various types data. However, if you use the InputBox() method, clicking the Cancel button returns value False, not the empty string.
4.4 I/O using Excel cells
The ActiveCell object can be effectively used to enter source data from specific cells in an Excel worksheet, as well as to output calculation results to cells. The Range object is used to specify cells. An ActiveCell object points to the cell (or Range object) that has focus when keyboard input occurs. The Value property is used for this. For example, in the cell with address A5 you need to write the value of the Rost variable:
Range(“A5”).Select
ActiveCell.Value = Growth
The reverse procedure is similar:
Rost = ActiveCell.Value
If there is a need to read a large number of data from different cells, the addresses of which can be calculated, it is advisable to use the Offset(R,C) method, where R is a shift down by R rows relative to the row of the active cell, C is a shift by C columns to the right relative to the column of the active cell . For example, the following line writes the value of the variable Rost to cell B7.
Output (transfer to the user) of the results of the program and other messages is carried out through the message dialog box.
To organize the output of information, the built-in function is used MsgBox.
Let's consider options for its use.
1. y = Sqr(16)
MsgBox y
2
. S=99
MsgBox("S= ” & S)
it's a sign concatenation – ampersand;
used to combine multiple lines into one
3
. a = 5: b = 101
MsgBox("a="& a & " " & " b = " &b)
4.MsgBox("a=" & a & Chr(13) & "b=" & b)
using this function you enter transition symbol
on a new line(converts a number to a character)
Working with Excel Objects
Excel object model represents a hierarchy of objects subordinate to one object Application, which corresponds to the at Excel application. Objects Excel are workbooks, worksheets, charts, cell ranges, cells, etc.
VBA can programmatically manipulate any of these objects.
Every an object has a set properties, methods And events.
Property is a characteristic or parameter of an object.
Method is an action that an object can perform.
Event is an action to which an object reacts automatically.
Manage an object inVBAyou can do it in 3 ways:
changing object properties;
by activating a method associated with an object;
defining a procedure that will be launched in response to an event.
Outputting information to an Excel cell
Cell as Excel object in a VBA program it is designated Cells(i, j), Where i– line number, j– spreadsheet column number.
Y
Meaning Y will be displayed on the active sheet of the worksheet Excel workbooks to cell B3
Cells(3, 2) = Y
WITH
Variable B will be assigned the value which is stored in the cell C5 (i.e. the contents of the cell C5)
B = Cells(5, 3)
vba tool for entering information
Input of information from the user is carried out through an input dialog box. Implemented using a built-in function InputBox.
Dim x As Single
x 
= Val(InputBox("Enter x"))
Function InputBox returns the sequence of characters entered in the input field ( 15 ) as data type String(string). Built-in function Val() Converts a string value from an input field to a numeric value.
Linear Computational Processes
Linear algorithm is characterized by a strict sequence of implementation of blocks in the order of their arrangement in the circuit - from top to bottom. There are no conditional blocks. Each block is executed once.
Example 1 Calculate function value
at k = 33.5 x = 17
1. Let's create an algorithm for solving the problem.
2. In the code editor window, enter the program code:
Option Explicit
Sub Linear_process()
Dim k As Single, x As Single, y As Single‘declaration of variables
x = Val(InputBox("Enter the value of x"))
y = k * Exp(Sin(x))
MsgBox "y="&y‘ output the result to a dialog box
End Sub
Team Option Explicit obliges to declare all variables in a given program. Can only be placed in the module announcements section.
Lecture for ZF
DATA INPUT AND OUTPUT OPERATORS.
LINEAR COMPUTING PROCESSES
ASSIGNMENT OPERATORLET
The assignment operator is used to evaluate the value of an expression and assign that value to a variable. When writing a program, the Let statement can be omitted.
Operator format:
Variable_name = Expression
SUMMA=X+COS(X)^2
It is important to distinguish between the assignment operator and algebraic equality. The operator Y = A + B means for the computer: add the contents of the memory cells allocated for storing the values of variables A and B, and place the result in the memory cell allocated for the value of the variable Y. In programming, the construction of an assignment operator like I = I + 1 is widely used . TO set value 1 is added to variable I, and the result is placed in the same cell, replacing the information that was there with new information. From a mathematical point of view, the equality i= i+1 doesn't make sense.
When assigning string values to variables, they must be enclosed in quotes:
T = "Parameter 1",
and values of the Date/Time type should be enclosed in # symbols (“hash”):
D = #11/29/2008#
DATA ENTRY OPERATORS
Let's look at three main methods of data entry.
1. Writing Variable Values directly in the program text carried out using assignment operator.
Variable_name = Expression
This method is used if the source data do not change with multiple executions of the program.
2. Data input from worksheet cellsExcel.
To do this, use the instructions WITHells(i, j) , which in this case acts as a data input function. Usage format:
Variable_name= Cells(i, j) ,
Where i, j– serial numbers of the row and column, respectively (numerical values!), at the intersection of which the cell is located, i.e., the cell address.
It should be borne in mind that the data on the Excel sheet already available.
Example: A = Cells(1, 2)
After executing this command, variable A will be assigned the value that is stored in the cell located in the first row (first digit) and in the second column (second digit), that is, in cell B1 of the spreadsheet.
3. Enter data directly during program execution, i.e. in dialog mode, is performed using the information input dialog box implemented by the function InputBox. The basic format of this function is:
Variable_name =InputBox(“Message” [, “Header”] [, “Value”]).
While the program is running, when this command is executed, a dialog box appears on the monitor screen with the specified Heading containing text Messages , as well as an input field with the specified Meaning:
Program execution pauses while waiting for keyboard input and button presses. After entering the information and clicking the OK button, the variable is assigned a value of type String(string data type) containing text entered into the input field.
Example: x = InputBox(“Enter x”, “Input input”, “0.15”)
The window name can be omitted (the location of the commas is preserved):
x = InputBox("Enter x", "0.15")
The InputBox function is used when the original data change every time the program is started, so usually the value of the variable is not set and the simplest way to write this function is used:
Variable_name= InputBox("Message")
Example: d = InputBox(“Enter diameter value”)
If you enter 23 in the input field in the dialog box that appears, then the text “23” will be written to the variable d, not the number 23. The text string “23” is only a visual display of the number, but is not a number in the literal sense of the word, i.e. That is, with it, as with any other text, you cannot perform any arithmetic operations.
To convert a string data type to a numeric type, use the function Val(Line) , which returns the number contained in Line , as a numeric value of the appropriate type.
When written in program code
d = Val(InputBox(“Enter diameter value”))
and entering 23 in the digit input field, the variable d will be assigned the number 23.
DATA OUTPUT OPERATORS
Let's look at three main ways to output data.
1. Conclusion to the dialog box using the command MsgBox:
MsgBox "Message", "Title"
IN 
As a result of executing this command, a dialog box appears on the screen Heading
containing the specified text Messages
. Program execution is suspended until the user clicks OK.
Example: MsgBox y , "Result"
In this case, as output to the window Result message set to the current value of the variable y:
The simplest format for writing the MsgBox operator:
MsgBox"Message"
Typically, the message includes not only the output value, but also a comment, which is enclosed in quotation marks. To merge several fragments into one line in the output statement, they are separated by the sign & .
Example: MsgBox “Diameter value =” & d
As a result, the following dialog box will appear on the screen:
2. Data output per sheet workbook Excel using instructions WITHells(i, j) . In this case, unlike previously discussed, it acts as an output command:
Cells(i, j) = "Message".
The result of this command is Message placed in a cell with an address determined by the line number i and column number j.
Cells(1, 1) = “x=" ‘Output to cellA1 textx=
Cells(1, 2) = x ‘Output to cellB1 current variable valuex
3. Data output to the debug window Immediate("Immediately"). This window is usually located below the code window. If this window does not exist, then it can be displayed by pressing Ctrl+G or from the VBA main menu View → Immediate Window.
To output to the debugging window, use the Print method of the Debug object (debugger). Recording format:
Debug.Print ["Message"]
Example: Debug.Print “Diameter value =” &d
As you can see from this example, the Debug.Print command is used similarly to the MsgBox command.
Empty (i.e. without Messages) the Debug.Print method prints an empty line.
In addition to the & sign, the Print method can use delimiters for the list of output data. At the same time, the sign “ ; " means outputting the next value immediately after the previous one, the sign " , » – transition to the beginning of a new print zone (the debugging window is divided into 5 vertical zones of 14 characters each). When entering the sign " ; " between the elements of the output list can be omitted, VBA will add it automatically.
A comma or semicolon at the end of the output list in a Debug.Print command suppresses a newline (the next Debug.Print will start printing on the same line).
Example: Debug.Print "Result y="; y ;
In any output statement(MsgBox, Cells, Debug.Print) it is possible to display not only ready results, but also simultaneous calculation and output:
Debug.Print "s = "; s, " k1+k2 = "; k1 + k2
MsgBox "If diameter = "&d&", then radius = "&d/2
To display a numeric value in a specified format in the MsgBox, Debug.Print output operators, use the function instead of the variable name Format indicating the number of decimal places.
Debug.Print z ‘variable output z in the usual way
Debug.Print Format(z, "#0. 00 ") ‘variable output z with 2 decimal places
MsgBox Format(z, "#0. 000 ") ‘variable output z with 3 decimal places
It is not recommended to use the Format function in the Cells output statement.
LINEAR COMPUTING PROCESSES
The simplest are programs that implement algorithms for a linear computational process. The program in this case contains data input, assignment and data output statements. Operators are written sequentially one after another in their natural order and are executed only once.
General structure of a linear program:
OptionExplicit
Sub Name_ procedures()
‘Declaring constants (Const … )
‘Declaring Variables (Dim … )
‘Procedure body:
‘Entering initial data
‘Computations
‘Output of results
EndSub
Example_L1. Compose GSA and program text to calculate the function:
where x = a∙t 2 + 0.2, a = 18, t is arbitrary.
GSA Program text:
OptionExplicit‘Prohibition of the use of undeclared variables
Sub Lin_process1() ‘Start procedure Lin_process1
Const a = 18 ‘Declaration of a constant a
Dim t As t
Dim x As Single ‘Declaration of a real variable x
Dim y As Single ‘Declaration of a real variable y
t = Val(InputBox("Enter t")) ‘Input value t
x = a * t ^2 + 0.2 ‘Calculation x
y = (x^2 + Log(x) - (x + 1)^2) / (x * Sin(x)) ‘Calculation y
MsgBox "Result y=" & y ‘Output y to the dialog box
EndSub‘End of procedure
Below is a solution to the same example using other data input and output methods.
1. InputBox function
The InputBox function has the following syntax:
Variable = InputBox(Prompt[, Title] [, default] [, Xpos] [, Ypos] [, help_file, contents])
This function requires only the argument Invitation Argument value Invitation– the only required argument to this function is a text string that is displayed in the input dialog box as a message. This text should be enclosed in double quotes.
Argument Heading
Argument default specifies the value that is displayed by default in the input field until the user enters a value. If this argument is omitted, the input field appears empty.
Optional Arguments Xpos And Ypos set the position of the input window on the screen. Arguments help_file And content
The return value of the InputBox function is the value entered by the user in the input box.
2. MsgBox function
A message box is created by the MsgBox function, which has the following syntax:
Variable = MsgBox(Prompt[, Buttons][, Header[, help_file, contents])
Argument value Invitation– the only required argument to this function is a text string that is displayed as a message in the dialog box. This text must be enclosed in double quotes. Note the use of parentheses in the MsgBox syntax - they indicate that in this case MsgBox is a function that returns a value. If the parentheses are omitted, then for VBA this is a sign that this expression does not return a value. If you want to return a value, you must use code similar to the following:
Dim i As Integer
The result of running this code in VBA will be a dialog box with three buttons “Yes”, “No”, “Cancel” and an icon Exclamation point in a yellow triangle.
If no argument is given Buttons, then VBA offers only one "OK" button. Argument Buttons allows you to manage the following message window parameters:
¨ The number of buttons in the window.
¨ Types of buttons and their placement in the window.
¨ Icons displayed in the window.
¨ Which button is assigned by default.
¨ Mode (modality) of the message window.
Table 1 shows possible settings for this argument. The first group of values sets the number and type of buttons. The second allows you to select the icon displayed in the window. The third assigns the default button. The fourth group sets the message window mode. To create the final argument value Buttons You can use only one value from each group, combining them with a plus sign.
| Group | Constant | Meaning | Description |
| Group 1 | vbOKOnly | Displays the OK button only (default setting) | |
| VbOKCancel | Displays OK and Cancel buttons | ||
| VbAbortRetryIgnore | Displays the Stop, Repeat and Skip buttons. | ||
| VbYesNoCancel | Displays Yes, No, and Cancel buttons | ||
| vbYesNo | Displays Yes and No buttons | ||
| VbRetryCancel | Displays the Redo and Cancel buttons | ||
| Group 2 | VbCritical | Displays a prohibition icon | |
| VbQuestion | |||
| VbExclamation | Displays a warning icon | ||
| VbInformation | Displays an information icon | ||
| Group 3 | VbDefaultButton1 | The first button is the default button | |
| VbDefaultButton2 | The second button is the default button | ||
| VbDefaultButton3 | The third button is the default button | ||
| VbDefaultButton4 | The fourth button is the default button | ||
| Group 4 | VbApplicationModal | Application mode: The user must close the message box before continuing with the current application | |
| VbSystemModal | System mode: All applications are unavailable until the user closes the message window | ||
| Additional group | vbMsgBoxHelpButton | Displays the Help button | |
| vbMsgBoxSetForeground | Makes the message window the foreground window | ||
| vbMsgBoxRight | Displays a message box aligned to the right edge of the application window | ||
| vbMsgBoxRtlReading | For Hebrew and Arabic, specifies that text should be displayed from right to left. |
Table 1. Settings for the argument Buttons MsgBox functions
To avoid mistakes when entering argument values Buttons, use the list of constants that appears after you enter the "+" sign. The "+" sign is used to combine multiple constants when specifying a complex argument Buttons.
Argument Heading used to specify the text that is placed in the title bar of the input window. If this argument is not specified, Microsoft Excel is displayed in the title bar.
Arguments help_file And content are used if you create your own help system for your application.
Table 2 provides a list of values returned by the MsgBox function . The return value depends on the button the user pressed.
| Return value | Button |
| OK | |
| Cancel | |
| Stop | |
| Repeat | |
| Skip | |
| Yes | |
| No |
Table 2. Values returned by the MsgBox function
Best type The return variable of the MsgBox function is Integer.
Range and Cells objects
IN VBA cells worksheet are treated as a Range object. This is the most commonly used object.
The Range object uses the A1 format when working with cells.
A1 format. The link consists of a column name (designated by letters A to IV, 256 columns maximum) and a row number (from 1 to 65536). For example, A77. To refer to a range of cells, specify the addresses of the upper left and lower right cells of the range, separated by a colon. For example, B10:B20, 7:7 (all cells in the 7th row), 5:10 (all cells between the 5th and 10th rows inclusive), D:D (all cells in column D), H: J (all cells between columns H and J inclusive). An absolute reference is indicated by a dollar sign before the row or column name
The Cells object uses the R1C1 format when working with cells.
R1C1 format. In the R1C1 format, after the letter “R” the row number of the cell is indicated, after the letter “C” the column number. For example, the absolute reference R1C1 is equivalent to the absolute reference $A$1 for format A1. To set a relative reference, specify the offset relative to the active cell. Mixing is indicated in square brackets. The sign indicates the direction of displacement. For example, R[-3]C (a relative reference to a cell three rows higher in the same column). RC (relative reference to a cell located two rows below and two columns to the right). R2С2 (absolute reference to the cell located in the second row and in the second column). R[-1] (relative reference to the row above the current cell), R (absolute reference to the current row).
The full cell address may also contain the worker's name and book address. The sheet name is followed by a “!” sign, and the workbook address is enclosed in square brackets. For example: [Book1.xls]Sheet5!D$2.
The following can act as a Range object:
§ separate cell;
§ selected range of cells;
§ several selected ranges of cells (i.e. a collection of non-adjacent ranges);
§ row and column;
§ three-dimensional range (i.e. consisting of ranges located on different worksheets).
Range and Cells Object Properties
| Properties | Description and allowed values |
| Value | Returns a value from a cell or range (to a cell or range): X=Range(“A1”).Value Range(“A1”).Value=10 |
| Name | Returns the name of the range: Range(“B1:B4”).Name=”Application” |
| Address | Returns the current position of the range |
| Count | Returns the number of cells in a range |
| Offset | Returns the offset value of one range relative to another |
| Resize | Allows you to change the current range selection |
| CurrentRegion | Returns the current range that contains the specified cell and is delimited by an empty row and column. |
| WrapText | True (False) – allows (does not allow) text wrapping when entered into the range. |
| EntireColumn, EntireRow | Returns the row and column. |
| ColumnWidth, RowHeight | Returns the column width and row height of a range. |
| Font | Returns a Font object. For example: With Worksheets(“Z3”).Range(“F10”).Font .Size=22 .Bold=True .Italic=True End With |
| Formula | Formula in A1 format. For example, this is how you can enter a formula in cell C2: Range(“C2”).Formula=”=$B$2+$A$2” |
| FormulaLocal | Formula in A1 format, taking into account the user’s language (for non-English speakers Excel versions). For example: Range(“C1”).FormulaR1C1= “=PI ()” |
| FormulaR1C1 | Formula in R1C1 format. For example, Range(“C1”).FormulaR1C1= “=R1C1+2” |
| FormulaR1C1Local | Formula in R1C1 format taking into account the user's language (for non-English versions of Excel). |
| Horizontal Alignment | Horizontal alignment. Possible values: xlHAlignGeneral (normal), xlHAlignCenter (center), xlHAlignCenterAcrossSelection (center of selection), xlHAlignJustify (width), xlHAlignRight (right), xlHAlignLeft (left) and others. |
| Vertical Alignment | Vertical alignment. Possible values: xlVAlignBottom (at the bottom edge), xlVAlignCenter (at the center), xlVAlignTop (at the top edge) and others. |
Range and Cells object methods
| Methods | Actions |
| Address | Returns the cell address. |
| AutoFit | Automatically adjusts column width and row height. For example: Range(“B1:B3”).Columns.AutoFit Using the Columns or Rows property in this case is necessary, since the range value must be rows or columns, otherwise an error will be thrown. |
| Clear | Clears the range. For example: Range(“B1:B20”).Clear |
| Copy | Copies a range to another range or clipboard (if Destination is not specified). For example, this is how you can copy range values from one sheet (L1) to another (L2): Worksheets(“З1”).Range(“D1:D5”).Copy Destination:=Worksheets(“P2”).Range(“D5”) ") |
| Cut | Copies the range with deletion (cuts) to another range or clipboard (if the Destination parameter is not specified). For example, let’s copy a range of cells with deletion to the clipboard: Worksheets(“Sheet1”).Range(“D1:E5”).Cut |
| Delete | Deletes a range. The Shift parameter determines the direction in which cells are shifted when deleted. For example: Range(“B6:D6”).Delete Shift:=xlShiftToLeft |
| Insert | Inserts a cell or range of cells. For example, this is how you can insert a row before the sixth row in the “Sheet2” sheet: Worksheets(“Sheet2”).Rows(6).Insert |
| Select | Selects a range: Range(“A1:C7”).Select |
Range and Cells object methods that implement Excel commands
| Methods | Actions |
| DataSeries | Creates a progression. DataSeries(rowcol,date,step,stop,trend) The method is performed manually using the Edit\Fill\Progression command |
| AutoFill | Autocomplete. Automatically fills the cells of a range with elements of the sequence: Object(Range, Type). |
| AutoFilter | Autofilter. Implements a request to filter data on a worksheet: Object.AutoFilter(Field, Condition1, Operator, Condition2) Corresponds to the Data\Filter\AutoFilter command. |
| AdvancedFilter | Advanced filter. Corresponds to the Data\Filter\Advanced Filter command. |
| Consolidate | Combining data from multiple ranges into one final table. Corresponds to the Data\Consolidation command. |
| Find | Data Search. Manually called with the Edit\Find command. |
| TblGoalSeek | Selection of parameter. This is done manually using the Tools\Parameter Selection command. |
| Sort | Sorting data. Manually performed using the Data\Sort command |
| Subtotal | Adds subtotals. Manually called by the command Data\Subtotals. |
Note . It should be especially noted that in VBA (unlike Excel) the assignment operation is performed regardless of the cell status and does not change it. Those. To assign a value to a cell (or to obtain its value), it is not at all necessary that this cell be active (remember that in Excel, before entering it into a cell, it must be activated); it will not become active (if it was not so before) even after the assignment it has no meaning.
Method Cells makes the specified cell active. The method syntax is as follows:
Cells[(N rows, N columns)]
N lines - row number of the current Excel sheet ,
Column N – column number of the current Excel sheet (when calling this method, the columns are numbered).
IN this option syntax assumes that on the active sheet Excel cell, located at the intersection N lines And Column N, becomes active.
Using the method Cells You can make a cell active so you can then enter or output data.
If this method is used data input from a worksheet cell to a variable, then Cells stands on the right side of the assignment operator, and if carried out conclusion from a variable to a worksheet cell, then Cells stands on the left side of it.
Input structure, using the method Cells next:
<
Name>=Cells (
< Name> - simple variable or array element.
Output structure using the method Cells next:
Cells(
< expression> - any output value.
Consider the example of the following program:
Sub input_output_Cells()
Cells(5, 1) = "c="
Before running this macro, the working Excel sheet looked like (see Fig. 13):
And after running the macro it looks like this (see Fig. 14):
VBA control structures. Conditional operator. Unconditional jump operator
Control structures allow you to control the sequence of program execution. Without control statements, all program statements will be executed from left to right and top to bottom. Let's look at one of the VBA control structures - conditional operator .
Conditional operator is used when it is necessary to execute one or a group of operators depending on the value of some condition.
Conditional statement syntax:
Short form Þ If<условие>Then<оператор>
If< условие >Then
< оператор > /< Группа операторов 1 >
Full form Þ If< условие >Then
< оператор 1 > / < Группа операторов 1 >
< оператор 2> < Группа операторов 2 >
In the block diagram, the conditional operator is depicted as follows:
Typically the condition is a simple comparison, but it can be any expression with a calculated value. This value is interpreted as False if it is null, and any non-null value is treated as True. If the condition is true, then all expressions after keyword Then. If the condition is false, then all expressions after the keyword are executed Else.
Multiple nested structures are allowed, having as many levels of nesting as necessary.
Consider another control structure - unconditional jump operator. Its syntax is:
GoTo label,
Where label – this is any combination of characters.
This statement forces a change in the execution sequence of the program. The GoTo operator transfers control to the operator with the label, i.e. Following the GoTo statement, the statement specified by the label will be executed.
VBA control structures. Loop operators.
Sometimes you need to repeatedly execute a set of instructions automatically, or solve a problem differently depending on the value of variables or parameters specified by the user at run time. The conditional operator and loop operators are used for this purpose.
Let's look at VBA loop statements. A cycle with a known number of repetitions(loop with parameter) implemented in VBA using the operator For Next following structure:
For<параметр цикла>= <начальное значение>To<конечное значение>
<операторы VBA>
Next<параметр цикла>
<параметр цикла>– name (identifier) of the cycle parameter;
<начальное значение> – initial value of the cycle parameter;
<конечное значение>– final value of the cycle parameter;
<шаг>– step of changing the cycle parameter (optional parameter, if it is missing, the change step is 1);
<операторы VBA>
In the block diagram this operator is depicted as follows:
In the body of this loop you can use the operator Exit For, with which you can end the loop For Next before the parameter takes its final value.
Loops with an unknown number of repetitions are implemented in VBA using the operators Do While... Loop, Do Until... Loop, Do... Loop While, Do... Loop Until.
Let's look at the structure of the Do While... Loop statement.
Do While<условие>
<операторы VBA>
Here <условие> – logical expression;
<операторы VBA> - VBA statements that implement the loop body.
<условие>, If<условие>takes the value True, then the statements are executed until function word Loop. Then the condition is checked again, and this continues until the condition becomes false.
Let's look at the structure of the Do Until... Loop operator.
Do Until<условие>
<операторы VBA>
The operator is executed as follows. Checked<условие>, If<условие>takes the value False, then the operators up to the service word Loop are executed. Then the condition is checked again, and this continues until the condition becomes true (True).
In the block diagram this operator is depicted as follows:
We looked at loop operators that implement a loop with a precondition.
Let's look at loop operators that implement a loop with a postcondition. These are the Do... Loop While and Do... Loop Until operators. The structure of these operators is as follows:
<операторы VBA>
Loop Until<условие>
<условие>, If<условие>takes the value False, then the operators up to the service word Loop are executed again. This continues until<условие>will not become true.
In the block diagram this operator is depicted as follows:
<операторы VBA>
Loop While<условие>
The operator is executed as follows. The statements up to the Loop service word are executed. Then it is checked<условие>, If<условие>takes the value True, then the operators up to the service word Loop are executed again. This continues until<условие>will not become false.
In the block diagram this operator is depicted as follows:
There is an operator in VBA that allows you to exit loops with an unknown number of repetitions early. To exit these loops you need to use the operator Exit Do.
Loading...
