The Use of Function in VBA

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:

Syntax:

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

Definition:

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