Excel VBA Tutorial Part 4 - VBA Functions & Subroutines

Home » Excel-VBA-Tutorial » VBA-Functions-And-Subroutines

Search this site:

Custom Search

Built-In VBA Functions

Excel VBA has a large number of built-in functions that you can use in your macros.

For a list of these:

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

In Excel Visual Basic, a set of commands to perform a specific task is placed into a procedure, which can be a function or a subroutine. The main difference between a VBA function and a VBA subroutine is that a function returns a result, whereas a subroutine 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, 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 subroutine.

Arguments

In VBA, both functions and subroutines can be passed data via arguments, which are declared in the VBA function or subroutine definition. For example, we might have a VBA subroutine that adds an Integer to every cell in the current range. You could supply the value of the integer to the subroutine via an argument, as follows:

  Sub AddToCells(i As Integer)
        .
        .
        .
  End Sub

Optional Arguments

You can also define VBA functions or subroutines to have Optional arguments. These are arguments that the user can supply if they want, but if they are omitted, the procedure will still work. To return to the example above, if we wanted to make the supplied integer optional, this would be declared as follows:

Sub AddToCells(Optional i As Integer)

Of course this needs to be considered in the subroutine code. We need to tell the subroutine to check if the argument i has been supplied, before attempting to use it. You can check if an argument has been supplied to a procedure, using the IsMissing command, as follows:

  Sub AddToCells(Optional i As Integer)

      If IsMissing(i) Then
          .
          .
          .
      Else
          .
          .
          .
      End If

  End Sub

VBA allows you to use multiple Optional arguments in a procedure, as long as the Optional arguments are positioned at the end of the argument list.

Passing Arguments By Value and By Reference

When arguments are passed to VBA functions, they can be passed in two ways:

ByVal - The argument is passed by Value. This means that just the value (ie. a copy of the argument) is passed to the procedure and therefore, any changes that are made to the argument inside the procedure will be lost when the procedure is exited.
ByRef - The argument is passed by Reference. This means that the actual address of the argument is passed to the procedure. Any changes that are made to the argument inside the procedure will be remembered when the procedure is exited.

We can specify whether an argument is passed to a VBA function 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 we exit the subroutine.
Sub AddToCells(ByRef i As Integer) . . . End Sub	In this case, the integer i is passed by Reference. When we exit the subroutine, any changes that have been made to i will be remembered by the variable that was passed into the subroutine.

It should be noted that, by default, in VBA, arguments are passed by Reference, so if you do not use the ByVal or the ByRef keyword, the arguments will be passed by Reference.

Before discussing further properties of VBA Functions and Subroutines, it is useful to look at the two types of procedure individually. The following two sections provide a brief discussion of VBA Functions and VBA Subroutines, along with simple examples :

VBA Functions

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

Function
    .
    .
    .
End Function

As previously mentioned, VBA functions (unlike subroutines) return a value. The return values have the following rules:

The data type of the returned value must be declared in the function header.
The value to be returned must be assigned to a variable having the same name of the function. This variable does not need to be declared, as it already exists as a part of the function.
The value returned from the function is the last value to be assigned to this variable before the function is exited.

This is best illustrated by the example below.

VBA Function Example: Perform Mathematical Operations on 3 Numbers

The following code shows an example of a simple VBA function 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 illustrates the way in which data arguments are supplied to a function, and the way the 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 result is stored in a variable that has the same name as the function.

Calling VBA Functions

If the above simple function is typed into a Module in the Visual Basic Editor, it will then be available to be used in the worksheets of your Excel workbook, in the same way as Excel's Built-In Functions. Therefore if cells A1, A2 and A3 contain numeric values, you could call the SumMinus function by typing the following into any cell:

=SumMinus(A1, A2, A3)

VBA Subroutines

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

Sub
    .
    .
    .
End Sub

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

The following code shows an example of a simple VBA subroutine 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)

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter

If Not IsMissing(iFontSize) Then Selection.Font.Size = iFontSize

End Sub

The above example illustrates how subroutines perform actions but do not return values. This example also includes the Optional argument, iFontSize. If iFontSize is not supplied to the subroutine, then the current range's font size is not changed. However, if iFontSize is supplied to the function, then the current range is set to have the supplied font size.

VBA Subroutine 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 subroutine that does not receive any arguments :

Sub Format_Centered_And_Sized()

Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter

Selection.Font.Bold = True

End Sub

Calling Excel VBA Subroutines

Subroutines cannot be typed directly into a cell in Excel, in the same way that VBA functions can, because subroutines don't return a value. However, provided they have no arguments (and are Public - see below), Excel VBA subroutines are available to the user of a spreadsheet. Therefore, if the above simple subroutines are typed into a Module in the Visual Basic Editor, Example 2 will then be available to be used in the worksheets of your Excel workbook but Example 1 will not (as it has an argument).

For those subroutines that are accessible from the workbook, a simple way to run (or execute) the subroutine is :

Press ALT-F8 (ie. press the ALT key and while this is pressed down, press F8)
From the list of macros that appear, select the macro you wish to run
Click Run

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

Press ALT-F8 (ie. press the ALT key and while this is pressed down, press F8)
From the list of macros that appear, select the macro you wish to assign a key combination to
Click Options..., and in the box that appears, type in a key combination
Click OK and then shut down the macro window

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 Functions & Subroutines

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 Functions and Subroutines :

Public Sub AddToCells(i As Integer) . . . End Sub	If a function or subroutine 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 function or subroutine 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 subroutine 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 Functions & Subroutines

If you want to exit a VBA function or subroutine 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 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 function immediately:

Function VAT_Amount(sVAT_Rate As Single) As Single

    If sVAT_Rate <= 0 Then
      MsgBox "Expected a Positive value of sVAT_Rate but Received " & sVAT_Rate
      Exit Function

    End If
     .
     .
     .
End Function

Go To Excel VBA Tutorial Part 5 - VBA Conditional Statements

Return to the Excel VBA Tutorial Page

Information Functions

Date & Time Functions

Lookup & Reference Functions