Function and Sub in Excel VBA

In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-procedure generally does not return a result whereas functions tend to return a result. Hence if there is a need for having a value post execution of tasks, then place the VBA code under a function or otherwise place the code under a sub-procedure. In Excel, there is the availability of large numbers of VBA functions that could be utilized in the development of new VBA codes. Such functions are referred to as Built-in functions. With the increase in the size of a VBA program, both Functions and Sub-procedures play a crucial role in the management and performance of VBA code.

Functions in VBA

A function in VBA can be defined as a procedure that executes a piece of code or instructions and post-execution, it returns the value of the tasks performed. A function is hence invoked using a variable. Functions are directly called in the spreadsheets by using excel based formulas. An excel VBA function is not of executable nature. They help in performing a set of repetitive tasks. Following is the syntax for the VBA function: –

Function <Function name>  (Parameters) as variable type

Piece of codes

End function.

Here is a small example of the VBA function: 

Function CalcArea(a As Double, b As Double) As Double

CalcArea = a * b

End Function

In an excel spreadsheet, place the formula in range A1 as shown below:

The following would be the output in range A1 of the excel spreadsheet as shown below: 

The above code is a very simple example of how to program or develop custom functions. A VBA programmer can develop as many custom functions’ basis the need of the program. He can insert a new module and start developing a new function just by naming it by a new unique function name.

Sub in VBA

A sub-procedure in VBA can be defined as a procedure that executes a piece of code or instructions, but post-execution does not return the value of the tasks performed. A sub-procedure, therefore, does not require a variable for getting invoked. An excel VBA sub-routine or a sub-procedure is of executable nature and can be assigned as a macro to any excel based object. Like functions, they help in performing a set of repetitive tasks. Following is the syntax for the VBA sub-procedure: 

Sub  <sub name>  (Parameters) 

Piece of codes

End sub

Here is a small example of a VBA sub-routine: 

sub CalcArea() 

Dim a As Double, b As Double

c = a * b

Sheet3.activate

Sheet3.range(“A1”).value=c

End sub

In an excel module, select run sub/user form present under the Run option:

The following would be the output in range A1 of the excel spread sheet as shown below:

The above code is like functions but the only difference is that in this program, the values are hard coded in the sub-procedure itself whereas, in functions, the values are to be passed by the end user. The following things should be followed when creating a sub-routine: 

• It should not have any spaces.
• It should not begin with any number or special character. It can however begin with an underscore or a simple letter.
• The name of the sub-procedure should not be the same as that of the reserved keywords present in the excel VBA.

To Summarize, the following are the differences between a function and a sub in VBA: 

Function In VBA	Sub In VBA
Functions always return a value after it completes their required set of instructions.	Sub does not return a value after it completes its required set of instructions.
Functions do not have any alternate nomenclature in VBA	In VBA, Subs are referred to as subprocedures as well as subroutines
To Execute Function, it is to be passed on to the excel sheet beginning with equal to sign. In Short, they are invoked as excel based formulas.	To execute a sub in VBA, it can be run or could be executed through Project explorer or by assigning it as a macro on excel objects.