如何使用 Excel VBA 确定整个工作簿中的任何单元格中是否存在错误

How do i determine if an error is in any cell in the entire workbook with Excel VBA

提问by Benny

Q: How do i determine if an error is in any cell in the entire workbook with Excel VBA?

问：如何使用 Excel VBA 确定整个工作簿中的任何单元格中是否存在错误？

Normally errors will be divide by 0 or #value errors, but this list is not exhaustive (or is it? - i don't know if more exist)

通常错误将除以 0 或 #value 错误，但此列表并不详尽（或者是吗？ - 我不知道是否存在更多错误）

Is there a way to determine if a cell contains an error then to skip over further processing in my script without spitting out a debug/warning/error message.

有没有办法确定一个单元格是否包含错误，然后跳过我的脚本中的进一步处理，而不会吐出调试/警告/错误消息。

something such like

诸如此类的东西

          if value in current.Workbook.cell is error then go to <jump>
           OR
          if value in old.Workbook.cell is error then go to <jump>

where jumpis a marker at the end of an if statmenet but within a loop.

wherejump是 if statmenet 末尾但在循环内的标记。

the script compares values between two workbooks and updates the current workbook with colours to show difference.

该脚本比较两个工作簿之间的值，并用颜色更新当前工作簿以显示差异。

I have no VBA experience at all. but i get the gist of the script i have been given.

我根本没有 VBA 经验。但我明白了我得到的剧本的要点。

thank you kindly.

非常感谢你。

回答by Rachel Hettinger

You can skip cells with errors by using the VarTypefunction. For example:

您可以使用该VarType功能跳过有错误的单元格。例如：

If VarType(ActiveCell.Value) <> vbError Then
    ' do something
End If

The VarTypefunction is also very useful to validate the data type. For example if your code expects a date value but sometimes encounters text, you can use this function to vet the data and handle exceptions gracefully.

该VarType函数对于验证数据类型也非常有用。例如，如果您的代码需要一个日期值但有时会遇到文本，您可以使用此函数来数据并优雅地处理异常。

回答by SkipVought

Here's an snippet of code that records in the Immediate Window, the worksheet name, cell address and formula, where there is a spreadsheet formula error...

这是在立即窗口中记录的一段代码，工作表名称，单元格地址和公式，其中存在电子表格公式错误...

Dim ws As Worksheet, r As Range

For Each ws In Worksheets
    For Each r In ws.UsedRange
        If IsError(r.Value) Then
            Debug.Print r.Parent.Name, r.Address, r.Formula
        End If
    Next
Next

回答by brettdj

Given your initial question was how do you detect errors in any cell of the workbook with VBA then you should be looking for a very efficient approach - looking at each cell is very expensive time wise!

鉴于您最初的问题是如何使用 VBA 检测工作簿的任何单元格中的错误，那么您应该寻找一种非常有效的方法 - 查看每个单元格在时间上非常昂贵！

Two options for this are:

对此有两个选择：

1. Use Excel's SpecialCells to shortcut the process
2. use my Mappit! addinwhich is configured to report on spreadsheet errors

1. 使用 Excel 的 SpecialCells 来简化流程
2. 使用我的Mappit！配置为报告电子表格错误的插件

For SpecialCells please see the code below. This takes advantage of the ready-made collection of errors that exist as formulas

对于 SpecialCells，请参阅下面的代码。这利用了作为公式存在的现成的错误集合

Please that that constantsalso have an errors collection, so if you have copied then run a paste special as value on a formula error then you would need to use Set rng1 = ws.Cells.SpecialCells(xlConstants, xlErrors)to detect these

请注意该常量也有一个错误集合，因此如果您已复制然后在公式错误上运行特殊粘贴作为值，那么您将需要使用Set rng1 = ws.Cells.SpecialCells(xlConstants, xlErrors)来检测这些

You can use also detect SpecialCells manually by

您也可以使用手动检测 SpecialCells

• Select all cells in the area of interest
• Press F5
• Click Special
• select 'Errors' under 'Formulas' (or 'Constants')

• 选择感兴趣区域中的所有单元格
• 按F5
• 单击特殊
• 在“公式”（或“常量”）下选择“错误”

Please be aware that prior to xl2010 there is a limit of 8192 areas that SpecialCells can handle

请注意，在 xl2010 之前，SpecialCells 可以处理的区域限制为8192

Sub ErrorList()
    Dim ws As Worksheet
    Dim rng1 As Range
    Dim strOut As String
    For Each ws In ActiveWorkbook.Sheets
        Set rng1 = Nothing
        On Error Resume Next
        Set rng1 = ws.Cells.SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rng1 Is Nothing Then strOut = strOut & (ws.Name & " has " & rng1.Cells.Count & " errors" & vbNewLine)
    Next ws
    If Len(strOut) > 0 Then
        MsgBox "Error List:" & vbNewLine & strOut
    Else
        MsgBox "No Errors", vbInformation
    End If
End Sub

回答by Neil Barnwell

You can use the IsError()function from VBA as well as as a formula in a worksheet.

您可以使用IsError()VBA 中的函数以及工作表中的公式。

See http://vbadud.blogspot.com/2007/04/using-vbas-iserror-function.htmlfor an example.

有关示例，请参见http://vbadud.blogspot.com/2007/04/using-vbas-iserror-function.html。

回答by aevanko

There's another way to do handle this: add On Error Resume Nextinto your code (usually just put it before the loop).

还有另一种方法来处理这个问题：添加On Error Resume Next到你的代码中（通常只是把它放在循环之前）。

If a cell is an error, it'll just skip it and move to the next element in the loop :)

如果一个单元格是一个错误，它会跳过它并移动到循环中的下一个元素:)