Project Building Blocks: Excel VBA Functions and Subroutines

Basic Structure|Subroutines|Dim|Functions

Functions and subroutines help keep the overall structure of VBA code compressed and easier to maintain. Both can reduce lines of code by consolidating repetitive code snippets. The main difference between the two is that functions return a single result, while subroutines can return many results. Functions may call other functions or subroutines, and subroutines may also call other functions or subroutines. Guidelines below are for the simplest versions of constructing and using them, and provide utility in a wide range of needs, especially for the beginner.

Basic Structure

All VBA subroutines and functions should be given unique names within the same workbook. If multiple subroutines residing in the same module are given the same name, VBA will not execute any code for that workbook. Although same names are permitted if the subroutines are in different modules, this can cause confusion for the devloper and user alike. More on this later.

Syntax: Subroutine

Sub Name()

Code statements

End Sub

Sub Name(Var1, Var2, ...)

Code statements

End Sub

Syntax: Functions

Function Name()

Code statements

Name = (result to return)

End Function

Function Name(Var1, Var2)

Code Statements

Name = (result to return)

End Function

In the first case, no values are passed to the function. In the second case, values are passed via the variables Var1 and Var2. One or many variables can be passed to the routine. After the statement Name = (result to return) control is returned to the calling routine and any code below that line will be ignored.

Subroutines

A subroutine is a block of instructions (code) that returns one or more results to the routine that called it or to specified cells in the active Excel spreadsheet (more on that later).

Passing values to a subroutine

A subroutine named add2numbers has two values passed to it, X and Y, and returns two values of variables Z and Zsq to a calling routine named Calladd2numbers. Z and Zsq must be variables defined in the calling routine.

Sub add2numbers(X, Y, Z, Zsq)

Z = X + Y

Zsq = Z*Z

End Sub

The necessary line of code in the calling routine Calladd2numbers is: Call add2numbers(X,Y,Z,Zsq).

Sub Calladd2numbers()
Dim X, Y, Z, Zsq as double

X = 2.0

Y = 3.0

Z = 0.0

Zsq = 0.0

Call add2numbers(X,Y,Z,Zsq)

End Sub

Before add2numbers is called, X is set to 2.0 and Y to 3.0, both Z and Zsq are set to 0. After the subroutine is called, Z has the value 5 and Zsq the value 25.

Declaring Variables: Dim statement

Notice in the second subrountine example that a Dim statement was added. This statement lets the routine know the type of variable each is. While VBA will (in many cases) interpret this characteristic if left undefined, it is good practice to include it, leaving no chance for misinterpretation in case of coding mistakes or vagueness. See Variables for more on this topic.

Functions

A functionis a block of instructions (code) that returns one result to the routine that called it.

How to create and use a simple function

The function name is add2numbers. Two values are passed to it, X and Y. This function will add the two values passed to it, and return the result to the calling routine.

Function add2numbers(X,Y)

add2numbers = X + Y

End Function

The necessary line of code in the routine that called it is Z = add2numbers(X,Y).

Sub Calladd2numbers()
Dim X, Y, Z, Zsq as double

X = 2.0

Y = 3.0

Z = add2numbers(X,Y)

End Sub

Inside the calling routine, values 3 and 2 are set for the two variables used in the calling statement, and the variable Z now has the sum of those two variables (5).