non-objectFunction Type in VBA
objectFunction Type in VBA
Exit FunctionCommand 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:
||function that will return strings, integers, boolean, etc|
||function what will return object like ranges, arraylists, etc.|
[Public | Private] [ Static ] Function name [ ( argumentlist ) ] [ As retType ] [ statements ] [ name = expression ] [ Exit Function ] End Function
||Optional. indicates that other procedures in all modules can access the function|
||Optional. indicates that the function can be accessed only by procedures in the current module|
||Optional. indicates that the procedures variable are preserved between function calls|
||Required. Name of the Function|
||Optional. List of variables that are passed once the function is called.|
||Optional. Return type of the function|
||Code block to execute|
||Return value of the function|
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
The area of the triangle is 40 square meters.
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
The total sum is 165.
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
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
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