VBA Call Function from a Sub

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on July 19, 2021

This tutorial will teach you how call a function from a sub procedure in VBA.

When you create a function in VBA, you can either use the function as a UDF (User Defined Function) in your Excel Workbook, or you can call it from a Sub Procedure.

Calling a function from a Sub Procedure

Once you create a function, you can call it from anywhere else in your code by using a Sub Procedure to call the function.

Consider the function below:

Function GetValue() As Integer
   GetValue = 50
End Function

Because functions return a value, in order to call the function, we must pass the value to something. In this case we will pass the value to a variable:

Sub TestValues()
   Dim intVal As Integer

   'run the GetValue function
   intVal = GetValue()
   'and show the value
   MsgBox  intVal
End Sub

Calling a Function with Parameters

If the function has parameters, you will need to pass these parameters from the Sub Procedure to the Function in order to get the correct value back.

Function GetValue(intA as Integer) As Integer 
   GetValue = intA * 5
End Function

To call this function, we can run the following Sub Procedure.

Sub TestValues() 
  MsgBox GetValue(10)
End Sub

This Sub Procedure would send the value of 10 to the function, which would in turn multiply that value by 5 and return the value to 50 to the Sub Procedure.




VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as

Learn More!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples