Excel VBA Tutorial Part 4 - VBA Function & Sub Procedures

Built-In VBA Functions

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:

  • From an Excel Workbook, open up the VBA Editor (by pressing ALT-F11), and then press F2
  • Select VBA in the drop-down list at the top left of the screen.
  • The list that appears shows the VBA built in classes, members and functions. If you click on a function name a brief description appears at the bottom of the window; Pressing F1 brings up the online help for that function.

Alternatively, a list of built-in VBA functions, with examples, can be found at the Visual Basic Developer Center website or the Tech on the Net website


User-Defined VBA Function & Sub Procedures

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 (eg. 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 (eg. formatting a set of cells), you might choose to use a Sub procedure.


Arguments

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)
      .
      .
      .
End Sub

Note that it is not essential for VBA Functions or Subs to have arguments. Some procedures may not require them.


Optional Arguments

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:

Sub AddToCells(Optional i As Integer = 0)

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.


Passing Arguments By Value and By Reference

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, 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.


VBA Function Procedures

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 Subs) return a value. The return values have the following rules:

This is best illustrated by the example below.


VBA Function Procedure Example: Perform a Mathematical Operation on 3 Numbers

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 - 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' (ie. 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.


Calling VBA Function Procedures

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.

Calling a VBA Function Procedure From Another Procedure

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:

Sub main()

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

End Sub

Calling a VBA Function Procedure From A Worksheet

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:

=SumMinus(10, 5, 2)


VBA Sub Procedures

The VBA editor recognises a Sub procedure, because the commands are positioned between the following start and end commands:

Sub
    .
    .
    .
End Sub


VBA Sub Procedure Example 1: Center and Apply Font Size to a Selected Range of Cells

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 = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Font.Size = iFontSize

End Sub

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.


VBA Sub Procedure Example 2: Center and Apply Bold Font to a Selected Range of Cells

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 Format_Centered_And_Bold()

    Selection.HorizontalAlignment = xlCenter
    Selection.VerticalAlignment = xlCenter
    Selection.Font.Bold = True

End Sub


Calling Excel VBA Sub Procedures

Calling a VBA Sub Procedure From Another Procedure

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:

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

Calling a VBA Sub Procedure From A Worksheet

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:

Alternatively, you can assign a key combination to your Sub procedure, to enable quick and easy execution of the code. To do this:

WARNING: When assigning a key combination to a macro, take care not to select one of Excel's predefined key combinations (eg. 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.


Scope of VBA Procedures

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.

It should be noted that if no keyword is inserted at the start of a VBA Function or Sub declaration, then the default is for the procedure to be Public (ie. to be accessible from anywhere in the VBA Project). This is different to variable declarations, which are Private by default.


Early Exit From VBA Function & Sub Procedures

If you want to exit a VBA Function or Sub procedure before its natural end point, 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
    If sVAT_Rate <= 0 Then
      MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate
      Exit Function
    End If
     .
     .
     .
End Function

Note that, before exiting the VAT_Amount Function procedure, the above code uses the built-in VBA MsgBox function, to pop up a box containing a warning message to the user.