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: