User-Defined Functions

Definition and Calling of Functions

【Excel VBA】

There are two forms of functions in Excel VBA: one without a return value, called a procedure; the other with a return value, called a function.

A procedure is defined by the Sub…End Sub structure. Its syntax is as follows:

[ Private | Public ] _
Sub name [([param[, ...]])]
    Execution statements…
End Sub

Here, Private and Public are optional, used to define whether the procedure is private or public, i.e., to define the scope of the procedure. When using the Private keyword, it indicates that the procedure is only used in the current module, usually to serve other functions in the module; when using the Public keyword, the procedure can be called by functions in other modules. The Sub…End Sub structure defines the body of the procedure; name is the procedure name; param is the parameter defined for the procedure; the lines between the Sub line and the End Sub line are the executable statements.

Running the following procedure will output a string in the Immediate Window.

Private Sub Output()
    Debug.Print "Hello, VBA!"
End Sub

Write the procedure code in the same module and call the procedure.

Sub Test()
    Output
End Sub

Running this procedure outputs the result in the Immediate Window.

Hello, VBA!

In Excel VBA, a function is defined by the Function…End Function structure. Its syntax is as follows:

[ Private | Public ] _
Function name [type] [([param[, ...]])] [As type]
    Execution statements...
End Function

Here, the Function…End Function structure defines the body of the function; As type indicates the data type of the return value; the descriptions of other keywords and names are the same as those of procedures.

The following Sum function calculates the sum of two given floating-point numbers and returns the result as a floating-point number.

Private Function Sum(sngA As Single, sngB As Single) As Single
    Sum = sngA + sngB
End Function

Write the procedure code in the same module and call the function.

Sub Test()
    Debug.Print Sum(1.2, 8.3)
End Sub

Running the function outputs the sum of 1.2 and 8.3, i.e., 9.5, in the Immediate Window.

【Python】

In Python, the syntax for user-defined functions is as follows:

def functionname(parameters):
    "Function documentation string"
    Function body
    return [expression]

Here, def and return are keywords; functionname is the function name; parameters is the parameter list. Note that there is a colon after the parentheses. Add a comment on the first line after the colon to describe the function’s functionality, which can be viewed using the help function. The function body defines the function’s functionality with code. It starts with the def keyword and ends with the return statement. If there is an expression, it returns the function’s return value; if not, it returns None.

After defining a function, it can be called at other positions in the module. When calling, you need to specify the function name and parameters; if there is a return value, specify the variable to引用the return value.

A function can have no parameters or no return value. Below, we define a function that uses a series of asterisks as a separator line for output content. After defining the function, we perform three operations and call the function to draw an asterisk separator line to separate the results of various operations when outputting. The file path is Samples\ch10\Python\sam10-001.py.

def starline():   # Define the starline function to draw an asterisk separator line
    "Asterisk separator line"   # Function description
    print('*'; * 40)   # Output 40 asterisks
    return
a = 1; b = 2
print('a={}, b={}'.format(1, 2))
print('a+b={}'.format(a + b))   # Addition of two numbers
starline()   # Call the starline function to draw a separator line
print('a={}, b={}'.format(1, 2))
print('a-b={}'.format(a - b))   # Subtraction of two numbers
starline()   # Call the starline function to draw a separator line
print('a={}, b={}'.format(1, 2))
print('a*b={}'.format(a * b))   # Multiplication of two numbers
help(starline)   # Output the description of the starline function

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-001.py
a=1, b=2
a+b=3
****************************************
a=1, b=2
a-b=-1
****************************************
a=1, b=2
a*b=2
Help on function starline in module __main__:
starline()
    Asterisk separator line

It can be seen that once a function is defined, it can be called repeatedly, thereby improving programming efficiency. Finally, the description of the starline function is displayed.

The starline function defined above has neither parameters nor a return value. Below, we define a mysum function to calculate the sum of two given numbers. Thus, the mysum function has two input parameters and one return value. The file path is Samples\ch10\Python\sam10-002.py.

def mysum(a, b):  # Summation
    "Calculate the sum of two numbers"
    return a + b
print('3+6={}'.format(mysum(3, 6)))   # Calculate and output the sum of 3 and 6
print('12+9={}'.format(mysum(12, 9)))  # Calculate and output the sum of 12 and 9

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-002.py
3+6=9
12+9=21

Multiple Return Values

【Excel VBA】

In Excel VBA, when a function has multiple return values, there are two methods to return them: one is to use procedure parameters to pass in values, complete the calculation, and then use the parameters to pass out the return values; the other is to save the return values to an array and return the array.

Below, we write a Sum procedure to first calculate the sum and difference of two given parameters, then pass the obtained sum and difference back using parameters.

Private Sub Sum(sngA As Single, sngB As Single)
    ' Pass in calculation parameters; use parameters to return results
    Dim sngC As Single, sngD As Single
    sngC = sngA + sngB
    sngD = sngA - sngB
    sngA = sngC
    sngB = sngD
End Sub

Write the procedure code in the same module, call the Sum procedure, input parameters to complete the calculation, and then pass the return values back using parameters.

Sub Test()
    Dim sngA As Single, sngB As Single
    sngA = 8
    sngB = 3
    Sum sngA, sngB
    Debug.Print sngA; sngB
End Sub

Running the procedure outputs the calculation results in the Immediate Window.

11  5

Note that the values of parameters sngA and sngB change before and after calling the Sum procedure in the Test procedure; this will be explained in Section 10.3.6.

Another method for passing multiple return values is to save them to an array and return the array. The following Sum2 function saves the sum and difference of two given parameters to an array and returns the array.

Private Function Sum2(sngA As Single, sngB As Single)
    Dim sngT(1) As Single
    sngT(0) = sngA + sngB
    sngT(1) = sngA - sngB
    Sum2 = sngT
    Erase sngT
End Function

Write the procedure code in the same module, first call the Sum2 function and return the calculation result to an array, then output the array values in the Immediate Window.

Sub Test2()
    Dim sngR
    sngR = Sum2(8, 3)
    Debug.Print sngR(0); sngR(1)
End Sub

Running the procedure outputs the sum and difference of 8 and 3 in the Immediate Window.

11  5

【Python】

In Python, when a function has multiple return values, you can either return them directly using the return statement or first write each return value to a list and then return the list.

Below, we define a function that takes two parameters and returns their sum and difference. The file path is Samples\ch10\Python\sam10-003.py.

def mycomp(a, b):   # Calculate the sum and difference of two given values
    c = a + b
    d = a - b
    return c, d
c, d = mycomp(2, 3)   # Call the mycomp function to calculate the sum and difference of 2 and 3
print('2+3={}'.format(c))   # Output the sum
print('2-3={}'.format(d))   # Output the difference

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-003.py
2+3=5
2-3=-1

When there are multiple return values, you can also add these return values to a list and use the return statement to return the list. Rewrite the above example below. The file path is Samples\ch10\Python\sam10-004.py.

def mycomp(a, b):   # Calculate the sum and difference of two given values
    data = []
    data.append(a + b)
    data.append(a - b)
    return data   # Return the sum and difference class="text-secondary">as a list
data = mycomp(2, 3)   # Call the mycomp function to calculate the sum and difference of 2 and 3
print(data)   # Output the list containing the sum and difference

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-004.py
[5, -1]

Optional Parameters and Default Parameters

Optional parameters are non-essential parameters; they may or may not be present. Default parameters are parameters with predefined default values; they must be optional parameters, and you cannot define default values for required parameters. For optional parameters, if no value is assigned when calling the function, the predefined default value is used; if a new value is assigned, it overrides the default value.

【Excel VBA】

In Excel VBA, the Optional keyword can be used to define optional parameters. Below, we define a Para procedure with 3 parameters, the latter two being optional parameters.

Private Sub Para(strID As String, Optional strName As String, _
    Optional sngScore As Single)
    Debug.Print strID & vbTab;
    ' If optional parameters are not used, do not output their values
    If Not strName = "" Then Debug.Print strName & vbTab;
    If Not sngScore = 0 Then Debug.Print sngScore;
    Debug.Print
End Sub

Write the program in the same module and call the Para procedure with different numbers of parameters.

Sub Test()
    Para "ID001"
    Para "ID001", "Jiang Lin"
    Para "ID001", "Xu Shu", 95
    Para "ID001", , 95  ' No value assigned to the second parameter
End Sub

Running the procedure outputs the following results in the Immediate Window.

ID001
ID001    Jiang Lin
ID001    Xu Shu     95
ID001    95

You can define default values for optional parameters, as shown below where the sngScore parameter is defined with a default value of 80.

Private Sub Para2(strID As String, Optional strName As String, _
    Optional sngScore As Single = 80)
    Debug.Print strID & vbTab;
    Debug.Print strID & vbTab;
    If Not strName = "" Then Debug.Print strName & vbTab;
    Debug.Print sngScore;
    Debug.Print
End Sub

Write the test procedure.

Sub Test2()
    Para2 "ID001"
    Para2 "ID001", , 90
End Sub

Running the procedure outputs the following results in the Immediate Window.

ID001    80
ID001    90

It can be seen that although no value is assigned to the third parameter when calling the Para2 procedure, because a default value is defined for this parameter, its default value 80 is still output. If a new value is assigned to this parameter, the new value overrides the default value.

【Python】

When defining a function, using an assignment statement for a function parameter can specify the default value of that parameter. The para function defined below has two parameters, id and score, with the default value of score set to 80. The file path is Samples\ch10\Python\sam10-005.py.

def para(id, score=80):   # Specify the default value of the score parameter class="text-secondary">as 80
    print('ID: 7;, id)   # Output id
    print('Score: ', score)   # Output score
    return
para('No001')   # Call the para function, only specify the value of the id parameter
para('No0027;, 90)   # Call the para function, specify values for both parameters

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-005.py
ID:  No001
Score:  80
ID:  No002
Score:  90

It can be seen that when no value is passed for the score parameter, the default value 80 is used.

Variable-Length Parameters

Variable-length parameters refer to parameters whose number is uncertain, which can be 0, 1, or any number.

【Excel VBA】

In Excel VBA, you can use an array to specify variable-length parameters, and add the ParamArray keyword before the parameters. The definition of a function containing variable-length parameters is as follows.

Function FunName(ParamArray paras() As Variant)
    Execution statements...
End Function

Note that the data of variable-length parameters must be of the Variant type. Variable-length parameters must be the last parameter in the parameter list and cannot be used with optional parameters.

Below, we define a function to calculate the sum of a set of data, where the number of data points is uncertain.

Private Function MySum(ParamArray paras()) As Single
    Dim para
    Dim sngR As Single
    sngR = 0
    For Each para In paras
        sngR = sngR + para
    Next
    MySum = sngR
End Function

Write the program and call the MySum function to accumulate and sum.

Sub Test()
    Debug.Print MySum(1, 2, 3)
    Debug.Print MySum(1, 2, 3, 4, 5, 6, 7, 8)
End Sub

Running the procedure outputs the calculation results with different numbers of parameters in the Immediate Window.

6
36

【Python】

The definition of a function containing variable-length parameters in Python is as follows.

def functionname([args,] *args_tuple):
    Function body
    return [expression]

Here, [args,] defines required parameters, and *args_tuple defines variable-length parameters. *args_tuple is passed in as a tuple.

Below, we define a function for summation. The first data of this operation is fixed, and the subsequent data is uncertain, with both the number and size of data being uncertain. The file path is Samples\ch10\Python\sam10-006.py.

def mysum(arg1, *vartuple):  # arg1 is a required parameter, *vartuple is a variable-length parameter
    sum = arg1
    for var in vartuple:   # Accumulate and sum
        sum += var
    return sum
a = mysum(10, 10, 20, 30)   # Call the mysum function and specify parameters for summation
print(a)

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-006.py
70

Parameters as Dictionaries

【Excel VBA】

In Excel VBA, a dictionary can be passed like other types of data. The following OutputData procedure outputs the key-value pairs of a specified dictionary in the Immediate Window.

Private Sub OutputData(dicT As Dictionary)
    Dim strID
    For Each strID In dicT.Keys
        Debug.Print strID & vbTab & dicT(strID)
    Next
End Sub

Write the program, create a dictionary, and call the OutputData procedure to output data.

Sub Test()
    Dim dicT As Dictionary
    Set dicT = New Dictionary
    dicT.Add "NO001", 89
    dicT.Add "NO002", 92
    dicT.Add "NO003", 79
    OutputData dicT
End Sub

Running the procedure outputs the data of the newly created dictionary object dicT in the Immediate Window.

NO001   89
NO002   92
NO003   79

【Python】

In Python, if a function’s parameter has two asterisks, it indicates that the parameter is a dictionary. The syntax for a function that passes a dictionary parameter is as follows.

def functionname([args,] **args_dict):
    "Function documentation string"
    Function body
    return [expression]

Here, [args,] defines required parameters, and **args_dict defines a dictionary parameter (note the two asterisks). The dictionary parameter corresponds to two actual parameters expressed as assignment statements, which are the key and value of the dictionary, respectively.

Below, we define a function with a dictionary parameter to output dictionary data. The file path is Samples\ch10\Python\sam10-007.py.

def paradict(**vdict):   # Parameter is a dictionary
    print(vdict)
paradict(id='No001', score=80)   # Call the function; note the input method of actual parameters

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-007.py
{'id': 'No001', 'score': 80}

Pass by Value vs. Pass by Reference

In functions, when an object is passed as a parameter, it is necessary to clarify whether the function passes the object’s address or its value. The main difference between pass by reference and pass by value is that if the parameter’s value is modified in the function body, the parameter’s value will change before and after calling the function if pass by reference is used; if pass by value is used, the parameter’s value remains unchanged.

【Excel VBA】

In Excel VBA, there are two ways to pass parameters in procedures: pass by value and pass by reference. By default, parameters are passed by reference. In the Excel VBA example code in Section 10.3.2, the Sum procedure first passes data using two parameters, then uses these two parameters to pass out their sum and difference. In the test procedure, the values of the parameters change before and after calling the Sum procedure, so by default, Excel VBA functions pass parameters by reference. Using the ByVal keyword, you can specify that parameters are passed by value.

Below, we modify the Sum procedure to set the two parameters to be passed by value.

Private Sub Sum(ByVal sngA As Single, ByVal sngB As Single)
    Dim sngC As Single, sngD As Single
    sngC = sngA + sngB
    sngD = sngA - sngB
    sngA = sngC
    sngB = sngD
End Sub

Write the program in the same module and call the Sum procedure for calculation.

Sub Test()
    Dim sngA As Single, sngB As Single
    sngA = 8
    sngB = 3
    Sum sngA, sngB
    Debug.Print sngA; sngB
End Sub

Running the procedure outputs the calculation results in the Immediate Window.

8  3

It can be seen that since the Sum procedure sets the parameters to be passed by value, the values of the parameters do not change before and after calling the Sum procedure in the test procedure.

【Python】

In Python, for immutable types (including strings, tuples, and numbers), when used as function parameters, they are passed by value. At this time, the value of the object is passed, and modifying a copied object does not affect the object itself. For mutable types (including lists and dictionaries), when used as function parameters, they are passed by reference. At this time, the object itself is passed, and modifying it will also affect it outside the function.

An example is given below. For immutable types, the following function passes a string; check if the parameter’s value changes before and after calling the function. The file path is Samples\ch10\Python\sam10-008.py.

def TP(a):
    a = 'python'   # Modify the parameter’s value to "python"
b = 'hello'   # Assign initial value "hello" to variable b
TP(b)   # Call the function with variable b class="text-secondary">as the parameter
print(b)   # Output the value of variable b

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-008.py
hello

It can be seen that the value of the variable does not change before and after calling the function; the parameter is passed by value.

For mutable types, the following function passes a list and adds a list element to the list in the function body. The file path is Samples\ch10\Python\sam10-009.py.

def TP(lst):   # Parameter is a list
    lst.append([6, 7, 8, 9])   # Add a list element to the passed list
    return
lst = [1, 2, 3, 4, 5]
print(lst)
TP(lst)   # Call the function with the list class="text-secondary">as the parameter
print(lst)

In the Python IDLE script window, select Run → Run Module. The IDLE command-line window displays the following results.

>>> = RESTART: .../Samples/ch10/Python\sam10-009.py
[1, 2, 3, 4, 5]
[1, 2, 3, 4, 5, [6, 7, 8, 9]]

It can be seen that the list changes before and after calling the function; the parameter is passed by reference.