VBA: Sub vs Function

Learn more about VBA commands

What is Sub vs Function?

In this article, we will discuss the key differences between a sub vs function. Both are sets of commands that are used to perform specific tasks in Microsoft Excel’s Visual Basic Application (VBA).

 

Sub vs Function

 

A sub, also known as a subroutine or sub procedure, is a piece of code that is used to perform a specific task mentioned in the code but does not return any kind of value. 

On the other hand, a function, also known as a user-defined function procedure, is a piece of code that executes a specific task determined by the Excel user and returns a result. One of the applications of function procedures is to perform repetitive tasks.

 

VBA Sub vs Function: Key Differences

The key differences between a sub and a function are as follows:

 

SubFunction
A sub performs a task but does not return a value.A function returns a value of the tasks performed.
Subs can be recalled from anywhere in the program and in multiple types.Functions are called by a variable.
Subs cannot be used directly in spreadsheets as formulas.Functions are used directly in spreadsheets as formulas.
Users must insert a value in the desired cell before getting the result of the sub.Functions can be used to perform repetitive tasks and return a value.
Excel users can execute a VBA sub.

Excel users cannot execute VBA functions.

 

What is a VBA Sub?

A sub can be described as a small program within the VBA Editor that performs a specific action in Excel. It is used to break large pieces of code into smaller parts that can be easily managed.

The command is used to perform tasks that may involve updating a cell, performing a calculation, or importing a file into the Excel application. However, the outcome or results from the tasks are not returned to another sub.

 

How to Write a Sub-Procedure in Excel?

When writing a sub, the following rules should be followed:

  • The subroutine should not contain spaces.
  • The sub procedure should not start with a special character or number. Instead, use a letter or underscore.
  • The subroutine name should not be a keyword or reserved word in VBA. Examples of reserved words include Function, Subroutine, Privation, End, etc.

When writing the sub, we use the “Sub” keyword and a name as the procedure name to declare the sub. The sub procedure should be followed by the task to be performed, written in VBA language. The sub should close with the statement End Sub.

A sub should follow the following structure:

Sub [Procedure name] (Parameters)

[Tasks that need to be done]

End Sub.

 

Types of Sub Procedures in VBA

A sub procedure can take two forms, i.e., private and public. The modifiers “private” and “public” allows users to use the subs differently. The private sub procedure can only be used in the current module. The public sub allows users to use the procedure in all modules present in the workbook.

 

What is a VBA Function?

A VBA function is similar to a sub procedure, only that the former can return a value whereas the latter cannot. It is a piece of code that can be called anywhere in the VBA Editor and eliminates the need to write the same lines of code every time. VBA allows users to use built-in functions, as well as user-defined functions.

VBA functions work in the same way as formulas in Excel and can be used to perform repetitive tasks. Users can create custom functions for any actions and then access the functions from a cell or a direct reference from a cell.

For example, a user can use functions to create a program that calculates the monthly interest payable on a motor vehicle loan. The function will include fields that accept the total loan amount and payment duration. After inputting the required values, the program will return the monthly interest value payable on the motor vehicle.

 

How to Write a Custom Function?

Follow the steps below to create a custom function:

  1. Open MS Excel and press Alt+F11 to activate the VBA Editor.
  2. Choose the specific workbook in the Project.
  3. Go to the menu, click Insert then Module to insert a standard VBA module.
  4. In the module, enter the term Function, followed by a unique function name. If the function uses an argument, add the list of arguments in the parentheses.
  5. Insert the VBA code that performs the intended task. The program will store the value of the result in a variable, using the same name as the function.
  6. Close the function with the End Function.

 

Related Readings

CFI offers the Certified Banking & Credit Analyst (CBCA)™ certification program for those looking to take their careers to the next level. To keep learning and developing your knowledge base, please explore the additional relevant resources below:

  • VBA Cell References
  • Excel IF Statement
  • How to Debug Code
  • VBA in Excel

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now

to take your career to the next level and move up the ladder!