Microsoft Access Database: Types of VBA Procedures

There are essentially two main types of VBA procedures you can utilise in your Access database application:

1. Sub Procedures
2. Function Procedures

1. Sub Procedures – is the popular procedure and the one new VBA programmers learn to use. It’s the sub-routine which has a beginning and ending signature of:

It executes code line by line in the order it appears, carrying out a series of actions and/or calculations.

The ‘Arguments’ element is optional which can be explicit or implicit. This allows values and/or references to be passed into the calling procedure and handled as a variable.

Basic Access VBA procedure macros have no arguments (in between the parenthesis) and can called from other objects easily as opposed to argument.

If you create a procedure intended as a macro in Access, users must not specify any arguments being present as it restricts have they are called.

Calling another procedure in Access VBA can include the Call keyword followed by the name of the procedure with optional arguments.  If arguments are used, users must use parenthesis around the argument list.

A procedure can be prematurely terminated, placed before the ‘End Sub’ statement by using the ‘Exit Sub’ statement.

2. Function Procedure – The main difference between a Sub and Function procedure is that a Function procedure carries out a procedure and will return an answer whereas a Sub procedure carries out the procedure without an answer.

A simple analogy of a Function procedure compared to that of a Sub procedure could be illustrated using two example features of Excel:

• File, Save is an action and does not return the answer – Sub Procedure.
• The Sum function calculates the range(s) and returns the answer – Function Procedure.

The signature for this type of procedure is:

This type of procedure can be called in a module by a Sub procedure or executed as a user defined function call in a form or macro object in Access.

A procedure can be prematurely terminated, placed before the End Function statement by using the Exit Function statement. This acts and responds in the same way as described in the previous section (Sub Procedures).

Microsoft Access Database: VBA Procedures

There is actually one other type but not for beginners and was left out in this page.

Microsoft Access VBA Programming eBook This is only the beginning of a long and eventful journey into the world of Access VBA. To learn more and master the true power of Microsoft Access you may wish to invest in either a book, a course or even consider my eBook