JIYIK CN >

This article will demonstrate how to create and use functions when working with VBA.

Functions are used to return results that a subroutine cannot return. As programs grow longer and more complex, functions are a useful tool that makes it easier to organize code blocks, debug, and modify scripts.

Functions can be divided into two subsets:

grammar:

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

definition:

非对象Using Function Types in VBA

The following code block will use 非对象the function type to calculate the area of ​​a triangle given its base and height as its parameters.

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.

objectUsing Function Types in VBA

The following code block will demonstrate how to use objectthe function type to calculate the sum of all 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.

Use Exit Functionthe command to force a return to the subroutine

Returning a value to a subroutine or function does not necessarily mean that the code execution in the current function will stop. We need to explicitly state to stop the code execution on a function. The command to be used is 退出功能.

Calling the function without Exit Functiona command may return an error value.

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

TestFunctionOutput:

Negative

Even if TestFunctionthe input is 5, the output is also Negative, because code execution PositiveOrNegative = "Positive"does not stop when it reaches the code line.

We need to use Exit Functionthe command to solve this problem.

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