Today's Goals

Tell Sub procedures and Functions apart
Define and call Sub procedures
Define and call Functions
Pass arguments correctly

Key Concepts

1. Sub vs. Function

A Sub performs an action and returns nothing; a Function performs an action and returns a value. Need a result back? Write a Function. Just getting something done? A Sub is enough. A Function also works as a worksheet formula (e.g. =计算面积(5, 3)).

2. Defining and Calling a Sub

vba
Sub ProcedureName(argumentList)
    ' code
End Sub

Call it with Call ProcedureName(arguments) or ProcedureName arguments. A macro is just a parameterless Sub — that's why F5 runs it.

3. Defining and Calling a Function

vba
Function FunctionName(argumentList) As ReturnType
    ' code
    FunctionName = returnValue
End Function

The key line assigns a value to the function's own name — that value is the return value. Skip it and nothing errors, but the function always returns 0 or an empty string. Capture the result in a variable when calling.

4. Passing Arguments: ByVal vs. ByRef

ByVal: the procedure gets a copy of the value; internal changes don't touch the original
ByRef (the default): the procedure gets the variable itself; changes carry back out

Set num1 = 10: after x = x + 10 inside a ByVal procedure, num1 is still 10; with ByRef it becomes 20. Prefer explicit ByVal in utility functions.

Today's Code

vba
Sub 调用过程示例()
    Call 显示欢迎信息
End Sub

Sub 显示欢迎信息()
    MsgBox "欢迎学习VBA!", vbInformation
End Sub

Function 计算面积(长 As Double, 宽 As Double) As Double
    计算面积 = 长 * 宽
End Function

Sub 调用函数示例()
    Dim area As Double
    area = 计算面积(5, 3)
    MsgBox "面积: " & area
End Sub

area = 计算面积(5, 3) is the standard call: the right side evaluates to 15 and lands in the variable on the left.

Real-World Scenario

The setup: finance reviews expense claims — anything over 1,000 needs manager approval. The check repeats across report after report, so write it once as a Function:

vba
Function 需要审批(金额 As Double) As Boolean
    需要审批 = 金额 > 1000
End Function

Sub 检查报销单()
    If 需要审批(1500) Then
        MsgBox "该笔报销需提交主管审批"
    Else
        MsgBox "该笔报销可直接通过"
    End If
End Sub

It pops up "该笔报销需提交主管审批". Type =需要审批(B2) into a cell and the same rule becomes a live formula — a business rule turned into a reusable asset.

Exercises

Create a function that calculates a perimeter
Create a procedure with an optional parameter
Create a function that returns the maximum value

Exercise Solutions

Function 计算周长(长 As Double, 宽 As Double) As Double, with 计算周长 = 2 * (长 + 宽) in the body.
Use the Optional keyword: Sub 欢迎(Optional name As String = "游客") — a bare call to 欢迎 uses the default value.
Running-maximum style: start from a, compare against b, then c, replacing on each bigger value (the 求最大值 function in the .bas file is the three-parameter version).

Extension Exercises

Turn Day03's leap-year test into Function 是闰年(y As Long) As Boolean and verify it from a cell.
Write Sub 交换(ByRef a As Long, ByRef b As Long) that swaps two variables.

Common Errors and Troubleshooting

Error / Symptom	Cause	Fix
Function always returns 0 or an empty string	Its name was never assigned	Add `函数名 = 返回值` inside the Function
`Compile error: Expected: =`	A Function called like a statement on its own	Capture the result: `x = 计算面积(5, 3)`
`Compile error: Expected: end of statement`	Parentheses on a multi-argument call without Call	Write `Call 过程名(a, b)` or `过程名 a, b`

Summary

Subs do things; Functions return values — and every Function must assign its own name. Arguments default to ByRef; mark utility functions ByVal explicitly.

由在线工具箱(www.vba.net)整理制作

🌱 Day05 - Procedures and Functions