VBA user-defined functions and subroutines

In the Visual Basic environment that is part of Excel, a set of commands for performing specific tasks is called a procedure, which is divided into two types: a function (Function) and a subroutine (Sub).

The main difference between these concepts is that the first returns a result, while the second does not.

In addition, if you need to perform an action that returns a result (for example, summing up a group of numbers), you will use a function, but if you just need to perform a sequence of actions, a subroutine will be used.

Arguments

Procedures can receive values ​​through the arguments specified when they are declared. For example, you need to create a subroutine that adds an integer value to each cell in a selected document range. You can supply an integer value via an argument, as in this example:

Sub AddToCells(i As Integer)

      .

      .

      .

End Sub

Note: Procedures do not have to contain arguments. Sometimes there is no need for them. 

Optional Arguments

Also, a number of VBA procedures may have optional arguments that the user can use if necessary, but they can also be skipped. In the latter case, the procedure uses the default values.

Let’s return to the described example. If we want to declare an argument as optional, we do it like this:

Sub AddToCells(Optional i As Integer = 0)

Then the argument i will default to zero. The function can contain several optional arguments. When enumerated, they must be inserted at the end of the list.

Two Ways to Pass Arguments

Arguments can be passed to functions in several ways:

1. By value. This type is called ByVal. In this case, the value (that is, a copy of the argument) is passed to the procedure, and therefore any changes that are made inside the procedure’s argument will be lost when the procedure ends.
2. Link. ByRef. In this case, the address of the argument in memory is passed to the procedure. Then any changes are saved after its completion.

You can specify whether an argument is passed to a procedure by value or by address in memory by using the appropriate keyword (ByVal or ByRef) at the procedure definition stage.

Example:

By default, arguments are passed by address. Therefore, if you do not use the keyword, the arguments will be passed based on the address in memory and will be saved after the subroutine ends.

Before discussing other properties of VBA functions and subroutines, it is necessary to look at the two types of procedures again so that it is easier to understand the material that follows. Next, we will look at each of these functions in more detail.

VBA Functions

To define a function in VBA, you need to write the following statements:

Function

.

.

.

End Function

As stated earlier, VBA functions return a value. This process is carried out according to the following rules:

1. The data type of the return value must be defined in the header of the function.
2. The value that is returned must be assigned to a variable that has the same name as the function. This variable does not need to be defined, as it is already part of the function.

All this can be shown in the example below.

Procedure “Function”: Performing an operation on three numeric values

Here’s an example of a simple function that takes three arguments, each of which is a double-precision floating point value (of type Double). As a result of calculations, the function returns another value of the same type, which is the sum of the first two arguments, from which the third one is subtracted.

Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double

SumMinus = dNum1 + dNum2 — dNum3

End Function

This is an elementary procedure of type “Function”, which shows the way in which information can be passed to the procedure. You can also see that the function returns a type that is defined as “Double” (such a command is given to the interpreter using the keywords As Double after giving all the arguments). This example also demonstrates how the result returned by this kind of procedure is stored in a variable that has the same name as the procedure itself.

Function call

If the function example above is integrated into a module in the editor, it can be executed from other functions or directly from a sheet in the document.

Calling a function from another function

A function can also be executed from another procedure by assigning a variable to it. This example demonstrates calling the previously defined SumMinus function.

Sub main()

   Dim total as Double

   total = SumMinus(5, 4, 3)

End Sub

Call from sheet

You can also call the procedure directly from the sheet in a similar way to the built-in ones. For example, like this:

=SumMinus(10, 5, 2)

VBA Subroutines

The macro editor recognizes a subroutine by statements placed at the beginning and end of the procedure.

Sub

.

.

.

End Sub

Subroutine example 1: Setting the alignment and modifying the font in the given range

Let’s analyze a simple example of an Excel subroutine that will change the formatting of a certain range: it sets the center alignment and the user-defined size of letters and numbers in it. 

Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)

   Selection.HorizontalAlignment = xlCenter

   Selection.VerticalAlignment = xlCenter

   Selection.Font.Size = iFontSize

End Sub

Because it is a subroutine, it does not return a result. Its task is to perform a certain sequence of actions.

It also uses an optional argument FontSize. If it is not passed to the subroutine, it automatically becomes 10. But if this argument is specified, then in the corresponding range the font will become the same size as defined by the user.

Example 2: Aligning and Applying Style

This subroutine is somewhat similar to the one just given, but in this situation, instead of modifying the font size, we will make the text bold. Here, no values ​​are passed to the subroutine as arguments.

Calling a VBA Subroutine

Calling a subroutine through VBA

Calling the Sub procedure is possible through VBA using the keyword “Call”, after which the name of the subroutine is immediately written. Here is an example that demonstrates how this works in practice:

Sub main()

Call Format_Centered_And_Sized( 20 )

End Sub

If the example subroutine has more than one argument, they can be separated by commas. For example.

Sub main()

Call Format_Centered_And_Sized( arg1, arg2, … )

End Sub

Calling a subroutine from a document

Subroutines cannot be executed directly from an Excel document in the same way as functions because the “Sub” procedure does not produce a result. Despite this, if it is public, users of the document can use it. At the same time, it is important that it does not have arguments. This means that if you paste the above examples into a Visual Basic environment, the Format_Centered_And_Bold subroutine can be used by the user.

Subroutines that can run in a document are called like this:

1. You need to open the Macros dialog box by pressing Alt+F8. To do this, first press the first button, then hold it and press the second. All other combinations work the same.
2. It contains a list of user-created macros. It is necessary to choose the appropriate one for the current task.
3. Press the “Execute” button.

To simplify and speed up the process of executing a subroutine at the right time, you can bind it to a specific key combination. To achieve this task, the following steps are taken:

1. By pressing the Alt + F8 combination, you need to open the macro dialog box and select the macro that you want to associate with hot keys.
2. Click “Options” and enter the combination that you want to use to start the procedure. 
3. Click “OK” and close the window.

It is important to note that when assigning a hot key combination to a subroutine, you must make sure that the selected combination is not reserved for standard Excel functions. For example, you cannot use the Ctrl+V combination, as it is intended to paste information from the clipboard.

Assigning rights to procedures

Function rights are set using the words “Public” and “Private”. To make it clearer, here is a table with examples:

If the keyword is not specified in a function or subroutine declaration, the default permissions will be set to public. That is, it will be possible to access it from any project. This is the main difference between functions and subroutines and variables, which are always declared Private by default.

early exit

If you need to exit a function or subroutine before stopping it (for example, if an error is received), you can use the statements Exit Function or Exit Sub depending on the type of procedure to be terminated prematurely. Below we give an example of a procedure that waits for a number greater than zero to be passed to it. If suddenly it gets a negative value, the function stops its work and returns an error.

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

It is important to note that before exiting the procedure, the above code uses the built-in function in VBA MsgBox, which displays the dialog box.

It can take the following arguments:

1. Prompt. The text that is displayed in the dialog box. Required argument. All others are optional.
2. Buttons. An argument that specifies the number of buttons that will be displayed by the dialog box. Can take multiple values. For example, if it contains the value vbOKCancel, the dialog will only have two buttons: OK and Cancel. But in our case, the best value is vbYesNo.
3. Title Specifies the title of the dialog box.
4. HelpFile. Allows you to attach a help file to a window.
5. Context. Help item ID. Required argument if HelpFile is used.

After the user has pressed a certain button, the function returns a certain value. Therefore, the above code can be modified depending on the tasks. For example, ask the user if he would like to change the value of the sVAT_Rate variable, and if the user clicks “Yes”, then make it positive. First you need to register a variable.

Here you can show your imagination. But in general, using a dialog box in macros is a completely different topic, which in this case also requires the ability to use conditional statements to perform actions. The main thing is that you can define functions and know their types and the main differences between them. Everything else will come with practice and the desire to keep learning. Good luck.