乐于分享
好东西不私藏
当前位置：夜雨聆风 > 技术教程 > 软件教程 > Excel VBA 自定义函数怎么写,把佣金和客户分级写成自己的公式

Excel VBA 自定义函数怎么写,把佣金和客户分级写成自己的公式

当前时间： 2026-04-13 06:15:28 更新时间： 2026-04-13 分类：软件教程 评论(0)

Excel VBA 自定义函数怎么写,把佣金和客户分级写成自己的公式

销售周报里最容易越写越长的，一般不是表头，而是那些“每个月都可能微调一次”的业务规则。佣金按档位算，老员工还要上浮一点，跟进超时又要提级，表里只要叠上两三条判断，IF 基本就已经不像给人维护的了。

我平时碰到这种表，通常不会继续把规则堆在单元格里，而是收进 UDF，也就是自定义函数。工作表里留短公式，真正的判断写进 Function。这样做的好处不只是公式变短，更重要的是规则终于有了固定住处，后面改逻辑时不会整张表到处翻。

这篇我不只想讲一个佣金函数，而是把做 UDF 时最该掌握的那条线一起串起来：为什么要写、函数过程怎么声明、代码该放哪、能从哪里调用、参数有哪些形态、怎么返回错误值和数组、怎么让它更像内置函数、怎么调试、怎么做成加载项，以及什么时候该停在 UDF，什么时候才值得往 Windows API 走。

先看一张销售团队常见的月度跟进表。假设工作表里 B2:G6 这一块是业务同事每天在维护的主表：

A
B
C
D
E
F
G
1
2
客户
月销售额
跟进天数
工龄
客户等级
应发佣金
3
华辰商贸
8600
16
1
4
云岚设备
15400
5
3
5
景禾门店
28700
11
2
6
宏川制造
46300
2
6

为什么 UDF 值得写，也别拿它替掉所有内置函数

UDF 适合接住两类东西：

  • 会反复出现的业务规则
  • 工作表里已经写得过长、过碎的判断逻辑

它能解决的几个问题很直接：

  • 把很长的工作表公式缩成一个清楚的函数名
  • 同一段规则既能给单元格用，也能给 VBA 过程复用
  • 业务规则改了，只动一个函数，不用去改十几处公式
  • 函数名本身就能变成业务语义，比如 CalcCommission、CustomerLevel

不过 UDF 也不是越多越好。SUM、AVERAGE、IF 这类内置函数已经足够成熟，能直接用的时候，我一般不会为了“看起来会写 VBA”再包一层。自定义函数在工作表里跑，很多时候确实会比内置函数慢一些，它更适合做规则封装，不适合拿来替掉所有现成函数。

UDF 的本质其实就三步：

  • 接收参数
  • 处理逻辑
  • 返回结果

它更像一个可被工作表、宏过程、条件格式共同调用的计算单元，而不是一个“顺手帮你改表”的过程。这个边界一旦想清楚，后面很多写法就不会跑偏。

函数过程怎么写，代码该放在哪

UDF 的标准语法长这样：

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
[Public | Private] [Static] Function name[(arglist)] [As type]    [instructions]    [name = expression]    [ExitFunction]    [instructions]    [name = expression]EndFunction

这几段平时最常用的是：

  • Public：别的模块和工作表都能访问；省略时默认也是 Public
  • Private：只允许同一模块内部调用；它不会出现在“插入函数”对话框里
  • Static：过程级变量在两次调用之间保留值，适合少数需要记状态的场景
  • As type：声明返回值类型
  • Exit Function：在提前判错或满足条件时立即退出

函数名本身也有几条很实在的约束：

  • 别起得像单元格地址，比如 ABC123
  • 别和 Excel 内置函数重名
  • 别和工作簿里的命名区域撞名

还有一个比语法更重要的前提：想让工作表公式直接识别 UDF，代码要放在标准模块里，比如 Module1。如果你把函数写在 Sheet、ThisWorkbook 或 UserForm 里，单元格里大概率直接看到 #NAME?。

无参数函数是最简单的一类，本质上常常就是把一个对象属性包装成工作表函数：

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
PublicFunction ReportOwner() AsString    ReportOwner = Application.UserNameEndFunctionPublicFunction CurrentSheetName() AsString    CurrentSheetName = Application.Caller.Parent.NameEndFunction

在工作表里调用无参数函数时，我一般都会把空括号也写上，比如 =ReportOwner()。这样读起来更像标准函数，后面接手的人也不容易误会这只是个命名单元格。

同一个 UDF，通常有 4 种调用方式

很多人以为 UDF 只能写给单元格用，这其实只是其中一种。

  • 工作表公式里直接调用：=CalcCommission(C3,E3)
  • VBA 过程中调用：result = CalcCommission(Cells(3, "C").Value, Cells(3, "E").Value)
  • 条件格式公式里调用：=IsPriorityClient($C3,$D3)
  • VBE 的立即窗口里测试：?CalcCommission(28700,2)

如果你是在另一个过程里调函数，除了直接写函数名，还可以用 Application.Run。这在跨工作簿、动态按名字调用函数时会方便一些。

条件格式这条路也很实用。比如你想把“销售额高或久未跟进”的客户自动标黄，就可以写一个布尔函数，再在条件格式公式里直接调用。UDF 一旦能在单元格公式和条件格式里共用，很多“人工盯表”的动作就能省掉。

参数才是 UDF 真正的主体

一个参数、两个参数，是最常见的起步方式

很多业务函数一开始都只有一个参数。像佣金规则，最基础的版本往往只收销售额。等规则再细一点，再多收一个工龄参数，就从“教学例子”变成“业务里真能用”的函数了。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
PublicFunction CalcCommission(ByVal salesAmount As Variant, _OptionalByValyearsAsLong=0)As VariantDim baseRate AsDoubleDim result AsDouble'销售额不是数字时，直接返回真正的 Excel 错误值IfNot IsNumeric(salesAmount) Then        CalcCommission = CVErr(xlErrValue)ExitFunctionEndIf'负数销售额通常是录入问题IfCDbl(salesAmount) < 0Then        CalcCommission = CVErr(xlErrNum)ExitFunctionEndIfSelectCaseCDbl(salesAmount)Case0To9999.99: baseRate = 0.08Case10000To19999.99: baseRate = 0.105Case20000To39999.99: baseRate = 0.12CaseIs >= 40000: baseRate = 0.14EndSelect    result = CDbl(salesAmount) * baseRate'工龄每满 1 年，佣金结果再上浮 1%    result = result + result * years / 100    CalcCommission = resultEndFunction

放回刚才那张表，G3 里直接写 =CalcCommission(C3,E3) 就能往下填。要是你只想做单参数版本，把 years 拿掉就是基础版；一旦业务里要把工龄也算进去，再把第二个参数补回来就行。

这个函数还有一个很实际的价值：它不只给工作表用。你在月结宏里照样可以复用它：

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
Sub FillCommissionResult()Dim lastRow AsLongDim i AsLong    lastRow = Cells(Rows.Count, "B").End(xlUp).RowFor i = 3To lastRow        Cells(i, "G").Value = CalcCommission(Cells(i, "C").Value, Cells(i, "E").Value)Next iEndSub

同一套规则，工作表里写一次，宏里再复用一次，这就是 UDF 很省事的地方。

参数尽量从外面传进来，别在函数里写死地址

这是我最不建议养成的习惯之一。比如下面这种写法：

  • 1
  • 2
  • 3
Function DoubleSales()    DoubleSales = Range("C3").Value * 2EndFunction

它短期能跑，后面几乎一定难维护。工作表位置一变，函数就偏了；别的表想复用，也只能继续改代码。

更稳的思路，是把单元格、区域、数组都当参数传进来。比如你想把一块销售额区域加总：

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
PublicFunction SumSales(list As Variant) AsDoubleDim item As VariantForEach item In listIf IsNumeric(item) Then   SumSales=SumSales+CDbl(item)EndIfNext itemEndFunction

这个函数既能在工作表里写 =SumSales(C3:C6)，也能在 VBA 里吃一个数组变量。这里参数故意没写死成具体数值类型，就是为了让它既能接工作表区域，也能接 VBA 数组。很多“区域统计”函数都是这个路子。

可选参数、IsMissing 和 Application.Volatile，会决定函数好不好用

如果只是给参数设默认值，直接写 Optional years As Long = 0 就够了。可如果你想判断“这个参数到底有没有传进来”，就要把它声明成 Variant，再配 IsMissing()。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
PublicFunction ReportOwnerEx(Optional UpperCase As Variant) AsStringIf IsMissing(UpperCase) Then UpperCase = False    ReportOwnerEx = Application.UserNameIfCBool(UpperCase) Then        ReportOwnerEx = UCase$(ReportOwnerEx)EndIfEndFunction

这时候下面三种写法都成立：

  • =ReportOwnerEx()
  • =ReportOwnerEx(FALSE)
  • =ReportOwnerEx(TRUE)

像抽查名单、随机分配审核人这类函数，还经常会碰到 Application.Volatile。它决定函数是不是“重算就刷新”的易失函数：

  • 1
  • 2
  • 3
  • 4
  • 5
PublicFunction DrawReviewer(nameRange As Variant, _OptionalRecalcAsVariant=False)As Variant    Application.Volatile Recalc    DrawReviewer = nameRange(Int((nameRange.Count) * Rnd + 1))EndFunction

这里的意思很直接：

  • =DrawReviewer(A2:A20)：区域没变时，结果通常不会乱跳
  • =DrawReviewer(A2:A20,TRUE)：工作表一重算，就会重新抽一个名字

这和 STATICRAND()、NONSTATICRAND() 这一类函数背后的思路是一样的。是否易失，不只是一个语法细节，它会直接影响工作表的重算行为。函数一多时，这个差别会很明显。

不定数量参数用 ParamArray

如果你想写一个像 SUM(number1, number2, ...) 这样的函数，就要用 ParamArray。它只能放在参数列表最后，类型也总是 Variant。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
PublicFunction MultiSum(ParamArray arglist() As Variant) AsDoubleDim arg As VariantDim cell As RangeForEach arg In arglistIf TypeName(arg) = "Range"ThenForEachcellIn arg.CellsIfIsNumeric(cell.Value) Then   MultiSum=MultiSum+CDbl(cell.Value)EndIfNext cellElseIf IsNumeric(arg) Then   MultiSum=MultiSum+CDbl(arg)EndIfNext argEndFunction

这个函数可以这样调：

  • =MultiSum(C3:C6,E3:E6)
  • =MultiSum(C3,500,C4:C6)

简单版本的 ParamArray 函数很容易死在文本、错误值、逻辑值和整列引用上，所以它特别适合拿来学习“成熟 UDF 要开始处理多少种输入”。

返回值不只是一格数字

该报错的时候，要返回真正的 Excel 错误值

如果函数只是返回字符串 "#N/A"，看起来像错误，但 Excel 不一定把它当成真正的错误值。更稳的做法是用 CVErr。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
PublicFunction CustomerLevel(ByVal salesAmount As Variant, _ByValfollowUpDaysAsVariant)As Variant'参数不合法时，直接返回 Excel 标准错误IfNot IsNumeric(salesAmount) OrNot IsNumeric(followUpDays) Then        CustomerLevel = CVErr(xlErrValue)ExitFunctionEndIfIfCDbl(salesAmount) >= 30000OrCLng(followUpDays) >= 15Then        CustomerLevel = "重点跟进"ElseIfCDbl(salesAmount) >= 10000OrCLng(followUpDays) >= 7Then        CustomerLevel = "常规跟进"Else        CustomerLevel = "稳定维护"EndIfEndFunction

这里返回值类型写成 Variant，就是因为它既可能返回文本，也可能返回 CVErr(xlErrValue) 这种错误值。只要函数有机会返回“不是单一类型的一种东西”，我一般都会优先考虑 Variant。

常见的 Excel 错误常量也值得记住几项：

  • xlErrValue 对应 #VALUE!
  • xlErrNA 对应 #N/A
  • xlErrNum 对应 #NUM!
  • xlErrRef 对应 #REF!
  • xlErrName 对应 #NAME?

UDF 也能返回数组

这一点很多刚接触 VBA 的人会忽略。UDF 不只会回一格结果，也能把一组值交给工作表。比如做报表头部月份标签时，这种写法就很好用：

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
PublicFunction MonthLabels(Optional monthIndex As Variant) As VariantDim labels As Variant    labels = Array("1月", "2月", "3月", "4月", "5月", "6月", _"7月","8月","9月","10月","11月","12月")If IsMissing(monthIndex) Then        MonthLabels = labelsElseIfCLng(monthIndex) <= 0Then        MonthLabels = Application.Transpose(labels)Else        MonthLabels = labels((CLng(monthIndex) - 1) Mod12)EndIfEndFunction

这组写法有三种常见用法：

  • 省略参数：返回整组月份标签
  • 参数小于等于 0：返回竖向数组
  • 参数大于等于 1：返回某一个月份标签

如果你用的是老版本 Excel，数组结果往往要先选中多个单元格，再按 Ctrl + Shift + Enter。自己写数组函数时，还要顺手留意 Option Base 和数组下界，别把 0 基和 1 基混在一起。

想让 UDF 更像内置函数，还要补三层

一层是输入兼容

真正成熟的 UDF，往往不像 CalcCommission 这么单纯。拿一个模仿 SUM 的函数来说，你很快就会发现自己得开始处理这些情况：

  • 单个数值
  • 看起来像数值的文本
  • 逻辑值
  • 单元格区域
  • 整行整列引用
  • VBA 数组
  • 省略参数
  • 错误值
  • 表达式结果

整列引用还不能傻扫到底，通常要先和 UsedRange 做交集，避免一整列几十万行都遍历一遍。做到这一步时，你就已经不只是“会写个 Function”了，而是在把函数往“像内置函数那样稳”的方向推。

不过这里也有个边界。像 SUM 这种内置函数，Excel 自己已经做得非常成熟了。自己写个 MySum 更适合作为练手和理解 UDF 设计，不一定真的拿去替代内置 SUM。

一层是“插入函数”里的说明和分类

如果 UDF 只是自己用，写完能跑就够了。只要这本工作簿还要给同事用，我通常会再补一步 Application.MacroOptions，让它在“插入函数”里看起来更像个正经函数。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
PublicSub RegisterUdfInfo()    Application.MacroOptions _        Macro:="CalcCommission", _        Description:="按销售额和工龄计算业务佣金", _        Category:="销售规则", _        ArgumentDescriptions:=Array("月销售额", "工龄年数")    Application.MacroOptions _        Macro:="CustomerLevel", _        Description:="按销售额和跟进天数给客户分级", _        Category:="销售规则", _        ArgumentDescriptions:=Array("月销售额", "跟进天数")EndSub

这样做会带来几个变化：

  • “插入函数”里能看到函数说明
  • 参数提示更清楚
  • 分类可以用编号，也可以直接自定义文字
  • Excel 会更规整地识别函数名的大小写

这里有个很容易漏掉的细节：Category 不只是能写 "销售规则" 这种自定义名称，也能直接写 Excel 内置函数分类的编号。你如果想把函数挂到“日期与时间”“文本”“逻辑”这些现成分类里，靠的就是这个编号。

比如日期类函数就可以这样写：

  • 1
  • 2
  • 3
  • 4
  • 5
Application.MacroOptions _    Macro:="XDateText", _    Description:="按年、月、日生成日期文本", _    Category:=2, _    ArgumentDescriptions:=Array("年份", "月份", "日期", "显示格式")

上面这个 Category:=2，对应的就是 Excel 内置的“日期与时间”分类。要是你改成 Category:=7，它就会落到“文本”分类里；写成 Category:="销售规则"，Excel 则会按这个名字新建一个自定义分类。

为了后面查起来方便，这个编号对照表我建议直接留在文章里：

类别编号
Excel 分类
0
全部
1
财务
2
日期与时间
3
数学与三角函数
4
统计
5
查找与引用
6
数据库
7
文本
8
逻辑
9
信息
10
命令
11
自定义
14
用户定义
15
工程
16
Cube
17
兼容性
18
Web

实际写的时候，可以按函数用途去对：

  • 信息判断函数，常见写 Category:=9
  • 工程计算类函数，常见写 Category:=15

如果你只是想让自己团队的一组函数单独成类，那就直接用自定义文字；如果你是想让 UDF 看起来更像 Excel 原生函数，放进现成内置分类会更自然。

如果你不走 MacroOptions，也能在“宏”对话框的选项里手工补说明，只是那条路更适合偶尔改一次，不像 MacroOptions 这么方便重复部署。

一层是跨工作簿复用

UDF 跨工作簿常见有三条路：

  • 公式里直接带工作簿名，比如 =MyFuncs.xlsm!CalcCommission(C3,E3)
  • 在 VBE 里通过“工具 -> 引用”建立引用
  • 把常用函数做成加载项

做成加载项以后，公式会短很多，也更适合长期复用。代价也很现实：这张表以后就依赖这个加载项了，发给别人时要一起说明清楚，不然对方的公式会直接断掉。

调试时别盯着 #VALUE! 猜

工作表里的 UDF 一旦报错，给你的反馈往往只有一个 #VALUE!。这时候继续盯着单元格看，通常帮助不大。我更常用的是下面这套定位顺序：

  • 先写个小 Sub 从过程里调用函数
  • 在关键语句上打断点，按 F8 单步走
  • 用 Debug.Print 把关键变量打到立即窗口
  • 需要临时确认值时，再短暂用 MsgBox

比如：

  • 1
  • 2
  • 3
  • 4
PublicSub TestCalcCommission()    Debug.Print CalcCommission(28700, 2)    Debug.Print CustomerLevel(28700, 11)EndSub

几个常见现象，通常都能很快对上原因：

现象
常见原因
#NAME?
函数没放在标准模块、函数名拼错、和内置函数或命名区域撞名、加载项没加载
#VALUE!
参数类型不对、函数内部出错、返回类型写得太死
不重算
不是易失函数，参数也没变化
每次都重算
用了 Application.Volatile True
“插入函数”里找不到
函数是 Private，或者没做说明和分类

还有个很容易忽略的点：UDF 不像 Sub 那样适合从“宏”对话框里直接运行。它更像一个被别人调用的计算单元，所以测试时最顺手的入口，往往不是宏列表，而是另一个过程、工作表单元格，或者 VBE 的立即窗口。

特殊场景：日期扩展和 Windows API

日期扩展函数，适合档案和历史类场景

Excel 对 1900 年之前的日期支持一直不算好。真碰到老档案、家谱、历史台账这种表，VBA 自定义函数就能把范围往前补一些。

  • 1
  • 2
  • 3
  • 4
PublicFunction XDateText(ByVal y AsLong, ByVal m AsLong, ByVal d AsLong, _OptionalByValfmtAsString="yyyy-mm-dd")AsString    XDateText = Format(DateSerial(y, m, d), fmt)EndFunction

这类函数好用的地方，是能把年、月、日重新组织成一个可展示的日期文本。要注意的地方也很明确：

  • 它返回的是日期字符串，不是 Excel 的日期序列值
  • 如果后面还要继续做标准日期算术，得再想清楚类型
  • 1752 年之前涉及历法切换的日期，要对精度保留一点谨慎
  • DateSerial 遇到月份写成 13、日期越界这类情况，会自动滚到下一月或下一年

Windows API，适合对象模型做不到的事

UDF 再往外走一步，就是调用 Windows API。这个方向能力很强，但风险也明显更高。很多 API 声明在 64 位 Excel 里都要带 PtrSafe，真要兼容 32 位和 64 位，最好再加条件编译。

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
PublicDeclare PtrSafe Function GetWindowsDirectoryA Lib"kernel32" _    (ByVal lpBuffer AsString, ByVal nSize AsLong) AsLongPublicFunction WindowsDir() AsStringDim winPath AsString * 255    winPath = Space$(255)    WindowsDir = Left$(winPath, GetWindowsDirectoryA(winPath, Len(winPath)))EndFunction

这样工作表里就能直接写 =WindowsDir()。

API 能做的事不只这一种。像 GetKeyState 这类函数，还能判断按钮点击时有没有按着 Shift、Ctrl、Alt。只是这一步已经开始离开“普通业务规则封装”了，调试成本、兼容性和崩溃风险都会上来。所以我自己的习惯一直是：先把标准模块、参数传递、错误值、调试、加载项这些基础动作写稳，再考虑要不要上 API。

我自己写 UDF 时，会过这一遍

真到开始落代码时，我通常会按下面这条线检查：

  • 这条规则值不值得单独封成函数，而不是继续写工作表公式
  • 函数是不是放在标准模块里
  • 参数是不是从单元格、区域或数组传进来，而不是函数内部硬编码地址
  • 返回值类型够不够灵活，该报错时有没有用 CVErr
  • 需不需要可选参数、数组返回值或 ParamArray
  • 这函数会不会因为 Application.Volatile 变得太“活”
  • 需不需要补 MacroOptions 说明和分类
  • 以后是放在当前工作簿里用，还是该做成加载项
  • 如果对象模型已经够用，是不是根本没必要走到 Windows API

把这些点理顺之后，工作表里留下来的通常只是一条短公式，真正复杂的规则被收进了一个能复用、能调试、也更容易部署的地方。对日常办公来说，这比单纯“会写一个 Function”有用得多。