夜雨聆风学习资料网

ARTICLE · 1359422

VBA自定义函数丨你写的 80 行,Excel 早就内置了

VBA自定义函数丨你写的 80 行,Excel 早就内置了

VBA 自定义函数 · 深入篇  04 / 05 借力打力

在自定义函数里调用 VLOOKUP / SUMIFS、两种调用方式的报错差异,以及按填充色统计单元格

为了「按客户名查单价」,有人自己写双重循环、逐格比较、还要处理找不到时的提示,一晃 80 行;为了「按部门汇总金额」,又写一个遍历判断加累加。写完发现又慢又容易漏边界。

其实 Excel 那几百个工作表函数,在 VBA 里可以直接借。第 1 篇我们借过 Round,这一篇把它讲透:怎么借、借哪种、找不到时怎么不弹窗,以及借不到的能力(比如按颜色计数)该怎么写。

回顾30 秒,接上前三篇

前三篇依次解决了参数怎么传、结果怎么铺、字符串怎么拼,都在练「自己写」。今天换个思路:能借就别写。自定义函数不是要重新发明 Excel,而是把 Excel 已有的能力按你的业务口径重新打包一次。

01借用的入口:Application.WorksheetFunction

在 VBA 里写 Application.WorksheetFunction.函数名(参数...),就等于在单元格里写同名公式。参数顺序完全一致,区别只是区域要传 Range 对象,字符串要加引号。

Function 查单价(ByVal 品名 AsString, ByVal 价目表 AsRange) AsDouble    查单价 = Application.WorksheetFunction.VLookup(品名, 价目表, 2, False)EndFunction

调用时写 =查单价("A4纸", $D$2:$E$50) 即可。把区域、列号、匹配方式这些「每次都要填一遍」的东西收进函数里,同时只需要记品名——这就是自定义函数在业务里的真实价值:封装口径。

不过要提前说清一句:借来的函数依然活在 UDF 的规矩里。它只能读区域、算结果、把值交回当前格,不能顺手给价目表标个颜色,也不能把结果写到别的单元格。这类边界,第 5 篇会一次列全。

02一个分岔口:带不带 WorksheetFunction,行为完全不同

这是本篇最值得记住的一点。下面两种写法只差一个单词,但查不到时的表现南辕北辙:

v = Application.WorksheetFunction.VLookup("张三", 区域, 2, False)v = Application.VLookup("张三", 区域, 2, False)
写法
查不到时
代码怎么处理
Application.WorksheetFunction.xxx
抛运行时错误 1004,直接中断
On Error Resume Next
 + 检查 Err.Number
Application.xxx
(不带中间那层)
返回错误值 #N/A,不中断
If IsError(v) Then
 判断即可

推演一下原因:这其实是两层不同的「代理」。WorksheetFunction 是严格代理,把工作表函数原样搬进 VBA,公式报错它就当场抛异常;而直接挂在 Application 下的是宽容代理,会把 #N/A 这类错误值原封不动交回变量,不打断执行。所以两者不是谁对谁错,而是两种错误传递策略。

所以在 UDF 里我更推荐第二种:它不会把「查不到」当成程序崩溃,判断起来也干净。第一种的优势是编辑时有参数智能提示、写错当场知道,适合逻辑严格、宁可报错也不要脏数据的场景。

案例带默认值的安全查找

把上面的结论落成一个真正能交给同事用的函数:查得到就返回值,查不到就返回你指定的提示语(默认「未找到」),绝不弹窗。

Function 安全查找(ByVal 关键字 AsString, ByVal 区域 AsRange, _                 OptionalByVal 列号 AsLong = 2, _                 OptionalByVal 找不到时 AsString = "未找到") AsString    Dim v AsVariant    v = Application.VLookup(关键字, 区域, 列号, False)    IfIsError(v) Then        安全查找 = 找不到时    Else        安全查找 = CStr(v)    EndIfEndFunction

逐行在做什么

代码行
作用
Optional 列号 = 2 / 找不到时
第 1 篇的四板斧:常用值给默认值,特殊场景才多传参数。
Dim v As Variant
必须声明成 Variant——因为它可能装正常值,也可能装错误值,String / Double 都装不下 #N/A。
Application.VLookup(...)
不带 WorksheetFunction,查不到时安静地返回错误值。
If IsError(v) Then
专门识别错误值。写成 v = "" 之类的比较会对错误值抛「类型不匹配」。
CStr(v)
确认没问题后再转字符串交回单元格。
在单元格里写
得到
=安全查找("A4纸", $D$2:$E$50)
对应的单价
=安全查找("不存在的品", $D$2:$E$50)
未找到
=安全查找("A4纸", $D$2:$F$50, 3, "缺货")
取第 3 列,查不到显示「缺货」
03借不到的能力:自己操作 Range 对象

有些事 Excel 函数确实不管——比如按单元格填充色统计。表里用黄底标了待核单据,想数一数有几个,公式无能为力,但 UDF 只要读 .Interior.Color 就行。

Function 按颜色计数(ByVal 区域 AsRange, ByVal 样板 AsRange) AsLong    Dim c AsRange, n AsLong    n = 0    ForEach c In 区域.Cells        If c.Interior.Color = 样板.Interior.Color Then n = n + 1    Next c    按颜色计数 = nEndFunction

把某个黄底单元格当「样板」放在 B2,写 =按颜色计数(A2:A50, $B$2) 就得到黄色单元格个数。用样板格而不是写死颜色编号,是为了改配色时不用改代码——这一点在做模板时特别省心。

逐行在做什么

代码行
作用
ByVal 区域 As Range
参数声明成 Range,才能拿到 .Cells、.Interior 这些对象成员。
For Each c In 区域.Cells
逐格遍历。.Cells 保证传多列区域也按单元格走。
c.Interior.Color
取填充色的 Long 编号;同理 .Font.Color 取字体色。
= 样板.Interior.Color
和样板比,而不是和数字比,用户换配色时函数不受影响。
按颜色计数 = n
结果是个数,所以函数声明为 As Long。

想改成「按颜色求金额」而不是数个数,只要把累加那句换成 合计 = 合计 + c.Offset(0, 1).Value、返回值类型改成 As Double;把 .Interior.Color 换成 .Font.Color 又能统计字体色。同一个骨架能长出好几个工具,这正是自定义函数比固定公式灵活的地方。

一个反直觉的坑:改单元格颜色不会触发重算,因为 Excel 的重算链只认数值变化,不认格式变化。所以标完颜色要按一次 F9 刷新结果;想自动刷新得在函数里加 Application.Volatile,但那会让整表变慢——第 5 篇专门讲这笔账。

04顺手再借两个高频函数

多条件求和、条件计数这类,完全没必要自己遍历。把列和条件收进参数,一行就够:

Function 按部门合计(ByVal 部门列 AsRange, ByVal 金额列 AsRange, _                   ByVal 部门 AsString) AsDouble    按部门合计 = Application.WorksheetFunction.SumIfs(金额列, 部门列, 部门)EndFunction

同理,CountIfs 做多条件计数、Max / Min 取极值、Text 做格式化,都能这样借。判断标准很简单:单元格公式能一行解决的,VBA 里就别写循环。

收尾一页纸收好
需求
推荐写法
注意
借用工作表函数
Application.WorksheetFunction.xxx
查不到会抛错,要 On Error
希望查不到不中断
Application.xxx
 + IsError
接收变量声明为 Variant
多条件汇总
SumIfs / CountIfs
区域参数用 As Range
按格式统计
.Interior.Color
 / .Font.Color 遍历
改色不触发重算,需 F9
封装业务口径
Optional
 给默认列号/提示语
见第 1 篇

到这里,你已经会写「好用」的自定义函数了。最后一篇聊「写得稳」:那些让函数突然失效、把整张表拖卡、或者怎么都调不出来的坑,一次讲清楚。

下一篇 · 05 / 05

六大避坑 + 调试 + 一页纸速查:UDF 到底不能做什么、Volatile 该不该加、大区域为什么卡、函数没法下断点怎么调,附全系列速查表。

你最想让哪个 Excel 函数「被封装」一下?

VLOOKUP、SUMIFS 还是 TEXT?评论区说说你的业务口径 👇 有用点个在看 + 收藏。

第4篇_借工作表函数.xlsm

回首页,搜索你想要的文档

在首页搜索框输入关键词,快速定位资料

文库首页搜索指引 立即搜索

相关学习资料