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

A function written in VBA is code that performs a calculation and returns a value (or an array of values). Once you create a function, you can use it in three ways:

1. As a formula in a worksheet, where it can take arguments and return values.
2. As part of your VBA routine. In a Sub procedure or inside other functions.
3. In the conditional formatting rules.

Although Excel already contains more than 450 built-in functions, they are also sometimes lacking. Sometimes built-in functions can't do what you want to do. Sometimes, to achieve a result, you need to create a huge and complex formula that is not understandable to others. In this case, you can create a custom function that is easy to read and use.

Note that user-defined functions created with VBA are generally significantly slower than built-in functions. Therefore, they are best suited for situations where you cannot get the result using built-in functions or there is not much computation and the performance penalty is not critical.

Not only you, but also your colleagues can use the built-in functions. The functions you write will appear alongside others in the Function Wizard dialog box. You may be intimidated by the process of creating functions, but I hasten to assure you that it is quite simple.

What is the difference between a procedure (Sub) and a function (Function)?

The main difference is that a procedure (sub) is used to execute a set of commands, and is not intended, unlike a function (function), to return a value (or an array of values).

To demonstrate, let's give an example. For example, there is a series of numbers from 1 to 100 and you need to separate the even ones from the odd ones.

Using the (sub) procedure, you can, for example, go through the cells and highlight the odd ones using fill. And the function can be used in the adjacent column and it will return TRUE or FALSE depending on whether the value is even or not. Those. you won't be able to change the fill color using a function in the worksheet.

Creating a Simple User-Defined Function in VBA

Let's create a simple user function in VBA and see how it all works.

Below is the code for a function that leaves only numbers from the text, discarding letter values.

Function Numbers(Text As String) As Long Dim i As Long Dim result As String For i = 1 To Len(Text) If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1 ) Next Numbers = CLng(result) End Function

For everything to work for you, you need to paste this code into the book module. If you don’t know how to do this, then start with the article How to record a macro in Excel.

Now let's see how the function works, let's try to use it on a sheet:

Before analyzing the function itself, let’s note 2 pleasant moments that appeared after its creation:

• It became available, like any other built-in function (we’ll tell you how to create a hidden function later).
• When you enter the "=" sign and start typing the name of the function, Excel displays all matches and shows not only the built-in functions, but also the custom ones.

Let's analyze the function step by step

Now let's take a deep dive and see how this feature was created. The function starts with the line

Function Numbers(Text As String) As Long

Word Function speaks about the beginning of the function, followed by its name, in our case Numbers.

• The function name cannot contain spaces. Also, you can't call a function if it collides with a cell reference name. For example, you cannot name the function ABC123 because that name also refers to a cell in the Excel worksheet.
• You should not give your function the same name as an existing function. If you do this, Excel will give preference to the built-in function.
• You can use the underscore character if you want to separate words. For example, Suma in cuirsive is a valid name.

After the name, the function arguments are described in parentheses. Similar to built-in Excel functions. In our case, the only argument used is Text. After the name of the argument we indicated As String, this means that our argument is a text value or a cell reference containing a text value. If you don't specify a data type, VBA will treat it as Variant(which means you can use any data type, VBA will figure it out for you).

Last part of first line As Long specifies the data type that the function returns. In our case, the function will return integer values. This is also not necessary.

The second and third lines of the function declare additional internal variables that we will use.

Dim i As Long Dim result As String

Variable i We will use it to enumerate characters. A variable result to store the intermediate result of a function.

The function's task is to go through all the characters in the Text variable and save only those that are numbers. Therefore, we will start the loop from 1 to the last character.

For i = 1 To Len(Text)

Len is a function that determines the number of characters.

The main line of the function is checking whether the next character of the text is a number and, if so, storing it in the result variable

If IsNumeric(Mid(Text, i, 1)) Then result = result & Mid(Text, i, 1)

For this we need a function IsNumeric- she returns True if the text is a number and False otherwise.

Function Mid takes from the Text argument i th character (meaning 1 , indicates that the function Mid takes only 1 character)/

Function Next- closes the loop For everything is clear here.

Numbers = CLng(result)

With this line we convert the text variable result, which contains all the digits of the argument Text, to a numeric value. And we say what result our function should output Numbers.

The last line of code is End Function. This is a required line of code that tells VBA that the function code ends here.

The above code describes the different parts of a typical user-defined function created in VBA. In future articles, we'll look at these elements in more detail, as well as look at different ways to perform a VBA function in Excel.

Function-procedure is a special type of VBA procedure that returns a result. User-defined procedure functions, like VBA built-in functions, can have optional and named arguments. You cannot use a macro recorder to record a procedure function, although you can edit a macro recorded by the recorder and turn it into a procedure function.

The main difference between a procedure function and other procedures, besides the fact that functions return a value and procedures do not, is that a procedure function uses keywords Function And End Function.

Syntax:

Function Name()
"VBA Statements
End Function

Function is a keyword that declares the start of a function.

Name - function name. Function names follow the same rules as other VBA identifier names.

Arglist - list of arguments for this function, optional element.

Type - any type of function return value. If the type is undefined, the result returned by the procedure function is of type Variant.

Name = expression is a function assignment that tells VBA what value the function should return, an optional element. However, you should always include an assignment operator in procedure functions.

End Function - keywords that end a function.

Even if the function has no arguments (for example, Now, Date), you must use parentheses in the function declaration.

Typically a function is designed to perform a calculation and return the result. When declaring a procedure function, you specify the name of each argument passed to the function. The argument names in the list are separated by a comma and must follow the rules that apply to any VBA identifier.

Below is a listing of the use of a simple procedure function that removes leading and trailing spaces and counts the number of characters in an expression.

Without using the function, the listing would look like this:

From this simple example, I think the main idea of ​​using procedure functions is clear - improving the readability of program code and reducing it (in other words, a procedure function is written when the same “piece” of code occurs more than 2-3 times in the program code). Indeed, if our procedure function consisted not of one line, but, say, 10 lines; and the program code would use this procedure function 5 times, then the overall program listing would be 38 lines shorter.

As stated earlier, VBA passes all arguments to a function procedure as Variant types. You can declare specific data types for each argument in the argument list.

Before you start creating your own VBA functions, it's helpful to know that Excel VBA has a large collection of pre-built built-in functions that you can use when writing code.

A list of these functions can be viewed in the VBA editor:

• Open an Excel workbook and launch the VBA editor (click here Alt+F11), and then click F2.
• Select a library from the drop-down list at the top left of the screen VBA.
• A list of built-in VBA classes and functions appears. Click on the function name to display a brief description at the bottom of the window. Pressing F1 will open the online help page for that function.

Additionally, a complete list of built-in VBA functions with examples can be found on the Visual Basic Developer Center website.

Custom Function and Sub Procedures in VBA

In Excel Visual Basic, a set of commands that perform a specific task is placed in a procedure Function(Function) or Sub(Subroutine). The main difference between the procedures Function And Sub is that the procedure Function returns result, procedure Sub- No.

Therefore, if you need to perform actions and get some result (for example, sum several numbers), then the procedure is usually used Function, and in order to simply perform some actions (for example, change the formatting of a group of cells), you need to select the procedure Sub.

Arguments

Various data can be passed to VBA procedures using arguments. The list of arguments is specified when the procedure is declared. For example, the procedure Sub in VBA, adds the specified integer (Integer) to each cell in the selected range. You can pass this number to the procedure using an argument, like this:

Sub AddToCells(i As Integer) ... End Sub

Be aware that having arguments to procedures Function And Sub in VBA is optional. Some procedures do not require arguments.

Optional Arguments

VBA procedures can have optional arguments. These are arguments that the user can specify if they want, and if they are omitted, the procedure uses the default values ​​for them.

Going back to the previous example, to make an integer argument to a function optional, you would declare it like this:

Sub AddToCells(Optional i As Integer = 0)

In this case, the integer argument i the default will be 0.

There can be several optional arguments in a procedure; they are all listed at the end of the argument list.

Passing arguments by value and by reference

Arguments in VBA can be passed to a procedure in two ways:

• ByVal– passing an argument by value. This means that only the value (that is, a copy of the argument) is passed to the procedure, and therefore any changes made to the argument within the procedure will be lost when it exits.
• ByRef– passing an argument by reference. That is, the actual address of the argument's placement in memory is passed to the procedure. Any changes made to an argument within a procedure will be saved when the procedure exits.

Using keywords ByVal or ByRef In a procedure declaration, you can specify exactly how the argument is passed to the procedure. This is illustrated below with examples:

Remember that arguments in VBA are passed by reference by default. In other words, if keywords are not used ByVal or ByRef, then the argument will be passed by reference.

Before you continue learning the procedures Function And Sub In more detail, it will be useful to take another look at the features and differences between these two types of procedures. The following is a brief discussion of VBA procedures Function And Sub and simple examples are shown.

VBA procedure "Function"

The VBA editor recognizes the procedure Function

Function...End Function

As mentioned earlier, the procedure Function in VBA (as opposed to Sub), returns a value. The following rules apply to return values:

• The data type of the return value must be declared in the procedure header Function.
• The variable that contains the return value must be named the same as the procedure Function. This variable does not need to be declared separately since it always exists as an integral part of the procedure Function.

This is perfectly illustrated in the following example.

Example VBA procedure “Function”: Perform a mathematical operation with 3 numbers

Below is an example VBA procedure code Function, which takes three arguments of type Double(double precision floating point numbers). As a result, the procedure returns another number of the type Double, equal to the sum of the first two arguments minus the third argument:

Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double SumMinus = dNum1 + dNum2 - dNum3 End Function

This is a very simple VBA procedure Function illustrates how data is passed to a procedure through arguments. You can see that the data type returned by the procedure is defined as Double(the words say this As Double after the list of arguments). This example also shows how the result of the procedure Function is stored in a variable with the same name as the procedure name.

Calling VBA procedure "Function"

If the simple procedure discussed above Function inserted into a module in the Visual Basic editor, it can be called from other VBA procedures or used on a worksheet in an Excel workbook.

Calling VBA procedure "Function" from another procedure

Procedure Function can be called from another VBA procedure by simply assigning this procedure to a variable. The following example shows a call to the procedure SumMinus, which was defined above.

Sub main() Dim total as Double total = SumMinus(5, 4, 3) End Sub

Calling a VBA procedure "Function" from a worksheet

VBA procedure Function can be called from an Excel worksheet in the same way as any other built-in Excel function. Therefore, the procedure created in the previous example Function – SumMinus can be called by entering the following expression into a worksheet cell:

SumMinus(10, 5, 2)

VBA procedure "Sub"

The VBA editor understands that there is a procedure in front of him Sub, when it encounters a group of commands enclosed between the following opening and closing statements:

Sub...End Sub

VBA procedure "Sub": Example 1. Center alignment and change font size in a selected range of cells

Let's look at an example of a simple VBA procedure Sub, whose task is to change the formatting of the selected range of cells. The cells are set to center alignment (both vertically and horizontally) and the font size is changed to the user-specified one:

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10) Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Size = iFontSize End Sub

This procedure Sub performs actions but does not return results.

This example also uses an Optional argument iFontSize. If the argument iFontSize not passed to procedure Sub, then its default value is taken to be 10. However, if the argument iFontSize passed to the procedure Sub, then the font size specified by the user will be set in the selected range of cells.

VBA procedure “Sub”: Example 2. Center alignment and applying bold to font in a selected range of cells

The next procedure is similar to the one just discussed, but this time, instead of changing the size, it applies a bold font style to the selected range of cells. This is an example procedure Sub, to which no arguments are passed:

Sub Format_Centered_And_Bold() Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True End Sub

Calling the "Sub" procedure in Excel VBA

Calling VBA procedure "Sub" from another procedure

To call a VBA procedure Sub from another VBA procedure, you need to write down the keyword Call, procedure name Sub and then in parentheses the arguments of the procedure. This is shown in the example below:

Sub main() Call Format_Centered_And_Sized(20) End Sub

If the procedure Format_Centered_And_Sized has more than one argument, they must be separated by commas. Like this:

Sub main() Call Format_Centered_And_Sized(arg1, arg2, ...) End Sub

Calling VBA procedure "Sub" from a worksheet

Procedure Sub cannot be entered directly into an Excel worksheet cell, as can be done with a procedure Function, because the procedure Sub does not return a value. However, the procedures Sub, which have no arguments and are declared as Public(as will be shown later) will be available to users of the worksheet. Thus, if the simple procedures discussed above Sub inserted into a module in the Visual Basic editor, then the procedure Format_Centered_And_Bold will be available for use on the Excel worksheet, and the procedure Format_Centered_And_Sized– will not be available because it has arguments.

Here's a simple way to run (or execute) the procedure Sub, available from the worksheet:

• Click Alt+F8(press key Alt and, while holding it down, press the key F8).
• In the list of macros that appears, select the one you want to run.
• Click Execute(Run)

To perform the procedure Sub quickly and easily, you can assign a key combination to it. For this:

• Click Alt+F8.
• In the list of macros that appears, select the one to which you want to assign a keyboard shortcut.
• Click Options(Options) and enter the keyboard shortcut in the dialog box that appears.
• Click OK and close the dialog box Macro(Macro).

Attention: When assigning a keyboard shortcut to a macro, make sure that it is not used like the standard one in Excel (for example, Ctrl+C). If you select an existing keyboard shortcut, it will be reassigned to the macro, and as a result, the user may accidentally trigger the macro.

VBA Procedure Scope

Part 2 of this tutorial discussed the topic of the scope of variables and constants and the role of keywords Public And Private. These keywords can also be used in relation to VBA procedures:

Remember that if before declaring a VBA procedure Function or Sub keyword is not inserted, then the default property for the procedure is set Public(that is, it will be available everywhere in a given VBA project). This is different from declaring variables, which by default are Private.

Early exit from VBA procedures “Function” and “Sub”

If you need to terminate the execution of a VBA procedure Function or Sub, without waiting for its natural ending, then there are operators for this Exit Function And Exit Sub. The use of these operators is shown below using a simple procedure example Function, which expects to receive a positive argument to perform further operations. If a non-positive value is passed to the procedure, then no further operations can be performed, so the user must be shown an error message and the procedure must terminate immediately:

Function VAT_Amount(sVAT_Rate As Single) As Single VAT_Amount = 0 If sVAT_Rate<= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If ... End Function

Please note that before completing the procedure Function – VAT_Amount, a built-in VBA function is inserted into the code MsgBox, which shows the user a pop-up warning window.

Dim nResult As Integer

nMult1 = CInt(InputBox("Enter the first number: ")) nMult2 = CInt(InputBox("Enter the second number: ")) nResult = fMultiply(nMult1, nMult2)

Selection.InsertAfter nResult Selection.Collapse wdCollapseEnd

4. To comment out the code AutoNew() , highlight all the code for this procedure (including Public Sub AutoNew() and End Sub ) and click the Comment Block button on the Edit toolbar.

3.9. Built-in VBA Language Functions

3.9.1. What are built-in functions

IN The VBA programming language provides several dozenbuilt-in functions. They are available in any program in the VBA language, and it does not matter what software product we are in - Excel, Word, Access or, for example, AutoCAD. They are used very actively, and in many situations one cannot do without them. Professional programmers use them completely automatically, but I would advise ordinary users to spend several hours getting to know them, because without knowledge of these functions they will not be able to work effectively in VBA. An additional argument in favor of studying them is that an almost identical set of functions is found in regular Visual Basic and VBScript, and many of these functions with the same names and syntax are found in other programming languages ​​- C++, Delphi, Java, JavaScript, etc. P.

IN In VBA help, built-in functions are grouped by letter (Fig. 3.2).

Many participants in the courses asked the question: is there help on these functions in Russian? Unfortunately, I was unable to find such information, so I will try to provide a brief reference in this book. Next, we will talk about most of the actively used functions of the VBA language (mathematical functions, such as cosine or tangent, which are almost never used in practical work, and we will not consider financial functions). To save space, the full syntax of the functions will not be given: the main thing is to understand what each function does and in what situations it can be used.

The functions in the following sections are grouped by functionality. If you need to find information simply by the name of a function, you can use the index at the end of the book.

Rice. 3.2. Built-in Functions Help

3.9.2. Conversion and data type checking functions

In VBA programs, it is very common to convert values ​​from one data type to another. Here are a few typical situations when you have to do this:

converting from a string value to a numeric value when receiving a value from the user via InputBox() ;

Converting a date/time value to a string value when we need to display the date or time consistently regardless of regional settings on users' computers;

converting a value from a string to a date/time to use special date/time functions.

Most often, to convert data types, functions are used whose name consists of the prefix “C” (from the word Convert) and the name of the data type. The list of these functions is as follows: CBool(), CByte(), CCur(), CDate(),

CDbl(), CDec(), CInt(), CLng(), CSng(), CStr(), CVar(), CVDate(), CVErr().

You can see what happened in the end using the TypeName() function, which returns the name of the data type used, for example:

nVar1 = CInt(InputBox("Enter a value")) MsgBox TypeName(nVar1)

In this case, this function will return "Integer" .

In addition, there are several other functions useful for conversion.

Str() - allows you to convert a numeric value to a string. Does almost the same thing as CStr(), but it inserts a space before positive numbers.

Val() - “pulls out” only a numeric value from a mixture of numbers and letters. In this case, the function reads the data from left to right and stops at the first non-numeric value (the only non-numeric value allowed is the dot, which will separate the integer part from the fractional part). It is very convenient when we write down units of measurement or currency interspersed with numerical data.

IsNumeric() and IsDate() - check values ​​for consistency so that there are no errors during conversion. To check for compliance with special values, you can use the functions IsArray(), IsEmpty(),

IsError(), IsMissing(), IsNull() and IsObject() . All these functions return

True or False depending on the results of checking the value passed to them.

Hex() and Oct() - Convert decimal data to a string representation of hexadecimal and octal values.

3.9.3. String functions

These are the most commonly used functions. They are constantly needed, and it is necessary to know them very well.

Asc() - this function allows you to return the numeric code for the passed character. For example, Asc("D") will return 68. This function is useful for determining the next or previous letter. It is usually used in conjunction with the Chr() function, which performs the inverse operation - it returns the character according to its numeric code. For example, this code in Excel allows you to write sequentially the letters of the Russian alphabet from A to U in cells A1 to A20:

Dim n, nCharCode As Integer n = 1

nCharCode = Asc("A") Do While n<= 20

ActiveWorkbook.ActiveSheet.Range("A" & n).Value = Chr(nCharCode)

nCharCode = nCharCode + 1 Loop

Variants of this function are AscB() and AscW(). AscB() returns only the first byte of the numeric code for the character, while AscW() returns the Unicode code for the character.

Chr() - returns a character by its numeric code. In addition to being used in conjunction with the Asc() function (see the previous example), you cannot do without it in one more situation: when you need to output a service character. For example, we need to type the value "Gazprom" (in quotes) in Word. The quote is a service character, and an attempt to use a string like:

Selection.Text = ""Gazprom""

will result in a syntax error. And this is how everything will be fine:

Selection.Text = Chr(34) & "Gazprom" & Chr(34)

There are variants of this function - ChrB() and ChrW(). They work similarly to the same options for the Asc() function.

InStr() and InStrRev() are some of the most popular functions. Allows you to detect a sequence of characters in the body of a string variable and return its position. If the sequence is not found, then 0 is returned. The InStr() function searches from the beginning of the string, and InStrRev() searches from the end.

Left() , Right() , Mid() - allow you to take the number of characters you specify from an existing string variable on the left, right or middle, respectively.

Len() - returns the number of characters in the string (string length). Often used with loops, replace operations, etc.

LCase() and UCase() - convert a string to lower and upper case, respectively. Often used to prepare values ​​for comparison when case is not important (surnames, names of companies, cities, etc.).

LSet() and RSet() - fill one variable with symbols of another without changing its length (left and right, respectively). Extra characters are cut off and spaces are substituted for missing characters.

LTrim() , RTrim() , Trim() - remove spaces from the left, right, or both left and right, respectively.

Replace() - replaces one sequence of characters with another in a string.

Space() and String() - return a string of the number of spaces or characters you specify, respectively. Typically used to form

Did not find an answer to your question? Look at here

Programming How to flash a Baofeng walkie-talkie

OK Google - Android voice assistant

Lazy loading images