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.

When recording a macro, no arguments are used and the parenthesis for the named procedure remains empty.

If you create a procedure intended as a macro in Excel, users must not specify any arguments.

Sub procedures can be recursive meaning that branching to another procedure is permitted which then returns back to the main calling procedure.

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

Example of the CALL statement

‘Procedure to be called with a single
argument explicitly ‘declared as a string

Sub MyMessage(strText As String)

MsgBox strText

End Sub

(Click here for an understanding of the MsgBox statement)

Correct

‘Test the calling procedure

Sub TestMessage()

Call MyMessage("It worked!")

End Sub

Incorrect - must use the parenthesis

‘Test the calling procedure

Sub TestMessage()

Call MyMessage "Did it work?"

End Sub

Correct (alternative) - No Call keyword used & no parenthesis therefore required.

‘Test the calling procedure

Sub TestMessage()

MyMessage "It worked!"

End Sub

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

‘This procedure will terminate after part A and never run part B.

Sub TerminateNow()

Code part A here...

Exit Sub

Code part B here....

End Sub

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:

Function NameOfProdedure([Arguments]) [As Type]

Code is executed here

NameOfProcedure = Answer of the above code executed

End Function

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.

The optional Type attribute can be used to make the function explicit. Without a type declared, the function is implicit (As Variant).

The last line before the End Function signature uses the name of the procedure to return the expression (or answer) of the function.

Users cannot define a function inside another function, sub or even property procedures.

This type of procedure can be called in a module by a Sub procedure or executed as a user defined function on a worksheet in Excel.

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

An example of a Function procedure:

‘This function calculates the distance of miles into kilometres.

Function ConvertToKm(dblMiles As Double) As Double

ConvertToKm = dblMiles * 1.6

End Function

A Sub procedure that uses of the above function:

‘Using the above function that must use parenthesis.

Sub CarDistance

MsgBox ConvertToKm(25)

End Sub

In Excel, this function can also be used (known as a User Defined Function - UDF)

Click on this link for more information on user defined functions.

Next Topic: Visual Basic editor (VBE)

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: MsgBox, Call, Exit Sub, Exit Function.