The Use of Function in VBA

The Use of Function in VBA

Glen Alfaro Apr-18, 2022 Dec-20, 2021 VBA VBA Function
  1. Using non-object Function Type in VBA
  2. Using object Function Type in VBA
  3. Using of Exit Function Command for a Forced Return to the Subroutine

This article will demonstrate how to create and utilize a Function when dealing with VBA.

Functions are used to return a result where a subroutine cannot. When the program gets longer and more complex, functions are a useful tool for easier code block organization, debugging, and revising the script.

Functions can be categorized into two subsets:

non-object-type function that will return strings, integers, boolean, etc
object-type function what will return object like ranges, arraylists, etc.

Syntax:

[Public | Private] [ Static ] Function name [ ( argumentlist ) ] [ As retType ]
[ statements ]
[ name = expression ]
[ Exit Function ]
End Function

Definition:

Public Optional. indicates that other procedures in all modules can access the function
Private Optional. indicates that the function can be accessed only by procedures in the current module
Static Optional. indicates that the procedures variable are preserved between function calls
name Required. Name of the Function
argumentlist Optional. List of variables that are passed once the function is called.
retType Optional. Return type of the function
statements Code block to execute
expression Return value of the function

Using non-object Function Type in VBA

Below code block will calculate the area of a triangle given its base and height as its arguments using a non-object Function type.

Public Function GetTriangleArea(b, h) as Double
Dim area as Double
area= (b*h)/2
GetTriangleArea= area
End Function
Sub TestFunction()
Debug.print("The area of the triangle is " & GetTriangleArea(10,8) & " square meters.")
End Sub

Output:

The area of the triangle is 40 square meters.

Using object Function Type in VBA

The Below code block will demonstrate how to use an object Function Type to calculate the sum of all the numbers between two numbers.

Public Function GetCollection(numStr, numEnd) As Object
'Declaring variable for the counter
Dim i As Integer
'Creating a new Arraylist named coll
Dim coll As Object
Set coll = CreateObject("System.Collections.ArrayList")

'Add all number from numStr to numEnd to the arraylist named coll using a for loop
For i = numStr To numEnd
    coll.Add (i)
Next i

'Returning the arraylist named coll to the subroutine
    Set GetCollection = coll
End Function
Sub testFunction()

'Declaring collForSum as an Object
'collForSum object will be the placeholder object where the coll arraylist will be in placed
Dim collForSum As Object
'Setting up a counter named j
Dim j As Integer
'Sum variable will hold the running sum of the elements in collForSum
Dim sum As Double
'Calling the GetCollection Function to fill collForSUm Object
    Set collForSum = GetCollection(10, 20)
'For loop to iterate on all element of collForSum arraylist
For j = 0 To collForSum.Count - 1
    'Add all the elements inside the collForSum arraylist.
    sum = sum + collForSum(j)
Next j
Debug.Print ("The total sum is " & sum & ".")
End Sub

Output:

The total sum is 165.

Using of Exit Function Command for a Forced Return to the Subroutine

Returning a value to a subroutine or a function does not necessarily mean that the code execution in the current function will stop. We need to declare to stop the code execution on the function explicitly. The command to use is Exit Function.

Calling a function without the Exit Function command may return wrong values.

Public Function PositiveOrNegative(num) as String 
If num >= 0 Then
    PositiveOrNegative = "Positive"
End If
PositiveOrNegative = "Negative"
End Function

Sub TestFunction()
   Debug.Print (PositiveOrNegative(5))   
End Sub

TestFunction Output:

Negative

Output is Negative even though the TestFunction input is 5 since the code execution did not stop when it reached PositiveOrNegative = "Positive" code line.

We need to utilize the Exit Function command to resolve this.

Public Function PositiveOrNegative(num) as String 
If num >= 0 Then
    PositiveOrNegative = "Positive"
    'Exit Function will force the code execution in the current function to stop and return the current value of PositiveOrNegative
    Exit Function
End If
PositiveOrNegative = "Negative"
End Function

Sub TestFunction()
   Debug.Print (PositiveOrNegative(5))   
End Sub

Output:

Positive

Related Article - VBA Function

  • The Sum Function in VBA
  • Exit Sub in VBA
  • Add New Line in VBA
  • VLOOKUP Function in VBA