错误检查和跟踪空单元格 [英] Error Checking and Trace Empty Cells

问题描述

在Excel中，可以找到引用空单元格的公式。例如

A1 = 1

B1 =空

C1 = .5

D1 = A1 + B1 + C1

D1将正确计算值为1.5
但是，错误检查将识别出有错误在公式D1中，它可以向该单元格绘制一个箭头。

我需要提醒用户他们是否有空单元格的引用。

当我尝试使用该功能记录自己时，我得到这个

  With Application.ErrorCheckingOptions 
 .EvaluateToError = False 
 .TextDate = False 
 .NumberAsText = False 
 .InconsistentFormula = False 
 .OmittedCells = False 
。 UnlockedFormulaCells = False 
 .ListDataValidation = False 
 .InconsistentTableFormula = False 
结束
 
 ActiveSheet.ClearArrows 
  

仅检查空单元格的选项设置正确，但Trace功能的实际执行完全被忽略。有什么办法可以自动发生这种情况，然后检查测试结果？

功能是公式选项卡错误检查公式引用空单元格空白单元格

解决方案

这是一个VBA方法来检查引用空单元格的公式。这样不仅会告诉你哪个公式是哪个单元格是空的。

为了测试这个，在Sheet1中，单元格A1将这个公式

  = F1 + G1 + H1 
  

保持H1空，填入F1和G1

在单元格A5中，放置此公式

  = A1 * D5 
  

保持单元格D5为空。

现在将此代码粘贴到一个模块中。

  Option Explicit 
 
子样本（）
 Dim ws As Worksheet 
 Dim RngFormulas As Range，fCell As Range，_ 
 DPrcd As Range，aCell As Range 
 
 Set ws = Sheets（Sheet1）
 
带有ws 
在错误恢复下一步
设置RngFormulas = .Cells.SpecialCells（xlCellTypeFormulas）
错误GoTo 0 
 
如果没有RngFormulas是没有
对于每个fCell在RngFormulas 
在错误恢复下一个
设置DPrcd = fCell.DirectPrecedents 
错误GoTo 0 
如果不是DPrcd是没有
对于每个aCell在DPrcd 
如果IsEmpty（aCell.Value）然后
 Debug.Print aCell.Address& in& _ 
 fCell.Formula& 是空的
结束如果
下一个
结束如果
下一个
结束如果
结束
结束Sub 
  

当您运行宏时，您将在立即窗口中看到输出。

SNAPSHOT

In Excel it's possible to locate formulas that have references to empty cells.

For example

A1 = 1

B1 = Empty

C1 = .5

D1 = A1 + B1 + C1

D1 will correctly calculate the value to be 1.5 However, error checking will identify that there is an error in the formula D1 and it can draw an arrow to this cell.

I need to alert the user if they have a reference to an empty cell.

When I try to record myself using the feature I get this

With Application.ErrorCheckingOptions
    .EvaluateToError = False
    .TextDate = False
    .NumberAsText = False
    .InconsistentFormula = False
    .OmittedCells = False
    .UnlockedFormulaCells = False
    .ListDataValidation = False
    .InconsistentTableFormula = False
End With

ActiveSheet.ClearArrows

The options for only checking for empty cells are setup correctly but the actual execution of the "Trace" function is completely ignored. Is there any way to cause this to happen automatically and then check the result of the test?

The function is "Formulas Tab" "Error Checking" "Formulas referring to empty cells" "Trace Empty Cell"

解决方案

Here is a VBA method to check Formulas referring to empty cells. This will not only tell you which formula it is but also which cell is empty.

To test this, in Sheet1, Cell A1 put this formula

=F1+G1+H1

Keep H1 Empty and fill F1 and G1

In Cell A5, put this formula

=A1*D5

Keep the Cell D5 empty.

Now paste this code in a module.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim RngFormulas As Range, fCell As Range, _
    DPrcd As Range, aCell As Range

    Set ws = Sheets("Sheet1")

    With ws
        On Error Resume Next
        Set RngFormulas = .Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If Not RngFormulas Is Nothing Then
            For Each fCell In RngFormulas
                On Error Resume Next
                Set DPrcd = fCell.DirectPrecedents
                On Error GoTo 0
                If Not DPrcd Is Nothing Then
                    For Each aCell In DPrcd
                        If IsEmpty(aCell.Value) Then
                            Debug.Print aCell.Address & " in " & _
                            fCell.Formula & " is empty"
                        End If
                    Next
                End If
            Next
        End If
    End With
End Sub

When you run the macro, you will see the output in the immediate window.

SNAPSHOT

这篇关于错误检查和跟踪空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！