Before creating your own VBA Functions, it is useful to know that Excel VBA already has a large number of built-in functions that you can use in your code.
You can view a list of these from the VBA Editor:
In Excel Visual Basic, a set of commands to perform a specific task is placed into a procedure, which can be a Function procedure or a Sub procedure (also known as functions and subroutines).
The main difference between a VBA Function procedure and a Sub procedure is that a Function procedure returns a result, whereas a Sub procedure does not.
Therefore, if you wish to perform a task that returns a result (e.g. summing of a group of numbers), you will generally use a Function procedure, but if you just need a set of actions to be carried out (e.g. formatting a set of cells), you might choose to use a Sub procedure.
VBA procedures can be passed data via arguments, which are declared in the procedure definition. For example, you might have a VBA Sub procedure that adds an Integer to every cell in the current range. You could supply the value of the integer to the Sub via an argument, as follows:
|Sub AddToCells(i As Integer)|
Note that it is not essential for VBA Functions or Subs to have arguments. Some procedures may not require them.
You can also define VBA procedures to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the procedure will assign a default value to them.
To return to the example above, if we wanted to make the supplied integer argument optional, this would be declared as follows:
You can use multiple Optional arguments in a VBA procedure, as long the Optional arguments are all positioned at the end of the argument list.
When arguments are passed to VBA procedures, they can be passed in two ways:
You can specify whether an argument is passed to a VBA procedure by value or by reference by using the ByVal or the ByRef keyword when defining the procedure. This is shown below:
|Sub AddToCells(ByVal i As Integer)|
|In this case, the integer i is passed by Value. Any changes that are made to i will be lost when you exit the Sub procedure.|
|Sub AddToCells(ByRef i As Integer)|
|In this case, the integer i is passed by Reference. When you exit the Sub, any changes that have been made to i will be remembered by the variable that was passed into the Sub procedure.|
It should be noted that by default, in VBA, arguments are passed by Reference. Therefore, if you do not use the ByVal or the ByRef keyword, the arguments will be passed by Reference.
Before discussing further properties of VBA Function and Sub procedures, it is useful to look at the two types of procedure individually. The following two sections provide a brief discussion of VBA Function and VBA Sub procedures, along with simple examples.
The VBA editor recognises a Function procedure, because the commands are positioned between the following start and end commands:
As previously mentioned, VBA function procedures (unlike sub procedures) return a value. The return values have the following rules:
This is illustrated in the following example.
The following code shows an example of a simple VBA Function procedure that receives three arguments, each of which are 'Doubles' (double precision floating point numbers). The Function returns a further 'Double', which is 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 - dNum3End Function
The above very simple VBA Function procedure illustrates the way in which data arguments are supplied to a procedure. It is also seen that the Function procedure return type is defined as being a 'Double' (i.e. by the term "As Double" which is included after the Function arguments).
The above example also shows how the Function procedure result is stored in a variable that has the same name as the Function.
If the above simple Function procedure is typed into a Module in the Visual Basic Editor, it will then be available to be called from other VBA procedures or to be used in the worksheets of your Excel workbook.
You can call a Function procedure from within another VBA procedure by simply assigning the Function to a variable. The following example shows a call to the simple SumMinus function that was defined above:
Dim total as DoubleEnd Sub
total = SumMinus(5, 4, 3)
You can call VBA Function procedures from an Excel Worksheet, in the same way as you can call any of the built-in Excel functions.
Therefore, you could call the SumMinus Function procedure by typing the following into any cell of your worksheet:
The VBA editor recognises a Sub procedure, because the commands are positioned between the following start and end commands:
The following code shows an example of a simple VBA Sub procedure that applies formatting to the current selected cell range. The cells are formatted to be aligned centrally (both horizontally and vertically) and to have a user-supplied font size:
Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)
Selection.HorizontalAlignment = xlCenterEnd Sub
Selection.VerticalAlignment = xlCenter
Selection.Font.Size = iFontSize
The above example illustrates how Sub procedures perform actions but do not return values.
This example also includes the Optional argument, iFontSize. If iFontSize is not supplied to the Sub, then the default font size of 10 is used. However, if iFontSize is supplied to the Sub, then the current range is set to have the user-supplied font size.
The following code is similar to example 1, but instead of supplying a font size to the selected range, the cells are set to have a bold font. This example has been included to show a Sub procedure that does not receive any arguments :
Selection.HorizontalAlignment = xlCenterEnd Sub
Selection.VerticalAlignment = xlCenter
Selection.Font.Bold = True
You can call a VBA Sub procedure from another VBA procedure by typing the Call keyword, followed by the Sub name and then the Sub procedure arguments enclosed in brackets. This is shown in the example below:
Call Format_Centered_And_Sized( 20 )End Sub
If the Format_Centered_And_Sized Sub procedure had more than one argument, these would be separated by commas. E.g.
Call Format_Centered_And_Sized( arg1, arg2, ... )End Sub
Sub procedures cannot be typed directly into a Worksheet in Excel, in the same way that VBA Function procedures can, because Sub procedures don't return a value. However, provided they have no arguments (and are Public - see below), Excel VBA Sub procedures are available to the user of a spreadsheet. Therefore, if the above simple Sub procedures are typed into a Module in the Visual Basic Editor, the Format_Centered_And_Bold Sub procedure will be available to be used in the worksheets of your Excel workbook but the Format_Centered_And_Sized will not (as it has an argument).
For those Sub procedures that are accessible from the workbook, a simple way to run (or execute) the Sub is:
You can assign a key combination to your Sub procedure, to enable quick and easy execution of the code. To do this:
In Part 2 of this tutorial, we discussed the scope of variables and constants and the role of the Public and Private keywords. These keywords have the same meaning when applied to VBA procedures :
|Public Sub AddToCells(i As Integer)|
|If a procedure declaration is preceded by the keyword Public, this makes the procedure accessible to all other modules in the VBA Project.|
|Private Sub AddToCells(i As Integer)|
|If a procedure declaration is preceded by the keyword Private, this makes the procedure only available to the current module. It cannot be accessed from any other modules, or from the Excel workbook.|
If no keyword is inserted at the start of a VBA Function or Sub declaration, then the default setting is for the procedure to be Public (i.e. to be accessible from anywhere in the VBA Project). This is different to variable declarations, which are Private by default.
If you want to exit a VBA Function or Sub procedure before it has run to the end, you can do this using the Exit Function or the Exit Sub command. This is illustrated below, in a simple Function procedure that expects to receive a positive value to work with. If the value received is not positive, the function cannot continue, so it highlights the error to the user and exits the procedure immediately:
Function VAT_Amount(sVAT_Rate As Single) As Single
VAT_Amount = 0End Function
If sVAT_Rate <= 0 Then
MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate
Note that, before exiting the VAT_Amount Function procedure, the above code uses the built-in VBA MsgBox function, to display a box containing a warning message to the user.