Creating a user function. Examples of user functions
Creating a user function in VBA Excel, its syntax and components. Description of the user function and its arguments. Application.Macrooptions method.
Custom function - This is a VBA procedure that produces specified calculations and returns the result. Used to insert into worker cells leaf Excel Or to call from other procedures.
Announcement of user function
Syntax function
Function Name ([List)) [Operators] [Name \u003d Expression] [Operators] [Name \u003d Expression] END FUNCTIONFunction components
- Static. - Optional keyword indicating that the values \u200b\u200bof variables declared in functions are saved between its challenges.
- Name - Mandatory component, the name of the user function.
- Wrongs - optional component, one or more variables representing arguments that are transmitted to the function. Arguments are in brackets and separated by commas.
- Operators - optional component, block of operators (instructions).
- Name \u003d expression - optional * component, assigning the name of the function of expression or variable. Usually, the value is assigned the functions immediately before leaving it.
- EXIT FUNCTION - optional component, forced output from the function, if it has already been assigned the final value.
*One of the components Name \u003d expression It should be considered mandatory, since if you do not assign the functions of the value, the meaning of its use is lost.
Function visibility
The visibility of the user function is determined by the optional keywords of Public and Private, which can be listed in front of the FUNCTION OPERATION (or STATIC, in case of use).
Keyword Public Indicates that the function will be available for calling from other procedures in all EXCEL open book modules. Function declared as PublicDisplays the functions wizard dialog box.
Keyword Private Indicates that the function will be available to call from other procedures only within the software module in which it is located. Function declared as Privateis not displayed in the functions wizard dialog box, but it can be entered into the cell manually.
If the Public or Private keyword is not specified, the function is considered to be default declared as Public.
To ensure that the user function has always been available in all open Excel books, save it in without an ad visibility or as Public. But if you plan to transfer a workbook with a user function to another computer, the function code must be in the program module of the transmitted book.
An example of a user function
For example, we will look at the simplest user function, which in the next paragraph will add description. The "Decision" function is called with a Variant data type, since its return value can be a number and text. The arguments of the function - a divide and divider - also announced as Variant, as in excel cells There may be numeric values \u200b\u200bof different types, and the ISNUMERIC function also checks different types of data and requires its arguments to be announced as Variant.
Function Delivery (dividimi as Variant, Dimel AS Variant) AS Variant if isnumeric (dividimy) \u003d false or isnumeric (divider) \u003d false then division \u003d "Error: Delimi and Divider must be numbers!" EXIT FUNCTION ELSEIF Divider \u003d 0 THEN DELECTION \u003d "Error: division to zero!" EXIT FUNCTION ELSE DELECTION \u003d DELIME / DELIATEL END IF END FUNCTION
This feature fifts the values \u200b\u200bof the two cells of the Excel working sheet. Two block conditions are checked before dividing:
- If a divider or divider is not a number, the function returns the value: "Error: Delimi and divider must be numbers!", And a forced output is made from the EXIT FUNCTION operator function.
- If the divider is zero, the function returns the value: "Error: division to zero!", And a forced output from the function by the Exit Function operator is made.
If the validation values \u200b\u200bare not executed (returns the value of False), the numbers are made and the function returns the private (division result).
You can copy this function to your standard module and it will be available in the "User Defined" section of the Master of Functions. Try inserting the "division" function in the working sheet cell using the wizard and experiment with it.
Practical meaning The "division" function does not have, but it demonstrates well as announced, custom functions are created in VBA Excel. And it will help to demonstrate how to add description to the functions and arguments. With a full-fledged user function, you can familiarize yourself.
Adding a function description
In the list of functions displayed by the wizard, it is impossible to add or edit their description. The list of macros allows you to add description procedures, but it does not have functions. The problem is solved as follows:
- Run the functions wizard, see how the name is displayed. the desired function And close it.
- Open and enter the name of the user function in the "Macro name" field.
- Click the "Parameters" button and in the window that opens, add or edit the description.
- Click the "OK" button, then in the Macro List window - "Cancel". Description Ready!
Adding a description on the example of the "division" function:
Description of the "Decision" function in the Master of Function Arguments "dialog box:
Using the "Macro List" window, you can add a description of the function itself, and its arguments can not be. But this can be done using the application.macrooptions method.
Application.Macrooptions method
Application.Macrooptions allows you to add a user function Description, assign a key combination, specify a category, add descriptions of arguments and add or change other parameters. Let's look at the possibilities of this method used most often.
Example code with application.macrooptions:
Sub nameProgram () application.macrooptions _ macro: \u003d "name function", _ description: \u003d "function description", _ Category: \u003d "Category name", _ ArgumentDescriptions: \u003d Array ("Description 1", "Description 2", " Description 3 ", ...) End Sub
- NameProgram name - Any unique name suitable for procedures.
- Name Function - The name of the function whose parameters are added or changed.
- Description of the function - Description of the function that is added or varies.
- name of category - The name of the category to which the function will be placed. If the Category parameter is missing, the user function will be recorded in the default section - "user defined". If the specified category name corresponds to one of the names of the standard list, the function will be recorded in it. If the name of the category is not listed, a new section will be created with this name and the function will be placed in it.
- "Description 1", "Description 2", "Description 3", ... - Descriptions of arguments in the order as they are located in the declaration of user function.
This subroutine starts once, after which it can be deleted or used as a template to adjust the parameters of other user functions.
Now using the Application.macrooptions method, let's try to change the description of the user function "division" and add descriptions of arguments.
Sub-change () Application.Macrooptions _ Macro: \u003d "Division", _ description: \u003d "DESCRIPTION OF THE DELECTION FUNCTION Changed by application.macrooptions", _ ArgumentDescriptions: \u003d Array ("- any numeric value", "numerical value, except zero ") End Sub
After one-time launch of this subroutine, we get the following result:
The application.macrooptions method does not work in, but here you can find a solution. Add descriptions to user functions and their arguments in an ordinary Excel book, then export a module with features to any hard disk directory and from there import macros to a personal book. All descriptions will be saved.
Most of the Microsoft Excel working sheet features can be used in the Visual Basic code (list of these features: List of Worksheet Functions Available to Visual Basic)
Part of the Excel functions in Visual Basic is simply not needed. For example, instead of a function Concatenate. (Catch up) in the Visual Basic code to combine the texts using the operator &
.
Calling the function of the working sheet from Visual Basic
In Visual Basic, the working sheet functions (Worksheet) Microsoft Excel are available through the object. WorksheetFunction..
For example, here uses a function MIN. To determine the smallest value in the range of cells:
Sub UseFunction () Dim MyRange AS Range Set MyRange \u003d Worksheets ("Sheet1"). Range ("A1: C10") Answer \u003d Application.WorksheetFunction.min (MYRANGE) MSGBox Answer End Sub
Here, the MYRANGE variable is first announced as an object type. Range.And then it is assigned the range of cells A1: C10 on Sheet1 sheet. Answer variable is assigned the result of the application of the function MIN. To MyRange. The resulting ANSWER is displayed in the message window.
Range of cells need to be declared as an object Range.!
For example, in the Formula in the Excel Table Cell
\u003d Match (9, A1: A10,0)
And in the code Visual Basic -
Sub Findfirst () MyVar \u003d Application.WorksheetFunction.Match (9, Worksheets (1) .range ("A1: A10"), 0) MSGBox MyVar End Sub
Visual Basic features (without prefix WorksheetFunction.) may have the same name as excel featurebut work otherwise. For example, Application.WorksheetFunction.log and Log return different values.
Insert working sheet functions in cell
To insert a function into a cell, you need to assign it to the property Formula. Relevant object Range..
For example:
Sub InsertFormula () Worksheets ("Sheet1"). Range ("A1: B3"). Formula \u003d "\u003d Rand ()" END SUB
the result of the RAND function (it returns a random number) is assigned to the Formula property of the range of cells A1: B3 on the Sheet1 sheet of the active book Excel.
Using Microsoft Excel Worksheet Functions in Visual Basic Office 2003.)
Virtually the entire VBA module code is contained in the procedures of two types of SUB (subroutines) and Function. The main task of the Function procedure is the calculation of some value and return it to the point of calling the function.
Syntax Function Procedure:
Function_name_name (AS arguments) as
Function_name \u003d Return_Vestation
End Function.
Function function procedures can be used in various expressions.
Example 1.
For example, the simplest procedure function Function:
FUNCTION F1 (X As Currency) AS CURRENCY
FUNCTION F1 (X) can be used in further computing (software code of the module). Function procedure can be performed only by calling it from another procedure. To do this, in the calling procedure, you must assign the name of this F1 (x) to some variable.
Example 2.
For example, FUNCTION F1 (X) can be used in the MySub () procedure, assigning the variable "U" name F1 (X).
Dim Y As Single 'Variable Announcement Y
y \u003d F1 (9) 'Determine F1 (x) for x \u003d 9
Debug.Print Y 'output in the Immediate window
FUNCTION F1 (X AS SINGLE) AS SINGLE
F1 \u003d X ^ 10 'Return value x to degree 10
Here, FUNCTION F1 (x) \u003d $ x ^ (10) $ for the value of $ x \u003d 9 $ returns to the calling procedure MySub () value of $ 3,486785e + $ 29. If for the value returned by the function or variable, which is used in the VBA procedure, the data type is not declared, then the variant data will be specified by default.
VBA uses both FUNCTION-Function procedures and built-in functions.
Built-in functions consist of two parts: name (identifier) \u200b\u200band arguments. Built-in features are ready-made VBA formulas that perform certain actions on expressions and return some value to the point of their call.
Functions Return the resultant value instead of their name, which is used in further calculations. As a rule, functions require the presence of arguments that are recorded in brackets through a comma. But some functions do not require arguments. For example, the NOW () function that does not require arguments returns the current system date and time. Functions can be used to create new expressions or functions.
How to insert a function in the text of the program? To use the function in expressions, you must enter its name to the VBA statement. To call a built-in function that does not require arguments, it is enough to enter its name (for example, NOW) into the module program code:
Sub MyDate ()
DIM TD 'Declaration of Variable TD
TD \u003d NOW 'Determine the current system date and time
Debug.Print TD 'output in the Immediate window
To call a function that requires input one or more arguments, it is necessary to enter the name with the parameters enclosed in the brackets (values \u200b\u200bof the arguments) in the right side of the assignment operator. For example, to call the built-in FUNCTION LOG (N) with one variable N in the sub procedure of the Log_n variable, the name of the Log (50) function is assigned to the value of the argument of 50.
SUB NaturalLogrom ()
Dim Logn 'Variable Logn
Debug.print logn 'output in the Immediate window
Note 1.
Here, the built-in function log (n) for an argument value of 50 returns the value of 3,91202300542815 to the call point of the Log (50) of the Cause procedure "SUB Natural_Logarifm ()". The function can be called as using a separate VBA statement, and placing its name with the list of arguments (parameters) values \u200b\u200bin a formula or expression in the VBA program. In VBA, a function attachment mechanism is used to reduce recording, which allows you to specify a single function call as an argument for another function. In this case, the value returned by the first function is used as an argument for the next function.
The VBA has a large set of built-in features and procedures that simplify programming that can be divided into the following categories:
- mathematical;
- type check functions;
- row processing functions;
- formatting function;
- format conversion functions;
- date and time functions.
Mathematical functions
Mathematical functions include:
- ABS (X) - an argument module $ x $;
- COS (X) - Cosine argument $ x $;
- Exp (x) - the construction of the base of the natural logarithm to the degree of $ x $;
- Log (x) - logarithm natural argument $ x $;
- RND is a random number from the interval;
- Sin (x) - Cine argument $ x $;
- SQR (X) - a square root of $ x $;
- ATN (X) - Arcthangence from $ x $;
- Tan (x) - tangent from $ x $;
- SGN (X) - a $ x $ sign.
FIX (X) and INT (X) Both functions discard the fractional part of the number and return an integer value. The difference between these functions consists for negative values \u200b\u200bof the argument. Int (x) Returns the nearest negative integer smaller or equal to x, and Fix (x) is the nearest negative integer, greater or equal to x.
Type check functions
We give functions that determine which type is the variable:
- Isarray (x) - the function checks whether the variable is an array;
- ISDATE (x) determines whether the variable is the date;
- ISerror (x) Determines whether the error code variable is;
- ISNULL (x) determines whether the variable is empty value;
- ISnumeric (x) determines whether the variable is a numerical value;
- IsObject (x) Determines whether the variable object is the object.
Formatting function
Formatting function Returns the value of the Variant type (String), comprising an expression, decorated according to the function syntax:
Format (expression [, format [, first day of the week [, first week of the year]]]), where:
- Expression - Mandatory argument (any permissible expression - combination keywords, operators, variables and constants, the result of which is a string, number or object);
- The format is an optional parameter (any permissible named or user-defined expression).
If a $ sign is added to the function name, the function returns a string value., The function returns the string value.
When creating your own numerical format, you can use the following characters:
- 0 - reserves the position of the digital discharge. Displays a digit or zero. If a formatted number has some digit in this discharge position, where there is 0 in the format row, the function displays this figure if not, then zero is displayed in this position;
- "#" - the action of this symbol is similar to the action of 0 with the only difference that insignificant zeros are not displayed;
- . - reserves the position of the decimal separator, determines how many discharges it is necessary to display the left and right of the decimal point;
- % - reserves the percentage of the number;
- . - Shares hundreds of thousands.
Format conversion functions
These include:
- Val (Row) - Returns the numbers contained in the string as the numeric value of the corresponding type.
- STR (number) - Returns the value of the VARIANT (String) value, which is a string representation of the number.
In addition to the functions of VAL and STR, there are a number of functions for converting types of expressions.
Row processing functions
Among the set functions are the following:
- CHR (code) - converts ASCII - code into a string. For example, CHR (10) carries out the transition to new string, CHR (13) - return carriage;
- MID (STRING, POS [, LENGTH]) - Returns a row substitution containing a specified number of characters where String is a string expression from which the substring is retrieved;
- POS - the position of the symbol in the string string from which the necessary substring begins;
- Length - the number of returned substring characters;
- LEN (string) - Returns the number of string characters.
Time and Date Functions
Return a value of the VARIANT type containing a system date, current time, etc. For example, the Date function returns a value containing the system date.
When writing a VBA code in Excel, the set of built-in operators is used at each step. These operators are divided into mathematical, string, comparison operators and logical. Next, we will consider in detail each group of operators.
Mathematical operators
The basic Mathematical VBA statements are listed in the table below.
The right column of the table indicates the priority of operators, adopted by default in the absence of brackets. By adding a bracket to the expression, you can change the execution order vBA operators At your request.
String operators
The main string operator in Excel VBA. - this is a concatenation operator & (Merge):
Operators comparison
Comparison operators are used to compare two numbers or rows and return the logical value of the type Boolean. (True or False). The main comparison operators Excel VBA are listed in this table:
Logic operators
Logical operators, as well as comparison operators, return the logical value of the type Boolean. (True or False). The basic logical Excel VBA statements are listed in the table below:
The table below lists not all logical operators available in VBA. Full list Logical operators can be found on the Visual Basic Developer Center website.
Built-in functions
VBA has many built-in functions that can be used when writing code. Below are some of the most commonly used:
| Function | Act |
|---|---|
| ABS | Returns the absolute value of a given number.
|
| Chr. | Returns the ANSI character corresponding to the numeric value of the parameter.
|
| Date. | Returns the current system date. |
| DateAdd. | Adds a certain time interval to set date. Syntax features: DateADD ( interval , number , date ) Where is the argument interval Determines the type of time interval added to the specified date in the amount specified in the argument number . Argument interval Can take one of the following values:
|
| Datediff. | Calculates the number of certain time intervals between the two specified dates.
|
| Day. | Returns an integer that corresponds to the day of the month in a given date. Example: Day ("29/01/2015") Returns the number 29. |
| Hour. | Returns an integer corresponding to the number of hours at a specified time. Example: HOUR ("22:45:00") Returns the number 22. |
| Instr. | Takes as arguments an integer and two lines. Returns the position of entering the second line inside the first, starting a search from the position specified by an integer.
Note: The argument number may not be specified, in this case, the search begins with the first character of the string specified in the second argument of the function. |
| Int. | Returns the whole part of the specified number. Example: Int (5.79) Returns the result 5. |
| Isdate. | Returns True.if the specified value is the date or False - If the date is not.
|
| ISerror | Returns True.if the specified value is an error or False - If the error is not. |
| Ismissing. | As an argument, the function is transmitted to the name of the optional argument of the procedure. Ismissing. Returns True.If the procedure is not transmitted for the argument under consideration. |
| Isnumeric | Returns True.if the specified value can be considered as a number, otherwise returns False. |
| Left. | Returns the specified number of characters from the start of the transmitted string. The syntax of the function is this: Left ( line , length ) where line - This is the source string, and length - The number of returned characters, counting from the beginning of the string.
|
| Len. | Returns the number of characters in the string. Example: Len ("ABVGDEZH") Returns the number 7. |
| MONTH. | Returns an integer that corresponds to the month in a given date. Example: Month ("01/29/2015") Returns a value of 1. |
| MID | Returns the specified number of characters from the middle of the transmitted string. Syntax features: MID ( line , start , length ) where line - This is the source string, start - the position of the beginning of the recoverable string, length - The number of characters you want to extract.
|
| Minute. | Returns an integer corresponding to the number of minutes in a given time. Example: Minute ("22:45:15") Returns a value of 45. |
| Now | Returns the current system date and time. |
| Right | Returns the specified number of characters from the end of the transmitted string. Syntax features: Right ( line , length ) Where line - This is the source string, and length - This is the number of characters that need to be removed, counting from the end of the specified row.
|
| SECOND. | Returns an integer corresponding to the number of seconds in a specified time. Example: SECOND ("22:45:15") Returns a value of 15. |
| Sqr. | Returns the square root of the numerical value transmitted in the argument.
|
| Time. | Returns the current system time. |
| Ubound. | Returns the upper measurement index of a given array. Note: For multidimensional arrays, as an optional argument, the index of which measurement must be returned. Unless specified, then by default is 1. |
DIM NRESULT AS INTEGER
nmult1 \u003d Cint (InputBox ("Enter the first number:")) nmult2 \u003d Cint (InputBox ("Enter the second number:")) nresult \u003d fmultiply (nmult1, nmult2)
SELECTION.INSERTAFTER NRESULT SELECTION.COLLAPSE WDCOLLAPSEEND
4. In order to comment out the codeAutoNew (), highlight the entire code of this procedure (including Public Sub AutoNew () and End Sub) and press the COMMENT BLOCK button on the Edit toolbar.
3.9. Built-in features vBA language
3.9.1. What is built-in functions
IN vBA programming language provides several dozenbuilt-in features. They are available in any VBA program, while indifferent, in the medium of which software Product We are located - Excel, Word, Access or, for example, AutoCAD. They are used very actively, and in many situations could not do without them. Professional programmers apply them completely automatically, and ordinary users I would like to advise you to spend a few hours to get acquainted with them, because without the knowledge of these functions it will not work effectively in VBA. The additional argument in favor of their study is that the almost identical set of functions is in the usual Visual Basic and VBScript, and many of these features with the same names and syntax are found in other programming languages \u200b\u200b- C ++, Delphi, Java, JavaScript, etc. P.
IN help on VBA built-in functions are grouped by letters (Fig. 3.2).
Many students in the courses asked the question: Is there a certificate on these functions in Russian? Unfortunately, such a reference could not be found, so I will try to bring a brief help in this book. Next, it will be described about most of the actively used VBA languages \u200b\u200b( mathematical functions, such as cosine or tangent, which in practical work Almost not used, and we will not consider financial functions). The full syntax of functions to save space will not be given: the main thing is to understand what each function does and in what situations it can be used.
Functions in the following sections are grouped by its functionality. If you need to find information simply by the name of the function, you can use the subject pointer at the end of the book.
Fig. 3.2. Help for embedded functions
3.9.2. Conversion and verification features of data types
In VBA programs, it is very often necessary to convert values \u200b\u200bfrom one type of data to another. I will give some typical situations when it has to do:
converting a string value into a numeric when receiving the value from the user via INPUTBOX ();
convert date / time value in a string when we need to display a date or time uniformly regardless of regional settings on users' computers;
convert value from a string to date / time to apply special dates / time functions.
Most often, the functions of which are used to convert data types, the name of which is made from the "C" prefix (from the word convert) and the data type name. List of these functions Next: 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 that returns the name of the data type used, for example:
nVAR1 \u003d Cint (InputBox ("Enter the value")) MSGBOX TypeName (NVAR1)
In this case, this function will return "Integer".
In addition, there are still several useful functions for conversion.
STR () - allows you to translate numeric value into a string. It makes almost the same as CSTR (), but at the same time inserts the space before positive numbers.
Val () - "pulls out" from the mixture of numbers and letters is only a numeric value. In this case, the function reads the data from left to right and stops on the first non-numeric value (the only non-numeric value is allowed - the point that will separate the whole part from the fractional). It is very convenient when we have a movement or currency units or currency are prescribed with numerical data.
Isnumeric () and ISDate () - check the values \u200b\u200bfor compliance so that no errors occurred when conversion. To check for compliance with special values, you can use the functions of the ISARRAY (), ISEMPTY (),
ISerror (), ismissing (), isnull () and isobject () . All these functions return
true or False depending on the results of checking the value transferred to them.
HEX () and OCT () - convert decimal data into a string representation of hexadecimal and octal values.
3.9.3. String functions
These are the most frequently used features. They are required constantly, and it is necessary to know them very well.
ASC () - This feature allows you to return a numeric code for the transmitted symbol. For example, ASC ("D") will return 68. This feature is convenient to use in order to determine the following or previous letter. It is usually used with the CHR () function, which produces a reverse operation - returns the character to the numerical code transmitted. For example, such a code in Excel allows you to write in cells with A1 by A20 consistently the letters of the Russian alphabet from A to y:
Dim N, Ncharcode AS Integer N \u003d 1
ncharcode \u003d ASC ("A") do while n<= 20
ActiveWorkBook.ActiveVesheet.Range ("A" & N) .Value \u003d CHR (Ncharcode)
Syntax and software constructions VBA |
ncharcode \u003d ncharcode + 1 Loop
Options for this function - ASCB () and ASCW (). ASCB () Returns only the first byte of the numerical code for the symbol, and the ASCW () returns the code for the symbol in the Unicode encoding.
Chr () - Returns the character on its numerical code. In addition to what is used in a pair with ASC () function (see the previous example), it is not yet in one situation without it: when you need to output a service character. For example, we need to print in Word the value "Gazprom" (in quotes). Quotes - this is a service character, and an attempt to use a string of the form:
Selection.text \u003d "Gazprom" "
will lead to a syntactic error. But everything will be fine:
Selection.text \u003d chr (34) & Gazprom & CHR (34)
There are options for this function - chrb () and chrw (). Work similarly to the same options for the ASC () function.
Instr () and Instrrev () are one of the most popular functions. Allows you to detect in the body a string variable sequence of characters and return its position. If the sequence is not detected, then 0 is returned. The INSTR () function is looking for from the beginning of the string, and Instrrev () - from the end.
Left (), Right (), MID () - allow you to take the number of characters from the existing string variable to the left, right or from the middle, respectively.
Len () - Returns the number of characters in the string (string length). Often used with cycles, replacement operations, etc.
LCASE () and UCASE () - translate the string to the lower and upper registers, respectively. Frequently used to prepare value to compared when the register is not important (surnames, firms, cities, etc.).
LSET () and Rset () - fill one variable symbols of the other without changing its length (respectively to the left and right). Excess symbols are cut off, spaces are substituted into the place of missing.
Ltrim (), Rtrim (), Trim () - remove spaces according to the left, right or on the left, and on the right.
REPLACE () - replaces one sequence of characters to another in the string.
Space () and String () - Return the line from the number of spaces or characters you specify, respectively. Commonly used for form
