作者：绿色风
分类：
时间：2022-08-17
浏览：161
楼主
kevinchengcw |
Q: 如何用VBA代码查找工作簿中引用错误的单元格并列表显示？
A: 代码如下：
- Sub test()
- Dim Ws As Worksheet
- Dim Rng As Range
- Dim Str$, Str2$
- Dim Dic, Arr
- Set Dic = CreateObject("scripting.dictionary") '创建字典项目，用于储存找到的出错信息的位置
- For Each Ws In Worksheets '循环工作簿中各表
- With Ws '在当前的循环表中进行操作
- For Each Rng In .UsedRange '循环表中使用区域的各个单元格
- Str = Rng.Formula '将单元格的公式内容赋值给字符串变量
- If InStr(Str, "#REF") > 0 Then '如果公式中包含引用出错的关键字“#REF”
- Str2 = .Name & vbTab & Rng.Address & vbTab & Str '创建对应的key值（工作表名，单元格地址，公式内容），并添加到字典中
- Dic.Add Str2, ""
- End If
- Next Rng
- End With
- Next Ws
- If Dic.Count > 0 Then '当字典不为空时执行下述操作
- Worksheets.Add '添加一个新的工作表用于列表收集到的出错的引用信息
- Arr = Dic.keys '将字典的keys赋值给数组，以便提取内容
- Cells(1, 1).Resize(1, 3) = Split("工作表名,单元格位置,单元格公式内容", ",") '写入工作表信息的标题行
- Cells.NumberFormatLocal = "@" '设定工作表中的单元格格式为文本（方便显示公式）
- For n = LBound(Arr) To UBound(Arr) '循环数组各项，并赋值到对应单元格中
- Cells(n + 2, 1).Resize(1, 3) = Split(Arr(n), vbTab)
- Next n
- Columns.AutoFit '调整一下列宽
- Set Dic = Nothing '清空字典项目
- MsgBox "查找完成" '显示提示信息
- Exit Sub '退出程序
- End If
- Set Dic = Nothing '如果字典项为空（即未找到出错的项目时），清空字典项目，并显示提示信息
- MsgBox "查找完成，未发现工作表引用出错项目。"
- End Sub
喜欢DIY的还可以加上链接功能或提供其他错误的查找功能。 |
2楼
yizhbi |
学习一下，谢谢 |
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，
经与ExcelTip.Net站长Apolloh商议并征得其同意，
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，
供有需要的人士查询使用，也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议，
可与本人沟通提出，或于本站点留言，我们会尽快处理。
在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一