Before you consider creating your own VBA Functions, it is useful to know that there are a large number of built-in VBA functions that you can use in your code.
For a list of the main built-in VBA functions, with examples, see the VBA Functions page.
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 could have a VBA Sub procedure that adds an Integer to every cell in the current selected range on a worksheet. You could supply the value of the integer to the Sub via an argument, as follows:
|
Sub AddToCells(i As Integer) . . . End Sub |
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:
In this case, the supplied integer, i, has a default value of 0.
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) . . . End Sub |
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) . . . End Sub |
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, 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:
|
Function
.
End Function
. . |
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 to Add Two Numbers and Then Subtract a Third Number
Function SumMinus(dNum1 As Double, dNum2 As Double, dNum3 As Double) As Double
SumMinus = dNum1 + dNum2 - dNum3
End 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 your VBA program by simply assigning the Function to a variable. The following example shows a call to the simple SumMinus function that was defined above:
|
Sub main()
Dim total as Double
End 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:
|
Sub
.
End Sub
. . |
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 Procedure to Center and Apply a Supplied Font Size to the Selected Range
Sub Format_Centered_And_Sized(Optional iFontSize As Integer = 10)
Selection.HorizontalAlignment = xlCenter
End 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:
|
' Sub Procedure to Center and Bold the Selected Range
Sub Format_Centered_And_Bold()
Selection.HorizontalAlignment = xlCenter
End Sub
Selection.VerticalAlignment = xlCenter Selection.Font.Bold = True |
You can call a VBA Sub procedure from your VBA program 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:
|
Sub main()
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.
|
Sub main()
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 still available to the user of a spreadsheet. Therefore, of the above simple Sub procedures, the Format_Centered_And_Bold Sub procedure would be available to be used in the worksheets of your Exce workbook but the Format_Centered_And_Sized would 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:
Open up the 'Macro' dialog box by either:
You can assign a key combination to your Sub procedure, to enable quick and easy execution of the code. To do this:
Open up the 'Macro' dialog box by either:
WARNING: When assigning a key combination to a macro, take care not to select one of Excel's predefined key combinations (e.g. CTRL-C). If you do select an existing Excel key combination, this will be overwritten by your macro, and you, or other users, may end up accidentally executing your macro code.
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) . . . End Sub |
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) . . . End Sub |
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 differs from 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 = 0
End Function
If sVAT_Rate <= 0 Then MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate Exit Function End If . . . |
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.