"פונקציע" און "סאַב" פּראָוסידזשערז אין וובאַ

Built-in VBA Functions

Before you start creating your own VBA functions, it’s good to know that Excel VBA has a rich collection of prebuilt built-in functions that you can use while writing your code.

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

• Open an Excel workbook and launch the VBA editor (click to do this אַלט + פקסנומקס), און דעמאָלט דריקן F2.
• Select a library from the dropdown list at the top left of the screen VBA.
• A list of built-in VBA classes and functions will appear. Click on the function name to display its brief description at the bottom of the window. pressing F1 will open the online help page for that feature.

In addition, a complete list of built-in VBA functions with examples can be found at the Visual Basic Developer Center.

Custom procedures “Function” and “Sub” in VBA

In Excel Visual Basic, a set of commands that perform a specific task is placed in a procedure. פונקציע (Function) or סאַב (Subroutine). The main difference between the procedures פונקציע и סאַב is that the procedure פונקציע returns result, procedure סאַב – not.

Therefore, if you need to perform actions and get some result (for example, sum several numbers), then the procedure is usually used פונקציע, and in order to simply perform some actions (for example, change the formatting of a group of cells), you need to select the procedure סאַב.

Arguments

Various data can be passed to VBA procedures using arguments. The argument list is specified when declaring a procedure. For example, the procedure סאַב in VBA adds the given 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

Keep in mind that having arguments for procedures פונקציע и סאַב in VBA is optional. Some procedures do not require 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.

Returning to the previous example, to make an integer argument to a function optional, it would be declared like this:

Sub AddToCells (אָפּטיאָנאַל איך ווי ינטעגער = 0)

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

There can be several optional arguments in a procedure, all of which are 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:

• בייוואַל – 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 inside the procedure will be lost when the procedure exits.
• ByRef – passing an argument by reference. That is, the actual address of the argument location in memory is passed to the procedure. Any changes made to an argument inside the procedure will be saved when the procedure exits.

Using keywords בייוואַל or ByRef in the procedure declaration, you can specify how the argument is passed to the procedure. This is shown in examples below:

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

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

Remember that arguments in VBA are passed by reference by default. In other words, if keywords are not used בייוואַל or ByRef, then the argument will be passed by reference.

Before proceeding with the procedures פונקציע и סאַב in more detail, it will be useful to take another look at the features and differences between these two types of procedures. The following are brief discussions of VBA procedures פונקציע и סאַב and simple examples are shown.

VBA procedure «Function»

The VBA editor recognizes the procedure פונקציעwhen it encounters a group of commands enclosed between the following opening and closing statements:

Function    ...    End Function

As mentioned earlier, the procedure פונקציע in VBA (as opposed to סאַב) returns a value. The following rules apply to return values:

• The data type of the return value must be declared in the header of the procedure פונקציע.
• The variable that contains the return value must be named the same as the procedure פונקציע. This variable does not need to be declared separately, as it always exists as an integral part of the procedure. פונקציע.

This is well illustrated in the following example.

VBA Function Example: Performing a Mathematical Operation on 3 Numbers

The following is an example of a VBA procedure code פונקציע, which takes three arguments of type טאָפּל (double-precision floating-point numbers). As a result, the procedure returns another number of type טאָפּל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 very simple VBA procedure פונקציע illustrates how data is passed to a procedure through arguments. You can see that the data type returned by the procedure is defined as טאָפּל (the words say ווי טאָפּל after the list of arguments). This example also shows how the result of the procedure פונקציע stored in a variable with the same name as the procedure name.

Calling the VBA procedure “Function”

If the above simple procedure פונקציע 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.

Call VBA procedure “Function” from another procedure

פּראָצעדור פונקציע can be called from another VBA procedure by simply assigning that procedure to a variable. The following example shows a call to a procedure Summinus, which was defined above.

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

Call VBA procedure “Function” from a worksheet

VBA procedure פונקציע 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 פונקציע - 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 it סאַב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 font size change in a selected range of cells

Consider an example of a simple VBA procedure סאַב, whose task is to change the formatting of the selected range of cells. The cells are centered (both vertically and horizontally) and the font size is changed to the user-specified:

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

דעם פּראָצעדור סאַב performs actions but does not return a result.

This example also uses an Optional argument פאָנטסייז. If the argument פאָנטסייז not passed to procedure סאַב, then its default value is 10. However, if the argument פאָנטסייז passed to procedure סאַב, then the selected range of cells will be set to the font size specified by the user.

VBA Sub Procedure: Example 2: Center Align and Bold Font in Selected Range of Cells

The following procedure is similar to the one just discussed, but this time, instead of resizing, it applies a bold font style to the selected range of cells. This is an example procedure סאַב, which takes no arguments:

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

Calling “Sub” Procedure in Excel VBA

Call VBA procedure “Sub” from another procedure

To call a VBA procedure סאַב from another VBA procedure, you need to write the keyword רוף, procedure name סאַב and further in parentheses are 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

Call VBA procedure “Sub” from worksheet

פּראָצעדור סאַב cannot be entered directly into an Excel sheet cell, as can be done with a procedure פונקציעbecause the procedure סאַב does not return a value. However, procedures סאַב, which have no arguments and are declared as עפנטלעך (as shown below) will be available to users of the worksheet. Thus, if the simple procedures discussed above סאַב inserted into a module in the Visual Basic Editor, the procedure Format_Centered_And_Bold will be available for use in an Excel worksheet, and the procedure Format_Centered_And_Sized – will not be available because it has arguments.

Here is an easy way to run (or execute) a procedure סאַב, accessible from the worksheet:

• דרוק אַלט + פקסנומקס (press key אַלט and while holding it down, press the key F8).
• In the list of macros that appears, select the one you want to run.
• דרוק לויפן (לויפן)

To perform a procedure סאַב quickly and easily, you can assign a keyboard shortcut to it. For this:

• דרוק אַלט + פקסנומקס.
• In the list of macros that appears, select the one you want to assign a keyboard shortcut to.
• דרוק פּאַראַמעטערס (Options) and in the dialog box that appears, enter the keyboard shortcut.
• דרוק OK and close the dialog מאַקראָו (מאַקראָ).

ופמערקזאַמקייַט: When assigning a keyboard shortcut to a macro, make sure that it is not used as standard in Excel (for example, Ctrl + C). If you select an already existing keyboard shortcut, it will be reassigned to the macro, and as a result, the user may start the macro by accident.

VBA Procedure Scope

Part 2 of this tutorial discussed the scope of variables and constants and the role of keywords. עפנטלעך и פּריוואַט. These keywords can also be used with VBA procedures:

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

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

Remember that if before declaring a VBA procedure פונקציע or סאַב keyword is not inserted, the default property is set for the procedure עפנטלעך (that is, it will be available everywhere in this VBA project). This is in contrast to variable declarations, which by default are פּריוואַט.

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

If you need to terminate the execution of a VBA procedure פונקציע or סאַב, without waiting for its natural ending, then for this there are operators אַרויסגאַנג פֿונקציע и אַרויסגאַנג סוב. The use of these operators is shown below using a simple procedure as an example. פונקציעA that 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 should be shown an error message and the procedure should exit 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 פונקציע - VAT_Amount, a built-in VBA function is inserted into the code MsgBox, which displays a warning popup to the user.