ARTICLE · 1359422
VBA自定义函数丨你写的 80 行,Excel 早就内置了
VBA 自定义函数 · 深入篇 04 / 05 借力打力
在自定义函数里调用 VLOOKUP / SUMIFS、两种调用方式的报错差异,以及按填充色统计单元格
为了「按客户名查单价」,有人自己写双重循环、逐格比较、还要处理找不到时的提示,一晃 80 行;为了「按部门汇总金额」,又写一个遍历判断加累加。写完发现又慢又容易漏边界。
其实 Excel 那几百个工作表函数,在 VBA 里可以直接借。第 1 篇我们借过 Round,这一篇把它讲透:怎么借、借哪种、找不到时怎么不弹窗,以及借不到的能力(比如按颜色计数)该怎么写。
前三篇依次解决了参数怎么传、结果怎么铺、字符串怎么拼,都在练「自己写」。今天换个思路:能借就别写。自定义函数不是要重新发明 Excel,而是把 Excel 已有的能力按你的业务口径重新打包一次。
在 VBA 里写 Application.WorksheetFunction.函数名(参数...),就等于在单元格里写同名公式。参数顺序完全一致,区别只是区域要传 Range 对象,字符串要加引号。
Function 查单价(ByVal 品名 AsString, ByVal 价目表 AsRange) AsDouble查单价 = Application.WorksheetFunction.VLookup(品名, 价目表, 2, False)EndFunction
调用时写 =查单价("A4纸", $D$2:$E$50) 即可。把区域、列号、匹配方式这些「每次都要填一遍」的东西收进函数里,同时只需要记品名——这就是自定义函数在业务里的真实价值:封装口径。
不过要提前说清一句:借来的函数依然活在 UDF 的规矩里。它只能读区域、算结果、把值交回当前格,不能顺手给价目表标个颜色,也不能把结果写到别的单元格。这类边界,第 5 篇会一次列全。
这是本篇最值得记住的一点。下面两种写法只差一个单词,但查不到时的表现南辕北辙:
v = Application.WorksheetFunction.VLookup("张三", 区域, 2, False)v = Application.VLookup("张三", 区域, 2, False)
| Application.WorksheetFunction.xxx | On Error Resume Next | |
| Application.xxx | If IsError(v) Then |
推演一下原因:这其实是两层不同的「代理」。WorksheetFunction 是严格代理,把工作表函数原样搬进 VBA,公式报错它就当场抛异常;而直接挂在 Application 下的是宽容代理,会把 #N/A 这类错误值原封不动交回变量,不打断执行。所以两者不是谁对谁错,而是两种错误传递策略。
所以在 UDF 里我更推荐第二种:它不会把「查不到」当成程序崩溃,判断起来也干净。第一种的优势是编辑时有参数智能提示、写错当场知道,适合逻辑严格、宁可报错也不要脏数据的场景。
把上面的结论落成一个真正能交给同事用的函数:查得到就返回值,查不到就返回你指定的提示语(默认「未找到」),绝不弹窗。
Function 安全查找(ByVal 关键字 AsString, ByVal 区域 AsRange, _OptionalByVal 列号 AsLong = 2, _OptionalByVal 找不到时 AsString = "未找到") AsStringDim v AsVariantv = Application.VLookup(关键字, 区域, 列号, False)IfIsError(v) Then安全查找 = 找不到时Else安全查找 = CStr(v)EndIfEndFunction
逐行在做什么
| Optional 列号 = 2 / 找不到时 | |
| Dim v As Variant | |
| Application.VLookup(...) | |
| If IsError(v) Then | |
| CStr(v) |
| =安全查找("A4纸", $D$2:$E$50) | |
| =安全查找("不存在的品", $D$2:$E$50) | |
| =安全查找("A4纸", $D$2:$F$50, 3, "缺货") |
有些事 Excel 函数确实不管——比如按单元格填充色统计。表里用黄底标了待核单据,想数一数有几个,公式无能为力,但 UDF 只要读 .Interior.Color 就行。
Function 按颜色计数(ByVal 区域 AsRange, ByVal 样板 AsRange) AsLongDim c AsRange, n AsLongn = 0ForEach c In 区域.CellsIf c.Interior.Color = 样板.Interior.Color Then n = n + 1Next c按颜色计数 = nEndFunction
把某个黄底单元格当「样板」放在 B2,写 =按颜色计数(A2:A50, $B$2) 就得到黄色单元格个数。用样板格而不是写死颜色编号,是为了改配色时不用改代码——这一点在做模板时特别省心。
逐行在做什么
| ByVal 区域 As Range | |
| For Each c In 区域.Cells | |
| c.Interior.Color | |
| = 样板.Interior.Color | |
| 按颜色计数 = n |
想改成「按颜色求金额」而不是数个数,只要把累加那句换成 合计 = 合计 + c.Offset(0, 1).Value、返回值类型改成 As Double;把 .Interior.Color 换成 .Font.Color 又能统计字体色。同一个骨架能长出好几个工具,这正是自定义函数比固定公式灵活的地方。
一个反直觉的坑:改单元格颜色不会触发重算,因为 Excel 的重算链只认数值变化,不认格式变化。所以标完颜色要按一次 F9 刷新结果;想自动刷新得在函数里加 Application.Volatile,但那会让整表变慢——第 5 篇专门讲这笔账。
多条件求和、条件计数这类,完全没必要自己遍历。把列和条件收进参数,一行就够:
Function 按部门合计(ByVal 部门列 AsRange, ByVal 金额列 AsRange, _ByVal 部门 AsString) AsDouble按部门合计 = Application.WorksheetFunction.SumIfs(金额列, 部门列, 部门)EndFunction
同理,CountIfs 做多条件计数、Max / Min 取极值、Text 做格式化,都能这样借。判断标准很简单:单元格公式能一行解决的,VBA 里就别写循环。
| Application.WorksheetFunction.xxx | ||
| Application.xxx | ||
| SumIfs / CountIfs | ||
| .Interior.Color | ||
| Optional |
到这里,你已经会写「好用」的自定义函数了。最后一篇聊「写得稳」:那些让函数突然失效、把整张表拖卡、或者怎么都调不出来的坑,一次讲清楚。
下一篇 · 05 / 05
六大避坑 + 调试 + 一页纸速查:UDF 到底不能做什么、Volatile 该不该加、大区域为什么卡、函数没法下断点怎么调,附全系列速查表。
你最想让哪个 Excel 函数「被封装」一下?
VLOOKUP、SUMIFS 还是 TEXT?评论区说说你的业务口径 👇 有用点个在看 + 收藏。















































































