“Function” and “Sub” Procedures in VBA

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 Alt + F11), and then press 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 (Function) or Sub (Subroutine). The main difference between the procedures Function и Sub is that the procedure Function returns result, procedure Sub – not.

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 argument list is specified when declaring a procedure. For example, the procedure Sub 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 Function и 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.

Returning to the previous example, to make an integer argument to a function optional, it would be declared 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, 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:

• 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 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 ByVal 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 ByVal or ByRef, then the argument will be passed by reference.

Before proceeding with the procedures Function и 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 are brief discussions of VBA procedures Function и Sub and simple examples are shown.

VBA procedure «Function»

The VBA editor recognizes the procedure Functionwhen it encounters a group of commands enclosed between the following opening and closing statements:

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 header of the procedure 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, as it always exists as an integral part of the procedure. Function.

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 Function, which takes three arguments of type Double (double-precision floating-point numbers). As a result, the procedure returns another number of type Doubleequal 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 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 As Double after the list of arguments). This example also shows how the result of the procedure Function stored in a variable with the same name as the procedure name.

Calling the VBA procedure “Function”

If the above simple procedure 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.

Call VBA procedure “Function” from another procedure

Procedure Function 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 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 it Subwhen 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 Sub, 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

This procedure Sub performs actions but does not return a result.

This example also uses an Optional argument FontSize. If the argument FontSize not passed to procedure Sub, then its default value is 10. However, if the argument FontSize passed to procedure Sub, 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 Sub, 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 Sub from another VBA procedure, you need to write the keyword Call, procedure name Sub 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

Procedure Sub cannot be entered directly into an Excel sheet cell, as can be done with a procedure Functionbecause the procedure Sub does not return a value. However, procedures Sub, which have no arguments and are declared as Public (as shown below) 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, 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 Sub, accessible from the worksheet:

• Press 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.
• Press Run (run)

To perform a procedure Sub quickly and easily, you can assign a keyboard shortcut to it. For this:

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

Attention: 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. Public и Private. 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 Function or Sub keyword is not inserted, the default property is set for the procedure Public (that is, it will be available everywhere in this VBA project). This is in contrast to variable declarations, 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 for this there are operators Exit Function и Exit Sub. The use of these operators is shown below using a simple procedure as an example. FunctionA 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 Function - VAT_Amount, a built-in VBA function is inserted into the code MsgBox, which displays a warning popup to the user.