Built-in Visual Basic functions. Built-in Functions in VBA String Processing Functions

Return value

Absolute value of a number

arctg(x) – arctangent of the parameter value specified in radians

sin(x) – returns the sine of the angle from the parameter value specified in radians

cos(x) – cosine of the angle specified in radians

tg(x) – returns the tangent of the angle from the parameter value specified in radians

e x – returns the number e raised to the specified power, where e is the base of the natural logarithm

ln(x) – returns the natural logarithm of the value of a numeric expression

- returns the square root of a numeric expression

Random number from the interval)

Named number formats

Procedures and functions are separate blocks that make up the program code; each procedure performs a task or part of it.

Event routines are constantly waiting for events after being called.

In addition to event processing procedures, the program can include procedures and functions not related to events. They perform separate actions and can be used repeatedly. Let's call them general. General-purpose procedures are called for execution in program code. Using procedures saves time and avoids unnecessary mistakes. Functions differ from procedures in that they return a value.

A procedure or function is a sequence of operations that must be performed repeatedly in different places in the application. In this case, the required block of commands is written in the code only once, after which it can be accessed from any part of the program.

Function is a subroutine that is called to perform some kind of calculation or check. When it completes its work, it returns control to the calling program and passes the calculation result to it.

Procedure- this is also a subroutine. It is also called to perform some action, but it is not required to return any values ​​to the main program.

Procedure and function declaration syntax:

Sub<Имя процедуры>(<Параметры>) <Операторы>End Sub Function<Имя функции> <Операторы>End Function

Procedures declared with the Public keyword can be called in any application module (each form is a separate module).

Procedures declared as Private can only be called in the current module.

The word Static means that all variables declared in the procedure will be static, i.e. their values ​​are preserved between calls.

Parameters provide the connection between a procedure and an application. This is the data passed to the procedure when called.

Event handling procedures. Called when an event occurs. In this case, both the name of the element and the type of event that happened to it are significant.

Custom procedures. Groups of operators created by the developer to perform specific tasks and not depending on the current state of the application or events that occurred at one time or another.

Built-in functions. Certain sets of commands available in the Visual Basic language and intended for calculating certain values ​​based on source data. In particular, both mathematical and string functions are built-in (Abs, Cos, Sin, Mid, Len, etc.)

Custom functions. Groups of statements similar to user procedures.

However, there are a number of differences between them.

The main differences between a function and a procedure are as follows.

1. A function has a type (similar to a variable) and can return a value to the program, which is assigned to the function using the operator:

<Имя функции>= value

2. A function is called, as a rule, by specifying its name and parameters on the right side of any operator. On the other hand, the procedure is called using a separate statement:

Call<Имя процедуры>(Options)

<Имя процедуры>(Options)

If the Call keyword is used when calling a procedure, then the list of parameters must be indicated in parentheses. If the procedure is called without using Call, then its parameters are listed without parentheses.

It should be noted that the called procedure may not have parameters. In this case (if the service word Call was used), empty parentheses should be placed after the procedure name.

User procedures are usually used when it is necessary to perform the same sequence of operations. For example, a program requires you to repeatedly enter the values ​​of an array arrA, consisting of five elements, in a loop. In this case, filling the array is best done as a procedure.

The Add Procedure command of the Tools menu allows you to add a procedure or function.

Let the Cir procedure draw an ellipse with coordinates x, y, which are passed to the procedure as parameters. When creating a Cir procedure with the Add Procedure command, you need to specify the procedure name and select the scope Public or Private.

Having completed the dialogue, we get a declaration of the procedure:

Private Sub Cir() : End Sub

Now you need to enter the parameters in brackets and write the text of the procedure. It is recommended to indicate the type of variables in the list of parameters.

Private Sub Cir(x As Integer, y As Integer) Circle (x,y),500,2 End Sub

In this part, you will learn about the data types that can be manipulated by VBA, how temporary data is stored in VBA, how to combine variables and constants to create new values, and learn how to include built-in functions in expressions.

Laboratory work No. 3. Overview of VB data types. Variables and

constants. Data type compatibility. Assignment operator. Arithmetic and logical operators. Built-in functions

Purpose of the lesson: Know data types. Be able to create variables. Be able to set data types to variables. Be able to create named constants. Know the scope

variables and constants. Know type conversion. Know how to use the assignment operator. Know arithmetic and logical operations. Know how to use string concatenation. Be able to use VBA built-in functions. Materials for the lesson: MS Excel 2003.

Data Types OverviewVisualBasic (VB)

Before learning about variables, you should understand how VBA stores different types of information. VBA, like most other programming systems, divides the data it processes into numbers, dates, text, and other types. Data type(data type) is a term that refers to certain types of data that VBA stores and can manipulate. In table Figure 9 summarizes VBA data types, shows how much memory each type takes up, briefly describes the data types, and gives the range of values ​​that a given type can store.

Table 9– VBA data types

In table 9 you came across a data representation called exponential notation(scientific notation), which is used to display very large and very small numbers in a compact format on external devices (monitor, printer, etc.). In exponential notation, values ​​are written without leading or trailing zeros and have only one digit to the left of the decimal place. The number is multiplied by 10 to some degree to show where the decimal place actually is.

VBA has six different numeric data types: Byte, Integer, Long, Single, Double And Currency. Numeric data types are used to store (and manipulate) numbers in various formats, depending on the specific type.

Typically a VBA program (like any other program) "makes" decisions by checking whether various conditions are true. To make it easier to test conditions and ensure that the results of such testing are stored, VBA provides a Boolean data type. Boolean values True And False called Boolean(Boolean) values. Their name is associated with the name of the mathematician who developed the system of mathematical logic. The VBA Boolean data type is also called the Boolean. The VBA Boolean type requires two bytes of memory and can have one of two values: True or False. If you display type Boolean on the screen, VBA automatically converts it to a string containing either the word True, or False. Boolean values ​​are obtained as

the result of the comparison operation.

VBA uses type Date for storing dates and times. No need to worry about how VBA stores type data Date, – you can simply display, save or manipulate dates; VBA automatically handles all the details of converting a sequential number to year, month, day, and time. VBA type Date is a type consecutive dates(serial Dates). Consecutive dates store the date as a number of days from a given start date. Base date for VBA type Date is December 30, 1899. VBA uses negative numbers to represent dates before 12/30/1899 and positive numbers to represent dates after 12/30/1899. The number 0 represents the date itself, 12/30/1899. According to this scheme, January 1, 1900 is written as the number 2 (January 1, 1990 is 2 days after December 30, 1899), but the number –2 is the date December 28, 1899 (two days before December 30, 1899). To verify this, write a simple procedure.

Exercise 1. Write a procedure that prints a date message.

For this:

Write a procedure (Listing 3):

Listing 3 – ProcedureDateTest

1 Sub DateTest()

2 Dim d As Date 3

As a result of this procedure, the base date will be displayed on the screen (Fig. 15).

Rice. 15 In a sequential date value, the integer part (the numbers to the left of the decimal place) is the total number of days from the base date. The VBA sequential date can have digits to the right of the decimal place; these numbers indicate the time of day as part of the day. One hour is 1/24 of a day (approximately 0.0416. Likewise, one minute is 1/1440 of a day, and a second is 1/86,400 of a day. You can subtract one date from another, add to a date, or subtract numbers to change it values. For example, if you need to determine the number of days between two dates, simply subtract the earlier date from the later one. Because these are values ​​of type. Date, VBA "knows" that the purpose of the calculation is to obtain the difference in days between these two dates. Likewise, if you need to determine a date 60 days after a given date, simply add 60 to that date.

VBA has several built-in procedures (described in this section) to separately extract the year, month, day, hour, minute, and second from a variable like Date.

Any text data saved in a VBA program is called lines(strings). Strings in VBA are saved using data type String. Strings get their name because text data is usually treated as strings of characters. The string can contain any type of text characters: alphabetic letters, numbers, punctuation marks, or various symbols. There are two categories of strings: variable-length strings, which grow or shrink in size, and fixed-length strings, whose size always remains the same. All strings in VBA are variable length strings unless you specify a fixed length. Most user input (in dialog boxes, worksheet cells) is string data. Additionally, since you can only display text on the screen, all other data types must be converted to string data before you can display them on the screen. Many VBA built-in procedures (like Msgbox) use string data in all or some of their arguments. VBA provides several operators for concatenation(concatenate), that is, to join together and compare strings. VBA also has several built-in routines to help you extract substrings from longer strings, find characters or words in a string, change the case of letters in a string, and so on. This part describes VBA string operators and procedures for manipulating VBA strings.

Data type Variant is a special data type that can store any of the types listed in table. 9, except type Object. VBA uses type Variant for all variables, unless you explicitly declare the variable's type, as described later in this section. Data type Variant take on characteristics of a certain type that they currently hold. For example, if data type Variant contain string data Variant accepts characteristics of type String. If data type Variant contain numerical data, Variant takes characteristics of some numerical type, usually - Double, although types Variant may also have type characteristics Integer, Long, Single or Currency. Although the types Variant Convenient and take some of the work out of writing procedures, they require more memory than any other data type except large strings. In addition, mathematical operations and comparison operations on data type Variant are slower than similar operations on any other type of data. In general, you should avoid using variables Variant: if you will rely on type variables Variant, you may develop a habit of sloppy programming and find it difficult to find and fix errors in programs.

Variables

Variable(variable) is the name the programmer gives to the area

computer memory used to store some type of data. VBA variables can store any of the data types listed in the table. 9.

Identifier(identifier) is the name you give to elements in procedures and modules you create, such as variables. The term identifier is based on the fact that the names you create identify specific locations of memory (in the case of a variable name), groups of statements (in the case of a macro or procedure name), or other program elements.

Variable names not "sensitive" to register state(not case- sensitive), that is, whether the variable name is written in uppercase or capital letters does not matter.

The easiest way to create a variable is to use it in a VBA statement. VBA creates a variable and reserves memory for the variable's memory location the first time that variable appears in a statement (usually a statement that stores a data value in a variable).

Storing a data value in a variable is called assignment to a variable(assigning the variable or making an assignment). Assignment is performed using the assignment operator, represented by the equal sign (=). The following line is an example of assigning a value to a variable:

This statement stores the numerical value 25 in the memory location specified by the variable name MyVar.

Creating a variable by using it in a statement is called implicit variable declaration(implicit variable declaration). By using a variable in a statement, you are implicitly telling (declaring) VBA that you want to create that variable. All variables that VBA creates by implicit variable declaration have a data type Variant. Implicit variable declaration is also known as variable declaration "on the fly"(on- the- fly).

Implicit variable declarations are convenient, but have potential problems. For example, when you have a variable named MyVar and you will make a mistake in the name later when typing Mvar. Depending on where the incorrect variable name appears in your code, VBA may throw a runtime error or simply create a new variable. If VBA creates a new variable, you may have problems that are very difficult to detect.

For these and other reasons, VBA gives you the ability to do explicit(explicit) declaration of variables.

To explicitly declare variables, use the VBA statement Dim with the following syntax:

Dim name1 [, name2 ]

nameN is any valid variable identifier. All the variables you create with this form of the keyword Dim, are variables

type Variant.

A variable can only be declared once in a separate procedure or module. Because the Dim statement comes before any statements that actually use the variable, you can place it anywhere in the procedure. In programming practice, it is a good rule of thumb to collect all explicit variable declarations in one place at the beginning of a procedure.

Task 2. Modify the procedure HelloExcel from the previous part, using an explicit variable definition.

For this:

b change the procedure HelloExcel (Listing 4):

Listing 4 – ProcedureHelloExcel

1 Sub HelloExcel()

2 Dim HelloMsg " variable for MsgBox 3

Operator Dim (on line 2) of Listing 4 declares a variable HelloMsg and reserves a memory area for it (in this subroutine HelloMsg is a variable of type Variant). Line 2 includes a final comment indicating the purpose of this variable. Line 4 assigns to a variable HelloMsg lines "Hello,Excel!" . Next (on line 5) the HelloMsg variable is used as one of the arguments to the procedure MsgBox. Function MsgBox displays the same message box as before. Although MsgBox now receives its first argument from a variable, this variable contains the same string information that was previously (in the listings of the previous part) written directly into the operator MsgBox.

All variables in VBA, whether declared implicitly or explicitly, are Variants unless you specify the variable's type in the statement that declares it. For announcement typed variable and its type using the operator Dim add the As keyword after the variable and then enter the name of the data type for that variable. Here is the general syntax for using the operator Dim when declaring typed variables:

Dim varnamel [, varname2 ]

varnameN represents any valid VBA variable name, a typeN – any of the VBA data type names.

Once a typed variable is declared, regardless of whether the variable is declared explicitly or implicitly and how the type is specified, that variable retains the same type for as long as it exists. You cannot re-declar a variable or redefine its type.

With an implicit declaration, you can also specify the type of the variable by adding

special character called type definition symbol(type- definition character), to the end of the variable name. In table Figure 10 lists the VBA type definition symbols and the types they represent. Table 10– Type definition symbols

Type

Definition symbol

Type

Symbol definitions

IntegerLong

CurrencyDouble

Type definition characters can only appear at the end of a variable name. Although you should know what type definition symbols are and how they are used, you rarely need to use them—using the Dim statement with the As keyword is much easier and clearer. Most VBA programmers do not use type definition symbols.

Task 3. Modify the procedure HelloExcel from task 3, using the definition of a typed variable.

For this:

b change the procedure HelloExcel (Listing 5):

Listing 5 – ProcedureHelloExcel

1 Sub HelloExcel()

2 Dim HelloMsg As String 3

HelloMsg = "Hello Excel!"

Title$ = "My first program"

MsgBox HelloMsg, Title$

This version of the procedure HelloExcel works much the same as the previous ones. Line 1 contains the procedure declaration. On line 2 the statement Dim explicitly declares a variable HelloMsg. Since the operator Dim includes keyword As and type name String, variable HelloMsg has type String. Line 4 assigns the message text to a string variable HelloMsg. Line 5 implicitly declares a variable Title$ and at the same time assigns the message box title text to the variable. Because the variable name Title$ ends with a type definition character for a string, this variable is also of type String. Finally, line 6 uses the operator MsgBox to display a message box; in this statement, both the message text and the window's title bar are variables: HelloMsg And Title$, respectively.

After adding a type definition symbol to a variable, you must include a type definition symbol every time you use the variable name.

Regardless of whether variables of type are declared String using the operator Dim or by adding the type definition character $, the string variables you create are variable-length strings by default.

Variable-length string variables change length depending on the length of the string being stored by the variable. Sometimes you may need to use the line fixed length(fixed- length). Fixed length strings are always the same length. The following line shows the general syntax for creating a fixed-length string:

Dim varname As String * N

varname is any valid variable name, a N – is any number from 1 to 65400 characters

Scope: Variable Availability

Term scope(scope) refers to the scope of a VBA procedure or module where a given variable, procedure, or other identifier is accessible. This part describes two basic levels of scope: procedural and modular. Variables, procedures, and identifiers that are accessible only within a procedure have procedural-level scope, and those that are accessible to all procedures in a module have module-level scope.

A variable declared in a procedure is accessible only by that procedure. For example, variable HelloMsg from line 2 of Listing 5, is only accessible in the procedure HelloExcel; no other procedure has access to this variable.

Therefore they say that the variable HelloMsg It has procedural level scope(procedure- level scope). In fact, the variable HelloMsg only really exists while VBA is actually executing the procedure HelloExcel.

Task 4. Create two procedures whose scope is procedural-level variables.

For this:

b enter two completed procedures (Listing 6):

Listing 6 – ProcedureHelloExcel

1 Sub HelloExcel()

2 Dim HelloMsg As String 3

HelloMsg = "Hello Excel!"

MsgBox HelloMsg, "My first program"

8 Sub HelloDave()

9 Dim HelloMsg As String 10

HelloMsg = "Good afternoon, Excel!"

MsgBox HelloMsg, "Another message box"

Lines 1–6 contain the same procedure HelloExcel from Listing 4, which works exactly the same. A procedure has been added to the module HelloDave, which begins on line 8 of Listing 6. Procedure HelloDave works the same as the procedure HelloExcel, it just displays a different text message and title in its dialog box. On lines 2 and 9, both procedures use the operator Dim to declare locally variables named HelloMsg.

Sometimes it is necessary for several procedures to have access to the same variable. It is usually more efficient to calculate a value once, store it in a variable, and then use that variable in multiple procedures than to calculate the same value over and over again.

VBA allows you to declare variables that are accessible to multiple procedures. When a variable is available to all procedures in a module, that variable is said to have scope modular level(module level). VBA limits the scope of a module-level variable to the module in which the variable is declared (VBA provides ways to extend the scope of a variable even further; these methods are described next).

To make a variable available to all procedures in a specific module, place the statement Dim for it at the beginning of the module before any procedure declarations.

Exercise 1

Create two procedures and a single module-level variable declaration (move the statement to line 1 Dim with variable HelloMsg removing lines 2 and 9 from Listing 6).

The variable name must be unique within its scope. However, it is possible to have variables with the same name on different scope levels. When variables have the same name but different scopes, VBA uses the variable with the most local(local) scope.

Requiring explicit declaration of variables

Although implicit variable declaration (declaring variables simply by using them) is convenient, it has some problems. When variables are declared implicitly, there is a risk of inadvertently creating a new variable when you should actually be using an existing one, or using an existing variable when the user intends to create a new one. Both of these situations lead to errors in the code that are very difficult to track down.

To make it easier to spot errors related to implicit variable declarations at any time, VBA provides the command OptionExplicit. Using OptionExplicit VBA requires all variables to be declared (using the operator Dim) before using them in the module.

To set the mode where VBA requires explicit declaration for all variables in a module, add the command OptionExplicit to the module declaration area, that is, to the beginning of the module before any variable or procedure declarations. Commands like OptionExplicit, are called compiler directives(compiler directives).

Team OptionExplicit only affects the module in which it appears. If the project containing this module also contains other modules, they are not affected by the command OptionExplicit. You must include the Option Explicit command in every module that requires explicit variable declarations.

Since inclusion OptionExplicit in all modules is very useful, VB Editor provides a way to automatically include this command in every new module when you create it. In order for VB Editor to add the command OptionExplicit For each new module, follow these steps:

ь select a command Tools/ Options (Service/Options); VB editor displays dialog box Options;

ь click on the tab Editor (Editor) to display editing options, if necessary;

ь select the checkbox Require Variable Declaration (Explicit description of variables);

ь select OK. VB editor closes dialog box Options.

Exercise 2

Set the command to auto-enable Option Explicit into each new module when it is created.

Constants

Constant(constant) is a value in a VBA program that does not change. The examples of procedures already given above use string constants like "Hello,Excel!" And "My first program". Constants like them are called literal constants(literal constants), because the literal value is written directly to the code.

You can also write literal numeric constants and dates in VBA code; examples of numeric literal constants include numbers 25 , 3.14 . Examples of literal date constants include dates #12/31/96# or #October 28, 1997#(You'll learn more about writing date constants later in this part).

VBA allows you to create named constants(named constants). A named constant, like a variable, has a name given to it; this name represents a specific immutable value. However, unlike a variable, the value of a named constant never changes. The following line shows the general syntax for declaring named constants:

Const name = valuel [operator name2… ] _

[, patheZ =value3 [operator name4 ] … ]

nameN represents any valid identifier, valueN – any data value: numeric, string or date, a operator – an arithmetic or comparison operation between two names of previously described constants. The following lines show several named constant declarations: Const Pi = 3.14, text = "Hello Excel!"

Const Pi2 = 2*Pi

Scope of constants

As with variables, you can declare named constants in procedures or in the declaration area at the beginning of a module. A constant declared in a procedure has procedural-level scope, while a constant declared in a module's declaration scope has modular-level scope. Named constants follow the same scope rules as variables.

Writing Literal Constants

When writing literal string constants in VBA code, follow these rules:

ь string constants must be enclosed in double quotes (");

ь is an empty string constant (called zero line– null string or empty string) is denoted by two double quotes with nothing between them ("");

b The string constant must all be on the same line.

When writing literal numeric constants in VBA code, follow these rules:

ь numeric constants must consist only of numeric characters from 0 to 9;

b a numerical constant may begin with a (–) sign and may contain a decimal point;

b You can use exponential notation for numerical constants.

VBA recognizes date constants in any of several different formats; You must place all date constants between pound signs (#). The following lines show some of the date constant formats that VBA recognizes: #2-5-97 21:17:34# #February 5, 1997 9:17:34pm# #Mag-31-97# #15 April 1997#

Regardless of which of the following formats a literal constant of type Date is written in, VBA reformats the constant (when the insertion point is removed from the line after writing the constant) to conform to one of the following two formats, depending on whether the Date constant contains information about time: #2/5/1997 9:17:34 PM# #2/5/1997# There are only two correct Boolean constants: True and False.

Specifying a constant type

When you declare a named constant or use a literal constant, VBA "believes" that the value represented by that constant has

the data type that most closely matches the expression assigned to the constant.

In VBA, you can set the type of a constant. The general syntax for declaring a typed constant is:

Const name As type = value[, name As type = value]

name is any valid constant name, type – the name of any VBA data type and value – the value you assign to the constant.

The following line illustrates the correct declaration of a constant with a specific type:

Const Pi As Double = 3.14

Domesticconstants

VBA provides several internal constants(intrinsic constants), also called predefined constants(predefined constants). An internal constant is a named constant that was defined by the VBA developers. Internal constants defined by VBA all start with letters vb to indicate that they are defined by the Visual Basic for Applications (or Visual Basic) language. For example, constants vbOKOnly, vbOKCancel are defined by VBA. Excel 2002 internal constants begin with letters xl to make it clear that they are defined by Excel. Thanks to internal constants, it is easier to use some of VBA's built-in procedures, such as the statement MsgBox, which you already know about, and the operator InputBox, which are discussed later in this part.

Display message windows. Receiving data from the user

Receiving data from the user, storing it in a variable, and displaying the results of actions performed on it are the basic elements needed to write interactive procedures. Interactive(interactive) procedure is a procedure that exchanges information with the user, that is, the procedure interacts with the user by displaying messages and receiving input.

Function MsgBox displaying a message box has the following syntax:

MsgBox(Prompt [, Buttons ] [, Title ] [, HelpFile , Context ])

argument Prompt MsgBox displays this line in a dialog box; must always provide an argument Prompt, since this is required argument(required argument). Argument Buttons (optional argument), is a numeric expression that determines the buttons and messages displayed in the dialog box. Argument Title represents any string value (literal, constant, or variable). MsgBoxTitle, VBA displays in title bar of dialog box MsgBox word " MicrosoftExcel" . Argument HelpFile – help file, Context – section in the help file. The message text can be enclosed in parentheses, but parentheses are optional when the function MsgBox

used as an operator.

Data entered by the user is called input data(input). To receive input from the user of a procedure, use the function InputBox. Function(function) is a special type of VBA procedure that returns a value. Function InputBox displays a dialog box containing text that prompts the user to enter some value, and a text box for entering that value. Dialog box displayed InputBox, also contains command buttons OK And Cancel.

Function InputBox has the following syntax:

stringvar=InputBox( Prompt[, Title] [, Default] [, XPos] [, YPos] _

[, HelpFile, Context])

Here stringvar represents any variable that can store a string (or a variable of type String, or – Variant). Argument Prompt represents any string value (literal, constant, or variable). InputBox displays this string as a prompt in a dialog box; must always provide an argument Prompt, since this is required argument; all others are optional. Argument Title is the second argument for InputBox. Title represents any string value (literal, constant, or variable). InputBox displays the text of this string in the title bar of the dialog box. If you omit the argument Title, VBA displays in the title bar of the dialog box InputBox word " MicrosoftExcel" . Argument Default – a string expression displayed in the input field as the default if the user does not enter another string; if this argument is omitted, the input field is rendered empty. Arguments XPos And YPos can be any numerical expressions. These arguments allow you to specify where in the active window the input window appears, and are the coordinates of the top-left corner of the dialog box: XPos – horizontal distance from the left edge of the window; YPos – this is the vertical distance from the top edge of the window. Both distances are measured in twips ( twips); One twip is equal to 1/20 of a point (a point is the measurement of a print font). Since a dot is 1/72 of an inch, one twip is approximately 0.0007 inch. Last two optional arguments to the function InputBox- This HelpFile And Context. They have the same purpose as similar function arguments MsgBox.

Using named function arguments

As you may have noticed, it's easy to accidentally omit commas or rearrange argument values ​​in functions that have optional arguments or multiple arguments, despite the help of the property Auto Quick (Brief information) VB editor. Omitting or rearranging arguments in a function's argument list can result in type mismatch errors. The error (even worse) may not be detected. To prevent programming errors and make it easier to use functions that have optional arguments, VBA provides an alternative to listing the values ​​in the argument list in a specific order. You can also transfer

function argument values ​​using named arguments(named arguments) functions. The following lines show two statements MsgBox, which have the same result; The first statement uses the regular argument enumeration method, and the second uses the named argument method:

MsgBox AnyMsg, AnyTitle

MsgBox Prompt:=AnyMsg, Title:=AnyTitle

The symbol that assigns a value to a named argument (:=) is not exactly the same as the regular assignment operator (=). If you omit the colon (:) when assigning a value to a named argument, VBA will not necessarily detect a syntax error, but may not interpret the statement correctly. When VBA executes this statement, it displays one of several possible runtime errors, often a type mismatch error.

You cannot mix named arguments with a regular argument list in the same function call. You must use either named arguments or a list of regular arguments for each individual function call.

Task 5. Write a procedure that calculates the area of ​​a circle, but gets the radius of the circle from the user of the procedure.

For this:

Enter the procedure (Listing 7):

Listing 7 – Receiving input data using an operatorInputBox

Const Pi As Single =3.14 "approximation of pi value

Dim CircleArea As Single " saves the calculated area of ​​the circle 3

4 Sub List3_07()

Const BoxTitle = "Area of ​​the circle"!}

Dim Radius As Single, Temp As String 7

Temp = InputBox("Enter the radius of " & Chr(13) & "circle", BoxTitle)

Radius = CSng(Temp)

CircleArea = Pi * Radius * Radius

MsgBox CircleArea, vbInformation + vbOKCancel, BoxTitle

Lines 1 and 2 of Listing 7 declare a constant Pi and a variable CircleArea modular level. Line 4 contains the actual procedure declaration. Line 5 declares a procedural level constant BoxTitle; this constant has only local access in the procedure List3_07. On line 8 the statement calls the function InputBox. It displays its first argument as text in a dialog box, prompting the user to enter values ​​of some type. In this statement InputBox displays the text "Enter Circle Radius" (function Chr(13) - newline character) to tell users of the procedure what value they should enter. InputBox uses the second argument,

represented by a constant BoxTitle, as the title of the dialog box. When executing the statement InputBox a dialog box is displayed (Fig. 16)

Area of ​​a circle

Enter the radius of the circle

Rice. 16 The user enters a number into the text box and selects a command button OK or Cancel to close the dialog box just like any other Windows window. Whenever you call any function, you need to somehow use the return value of the function. The value returned by the function is called function result(function result). As shown on line 8 in the case of the function InputBox, the result of which is assigned to a variable Temp. Function result InputBox is always a string (that's why the variable Temp was announced as String). Since the variable Temp was declared explicitly as String, the string value must be converted to a numeric value before it can be used in mathematical calculations. Line 9 of Listing 7 does just that, using the built-in VBA function CSng to convert user input to a number of type Single.

Expressions inVisualBasic

Expression(expression) is a value or group of values ​​that expresses a single value. Every expression calculated to(or has the result of) a separate meaning. Expressions consist of one or more of the following parts: constants (literal or named), variables (any data type), operator signs, arrays, array elements, functions.

All expressions result in a single value of a specific data type. Expressions can also result in one of the special values Empty(an uninitialized variable of type Variant or the result of an expression containing an uninitialized variable of type Variant) or Null(Null represents an expression containing invalid data). When you use a character in an expression, the data elements (variables or constants) on which the action is performed are called operands(operands); most operations require two operands.

Data type compatibility. Automatic data conversion

Not all data types are compatible with each other, and you cannot use incompatible data types in the same expression. For example, arithmetic addition of a string to a number does not make sense, since such an expression is not meaningful and VBA cannot evaluate it.

Many data types are compatible with each other. For example, you can

combine different numeric data types in the same expression; VBA automatically performs the necessary type conversions for various numeric types. VBA can also sometimes automatically convert other data types so that all types in an expression are compatible, although this is not always possible. It is very important to control and know the type of the expression because if the expressions contain incompatible types, VBA throws a runtime error - error type mismatch(type- mismatch). When processing an expression containing different data types, VBA first "attempts" to resolve any type differences by converting the values ​​in the expression to compatible data types. If type conversion fails to resolve any differences, a run-time error is displayed and the procedure stops executing. For example, in the expression 25 & Computer Science, VBA always performs string concatenation (joining two strings together), regardless of variable types; the result is the type String; this expression never causes a type mismatch error.

VBA typically converts all numeric data types in an expression to the highest precision type and then gives that type to the result of the expression. For example, if the expression contains numeric values ​​with types Integer And Single, the result of the expression is the type Single– the type of greatest precision in this expression. If you assign the result of a numeric expression to a variable with less precision than the actual type of the expression result, VBA rounds the result of the expression until its precision matches the expected type. For example, if you assign a numeric expression that results in a number of type Double, variable type Integer, VBA rounds double precision number to type Integer.

When you convert a number to a string, VBA creates a string containing all the digits of that number and a decimal place (if the number has one). The number 3413.72 (the dot is used to represent a number in code), for example, is converted to the string "3413.72". If the number is very large or very small, VBA can create a string representation of the number in scientific notation; for example, the number 0.0000000004927 is converted to the string "4.927E–11".

VBA can convert a string to a number only if the string contains a symbolic representation of the number in decimal or scientific notation. The strings "988.6", "812", "-186.7", "1,ZE10" represent numbers, and VBA can convert them to numbers. The lines "1,045", "$74,550" and "Good morning!" cannot be converted to numbers.

When VBA converts values ​​of type Boolean in numbers, meaning True is converted to 1 and the value False– to 0. When VBA converts a number to a type Boolean, zero is converted to False, and any other value is converted to True. When VBA converts values ​​of type Boolean to strings, VBA uses the string "True" to True and "False" – for False.

When VBA converts data type Date to a number, the result is a numerical value - a number of type Double, which contains the number of days since December 30, 1899 (a negative number represents a date earlier than 12/30/1899). The decimal part of the number (if present) expresses the time of day as a part

day; 0 is midnight and 0.5 is noon. In VBA, converting numeric data types to types Date is simply the inverse of the type conversion Date in number.

Assignment operator (=)

This operator is used to assign the result of an expression to a variable. The syntax of the assignment operator form is as follows:

varname = expression

variable varname - any variable, a expression - any expression.

When you execute an assignment operator, VBA first evaluates the expression to the right of the assignment operator (=) and then stores the result of the expression in the variable whose name is to the left of the assignment operator.

Illustration of the assignment operator in a block diagram:

varname = expression

When you assign the result of an expression to a variable of a particular data type, that result can be of a data type that is compatible with the type of the variable receiving the new value. In many cases, VBA can convert the data type of the result of an expression to a type compatible with the type of the variable receiving the new value if the result of the expression and the variable do not already have compatible types. Variable type Variant any data type can be assigned.

Arithmetic operations

VBA can perform all the usual arithmetic operations (implemented through arithmetic expressions): addition, subtraction, multiplication and division, as well as raising numbers to a specified power, and provides additional special mathematical operations for integer division and modulo division (Table 11).

Table 11- Operation signs (notations) used in VBA arithmetic expressions (Ni is any valid VBA numeric expression)

Both operands must be numeric expressions or strings that VBA can convert to a number.

Comparison Operations

Comparison operations are sometimes also called relational operations(relational operators). Most often, comparison operations are used to set criteria for making a decision or to formulate a description of the conditions under which a group of commands should be repeated (cycle organization).

The result of any comparison operation is a value of type Boolean: True or False. Comparison operators are used to compare literal, constant, or variable values ​​of any similar type (Table 12).

Name/description

Equality. True , IfElequals E2, otherwise –False

Less than. True , IfElless than E2, otherwise –False

Less than or equal to.True, if E1 is less than or equal

E2, otherwise –False

More than. True , if E1 is greater than E2, otherwise –False

Greater than or equal to.True, if E1 is greater than or equal to

E2, otherwise –False

Not equal.True, if E1 is not equal to E2, otherwise – False

Object. True, if El refers to the same object as E2, otherwise –False Similarity. Both operands must be values ​​of type

Table 12– Comparison symbols (E in this table represents any valid VBA expression)

example,

String. True, if El matches

Binary and text string comparison

VBA provides two different ways to compare characters of different cases. The first method that VBA uses to compare strings is called binary (binary) or binary comparison and is the default comparison method. To select VBA's string comparison method (binary or text), use the directive OptionCompare:

Option Compare [ Binary | Text]

In text comparison, VBA "treats" uppercase letters as equivalent to lowercase letters.

String concatenation

VBA provides the ability to concatenate (glue) strings together to form longer strings. Appending one string to another is called concatenation(concatenation) lines. The & sign can only be used to concatenate strings. The general syntax for the & sign is:

Operand1 & Operand2 [& Operand3… ]

Operand1 And Operand2 – any valid string or numeric expressions. If one or both operands are numeric expressions, VBA converts the numbers to strings before performing the concatenation operation. The data type of the result of string concatenation is always the type String.

Logical operators

The most common uses of VBA logical operators are to combine the results of individual comparison expressions to create complex decision criteria in a procedure, or to create conditions under which a group of statements must be repeated (Table 13).

Table 13– Logical operators (E in this table represents any valid expression with a Boolean result, such as a comparison operator)

Operator Syntax Name/Description

And El And E2 Conjunction. True, if both E1 and E2 are significant

True , otherwise -False Or El Or E2 Disjunction. True, if one expression or both (E1 and

E2) are equalTrue ; otherwise -False Not Not El Negation. True, if E1 has a value False; False,

IfElis equalTrue

Exception. True, if E1 and E2 have different

values; otherwise -False

Equivalence. True, if E1 has the same

meaning the same as E2; otherwise -False

Implication. False, when E1 is equal True And

E2 is equal toFalse ; otherwise -True .

Operation priorities when evaluating complex expressions

Complex(compound) expression(complex expression) is any expression formed from two or more expressions. Many of the expressions you write are complex expressions, especially if they control the execution of code in procedures or represent various mathematical formulas (Table 14).

Table 14– Hierarchy of statements/operations from highest to lowest priority

Using FunctionsVisual Basic

Have you already used the built-in VBA functions: InputBox And MsgBox. Function(function) is a built-in formula that performs operations on expressions and generates a value. A function always returns a value, which VBA inserts into the program where the function name appears. VBA functions are divided into several groups depending on the type of operation or calculation they perform. Don't confuse the terms function And procedure. Typically, a procedure performs a specific task (or group of tasks), much like a specific menu command in Excel, Word, or another application performs a specific task. A function operates on one or more values ​​and returns some resulting value (like a formula in a cell in an Excel worksheet). To use a function, simply enter the function name into a VBA statement along with any arguments that the function requires at the point in the statement where you need to use the function's result. Placing the name of a function in a VBA statement to invoke the function is called challenge(calling) functions.

VBA built-in functions are divided into several categories based on the general purpose of the functions (mathematical, data conversion, date and time, string, and disk manipulation).

Mathematical functions

VBA provides a standard set of mathematical functions (Table 15). Table 15– VBA Math Functions (N means any numeric expression)

Returns/Action

Functions(ar-

gums)

Returns absolute valueN

Abs(N)

Cosine of angleN, WhereN

Cos(N)

Returns the sine of an angle;Nis the angle measured in radians

Sin(N)

Tan(N)

Returns the tangent of an angle;N– angle in radians

Atn(N)

Returns the arctangentNas the angle in radians

Returns a constant e, raised to the power N ( e is the base of natural logarithms and it is (approximately) equals 2.718282)

Returns the integer part of N. Fix does not round the number, but discards any fractional part. If N is negative, Fix returns the nearest negative integer greater than or equal toN

Log(N)

Returns the integer part of N. Int does not round the number, but discards any fractional part. If N is negative, Int returns the nearest negative integer less than or equal toNReturns the natural logarithmN

random

argument

is

Returns

optional. Use the Rnd function only after

initializing the VBA random number generator with the operator

Randomize

Returns the sign of the number: –1 if N is negative; 1 if N –

positive; 0 ifNequals 0

Returns the square root of N. VBA displays an error

execution time, ifN– negative

Data conversion functions

Visual Basic provides several functions for converting one data type to another (Table 16). Use these functions to resolve type mismatch errors and provide explicit control over data types in expressions.

Table 16– Data conversion functions (N is any numeric, S is any string, and E is an expression of any type)

Function(ar- Returns/Actiongums)

Returns the character code number corresponding to the first letter linesS. The letter "A", for example, has a character code of 65

Date and time functions

VBA date and time functions are typically used to get the current date and time, break a date value into its component parts, or convert strings and numbers to values ​​like Date(Table 17).

Table 17– Date and time functions (N is any valid numeric expression, and D is any valid expression like Date(including values ​​like Date, numbers or strings that VBA can convert to a date); all function arguments are required unless otherwise noted)

Returns/Action

Functions(ar-

gums)

Returns the system date. You can also use this function as a procedure to set your computer's system clock. More details can be found in the reference book. systemsVBA

Returns the computer's system time as a value of type Date. You can also use this function as a procedure to set the system clock. More details can be found from the help systemVBAReturns the system date and time

Date And

Returns an integer that is part of an expression of type Date,

inclusive

Returns an integer that is part of an expression of type Date And

Weekday(D) Hour(D)

inclusive

Returns an integer containing the day of the week for an expression of type

Date. The day of the week is returned as a number between 1 and 7,

inclusive; 1 is Sunday, 2 is Monday and so on

Returns an integer containing the hours as part of the time,

a number between 0 and 23, inclusive. If expression D is not

contains time values, thenHourreturns 0

Functions(ar-gums)

DateAdd(S, N, D)

Returns an integer containing minutes as part of the time in

expression type Date. The minutes are returned as a number between 0

and 59, inclusive. If expression D does not contain a value

time,Minutereturns 0

Returns an integer containing seconds as part of the time in

expression type Date. The seconds are returned as a number between

0 and 59, inclusive. If expression D does not contain a value

time,Secondreturns 0

Returns value [type Variant(Date)], containing the date to

DateDiff(S, D1, Returns [type Variant(Long)] number of temporary D2[,Nl [, N2 ]]) intervals between two specific dates DatePart(S, D, [, Returns the specified part [type Variant(Integer)] N1 [, N2]]) given date

Returns the sequential date value for the given date. From left to right, the arguments represent the year, month, and day. The year argument must be an integer between 100 and 9999, the month must be between 1 and 12, the day must be between 1 and 31 (all ranges are inclusive)

Returns the serial time value. From left to right, the arguments represent hours, minutes, and seconds. The hours argument must be an integer between 0 and 23, arguments minutes and seconds must both be numbers from 0 to 59 Returns a value of type Date, equivalent to the date specified by argument E, which must be a string, number, or a constant representing a date

midnight according to the computer system time Some of the functions listed have already been used in the examples in this book, others will be used further.

String functions

VBA string functions are often used to find specified strings within other strings, compare one string to another, and copy selected portions of strings (Table 18).

Table 18– String functions (N is any valid numeric expression, and S is any valid string expression)

gum)

Returns a string (type String), containing a copy of S with all uppercase characters converted to characters lowercase

Returns a string; copies N characters from S, starting from the left last characterS

Returns the number of characters in S, including leading and trailing characters spaces

from the left side of the line (leading spaces) Returns a string; copies N2 characters from S, starting at the character position in S specified by N1. N2 is optional; if N2 is omitted, then Mid returns everything characters in a stringSfrom positionN1 to end of line Returns a String value; copies N characters from S,

starting from the rightmost characterS

Returns a copy of the string S after whitespace characters have been removed

from the right side of the string (ending characters)

Returns a string of spaces of lengthNcharacters

Compares S1 with S2 and returns a number indicating the result of the comparison: -1 if SI< S2; 0, если SI = S2; 1, если SI >S2. N is optional and specifies whether the comparison should be case sensitive. If N is omitted, strings are compared using the current setting OptionCompare

Returns a string converted to a new form based on the numeric code specified by N. VBA provides internal constants for use with the StrConv function; the most useful are: vbProperCase(converts the string so that every letter that begins a word becomes capitalized), vbLowerCase(converts the string to lowercase letters) and vbUpperCase(converts the string to uppercase letters)

Function(ar- Returns/Actiongum)

String(N, S) Returns a string of N characters long, consisting of the character

specified by the first character inS Trim(S) Returns a copy of the string S after removing the leading and

trailing whitespace characters from this string UCase(S) Returns S with all lowercase characters,

converted to uppercase characters Several listed in the table. The 16 data type conversion functions also apply to string manipulation: Chr, Format, CStr, in particular.

Formatting Data Values

Although VBA can automatically convert any data type to a string for display using the MsgBox function or for insertion into an Excel worksheet, the data format that VBA chooses may not be the one you want. When converting a number to a string, VBA does not add a thousands separator, dollar signs, or other number formatting to the string. Additionally, if a number is very large or very small, VBA creates a string representing that number in scientific notation. When converting dates, VBA always uses the short date and time format used by the computer's operating system and always displays both the date and time.

To get almost any date format when converting numbers or dates to strings, you can use the function Format; you can even use the function Format to format string data according to a specific pattern. You can also create custom screen formats if you need the data to appear in a specific format. Format operator syntax:

Format( Expression [, Format[, Firstdayofweek [, Firstweekofyear]]])

Expression – any valid expression (mandatory); Format – a valid expression of a named or user-defined format (optional); Firstdayofweek – a constant that defines the first day of the week (optional); Firstweekofyear – a constant that defines the first week of the year (optional).

For arguments Firstdayofweek And Firstweekofyear VBA has named constants, which you can learn about in the VBA Help under Date Constants.

To use the function Format, you can either specify a predefined format (called named format(named format), or create an image of a specific format using combinations of a special group of characters called placeholder characters(placeholders). If you need to create custom formats for numbers, dates, or times, you need to create a string containing placeholder characters to specify the formatting that the function should use Format when converting values ​​to a string (Table 19). In addition, in table. 19 used

as an example, the numerical value is 1234.5.

to create custom

Table 19– Format filler characters

Using host application functions

In addition to the functions built into Visual Basic for Applications, some VBA host application functions are available from VBA code. Host-application is an application in which VBA procedures such as Word, Excel, PowerPoint, Outlook or FrontPage are developed. Excel, for example, has various functions that perform mathematical, logical, financial, and statistical operations on data in worksheets. Many (though not all) of these Excel functions are accessible from VBA code. The host application functions available to VBA are not part of VBA, they are part of the host application. Features available to VBA in one host application may not be available in another.

To use a function that belongs to a host application, access the function through a program object Application. The Application VBA object represents the host application and all its resources. Objects are described in more detail in Part 6.

Task 6. Write a procedure that uses Excel's Max function.

For this:

Enter the procedure (Listing 8):

Listing 8 – Using Excel functionsMax

1 Sub List3_08()

2 MsgBox Application.Max(9, 17, -18, 20)

Behind the word Application followed by a dot (.) and then the function name Max without spaces. This point, called separator dot(dot separator), indicates that the statement refers to a function Max, which is part of the object

Application.

The result of the Excel function cannot be ignored. You should always include parentheses in an Excel function call, and you should always use the function result in some way: as a value in an expression, an argument to another function or procedure, or in an assignment statement.

Not every host application function is available to VBA. If you are not sure whether a certain host application function is available to VBA, use Object Browser to check if the list includes Members (Component) this function, when selected WorksheetFunction on the list Classes (Classes) and with the selected host application in the list Project/ Library (Project/Library). If the required function is not in the list, then it is not available for VBA.

Control questions

Define the concept of “data types”. What data types are used in VBA?

What is an ID?

What is a variable? What does the expression “define a variable implicitly” mean and what does it mean “explicitly”?

What constants are used in VBA? What are internal constants?

What are the InputBox and MsgBox functions used for?

What is an expression?

Define the assignment operator syntax.

What are relational operations?

What is the priority of mathematical operations?

What keyword exists in VBA that you need to use when accessing Excel functions? What can you use the Object Browser window for?

Procedures and functions are separate blocks that make up the program code; each procedure performs a task or part of it.

Event routines are constantly waiting for events after being called.

In addition to event processing procedures, the program can include procedures and functions not related to events. They perform separate actions and can be used repeatedly. Let's call them general. General-purpose procedures are called for execution in program code. Using procedures saves time and avoids unnecessary mistakes. Functions differ from procedures in that they return a value.

A procedure or function is a sequence of operations that must be performed repeatedly in different places in the application. In this case, the required block of commands is written in the code only once, after which it can be accessed from any part of the program.

Function is a subroutine that is called to perform some kind of calculation or check. When it completes its work, it returns control to the calling program and passes the calculation result to it.

Procedure- this is also a subroutine. It is also called to perform some action, but it is not required to return any values ​​to the main program.

Procedure and function declaration syntax:

Sub<Имя процедуры>(<Параметры>) <Операторы>End Sub Function<Имя функции> <Операторы>End Function

Procedures declared with the Public keyword can be called in any application module (each form is a separate module).

Procedures declared as Private can only be called in the current module.

The word Static means that all variables declared in the procedure will be static, i.e. their values ​​are preserved between calls.

Parameters provide the connection between a procedure and an application. This is the data passed to the procedure when called.

Event handling procedures. Called when an event occurs. In this case, both the name of the element and the type of event that happened to it are significant.

Custom procedures. Groups of operators created by the developer to perform specific tasks and not depending on the current state of the application or events that occurred at one time or another.

Built-in functions. Certain sets of commands available in the Visual Basic language and intended for calculating certain values ​​based on source data. In particular, both mathematical and string functions are built-in (Abs, Cos, Sin, Mid, Len, etc.)

Custom functions. Groups of statements similar to user procedures.

However, there are a number of differences between them.

The main differences between a function and a procedure are as follows.

1. A function has a type (similar to a variable) and can return a value to the program, which is assigned to the function using the operator:

<Имя функции>= value

2. A function is called, as a rule, by specifying its name and parameters on the right side of any operator. On the other hand, the procedure is called using a separate statement:

Call<Имя процедуры>(Options)

<Имя процедуры>(Options)

If the Call keyword is used when calling a procedure, then the list of parameters must be indicated in parentheses. If the procedure is called without using Call, then its parameters are listed without parentheses.

It should be noted that the called procedure may not have parameters. In this case (if the service word Call was used), empty parentheses should be placed after the procedure name.

User procedures are usually used when it is necessary to perform the same sequence of operations. For example, a program requires you to repeatedly enter the values ​​of an array arrA, consisting of five elements, in a loop. In this case, filling the array is best done as a procedure.

The Add Procedure command of the Tools menu allows you to add a procedure or function.

Let the Cir procedure draw an ellipse with coordinates x, y, which are passed to the procedure as parameters. When creating a Cir procedure with the Add Procedure command, you need to specify the procedure name and select the scope Public or Private.

Having completed the dialogue, we get a declaration of the procedure:

Private Sub Cir() : End Sub

Now you need to enter the parameters in brackets and write the text of the procedure. It is recommended to indicate the type of variables in the list of parameters.

Private Sub Cir(x As Integer, y As Integer) Circle (x,y),500,2 End Sub