A procedure is a group of statements called and executed as a unit to perform a task. Note: a broader concept in programming language is a function, however we will refer to procedures here to avoid confusion with a function procedure in VBA.

1. Designing procedures

When coding a procedure it is necessary to clearly know two things. First, what type of inputs and outputs must the function produce? This is called the signature of a procedure:

 Input DataType → Output DataType

Secondly, what is the purpose of the procedure?  Always write it in a one-line description that you can include as a comment in your code. A good programming practice is to define procedures that perform one specific task.

2. Sub and Function Procedures

In VBA there are two type of procedures: Subs (like sub-routines) and Functions.

Sub procedure: it does not return a value to the statement that called it, although it may perform other operations like modifying cells in a worksheet.

Sub SubName (arg)
statements
End Sub

To call a procedure within another procedure you just put the procedure name in a code line (with any necessary arguments), or you can use the Call statement:

Call SubName

Function procedure: this can return a value to the statement that called it, therefore you need to set the the output data type in the function declaration.

Function FunctionName (arg) as DataType
statements
FunctionName = value
End Function

To set the value that the function should return, you have to define a variable with the same name as the function within the function body.  To call the function you just set a variable with the same data type of the function.

y = FunctionName(x)

3. Passing Arguments to Procedures

You can pass values to a procedure for processing within the procedure. These values are called arguments.

Sub ProcedureName (ArgName as DataType)
Function FunctionName (ArgName as DataType) as DataType

Optional Arguments

You can create procedures with optional arguments using the Optional statement.

Optional ByVal ArgName as DataType = DefaultValue

You must also declare the default value and any arguments that follow an Optional argument must also be optional

Procedure Scope

• Public: whole application (default)
• Private: only module

Early termination

Exit Sub
Exit Function

 4. User Defined Math Functions

You can use Function procedures to create user-defined math functions, such as:

Log base n function

Function LogBaseN (x as Double, n as Integer) as Double
LogBaseN = Log(x) / Log(n)
End Function

Poisson function: you can code a function that returns random numbers with a Poisson distribution using Knuth’s algorithm.