如何测试Excel中的范围内有单元格？ [英] How do you test that a Range in Excel has cells in it?

问题描述

我在Worksheet_Change事件中发现了Excel / VBA中的问题。我需要将Target.Dependents分配给一个Range，但是如果没有依赖关系，它会引发一个错误。我试过测试Target.Dependents.Cells.Count，但是没有工作。任何想法？

I've found a problem in Excel/VBA in the Worksheet_Change Event. I need to assign Target.Dependents to a Range, but if it has no dependents it brings up an error. I tried testing Target.Dependents.Cells.Count but that didn't work. Any Ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 OR Target.Dependents.Cells.Count = 0 Then Exit Sub

Dim TestRange As Range

Set TestRange = Target.Dependents

我也尝试过Target.Dependents Is Nothing。

I've also tried "Target.Dependents Is Nothing".

推荐答案

简而言之，没有办法测试依赖关系而不引发错误，因为属性本​​身设置为在访问时引发错误，并且没有任何错误。我不喜欢这个设计，但没有办法阻止它而不会出现错误。 AFAIK这是关于你将能够做的最好的。

Short answer, there is no way to test for dependents without raising an error, as the property itself is set to raise an error if accessed and there aren't any. I dislike the design but there is no way to prevent it without suppressing errors. AFAIK this is about the best you are going to be able to do with it.

Sub Example()
    Dim rng As Excel.Range
    Set rng = Excel.Selection
    If HasDependents(rng) Then
        MsgBox rng.Dependents.Count & " dependancies found."
    Else
        MsgBox "No dependancies found."
    End If
End Sub

Public Function HasDependents(ByVal target As Excel.Range) As Boolean
    On Error Resume Next
    HasDependents = target.Dependents.Count
End Function

说明，如果没有依赖关系引发错误， HasDependents的值与default类型保持不变，这是false，因此返回false。如果是依赖关系，则计数值永远不会为零。所有非零整数都将转换为true，所以当count被分配为返回值时，返回true。这很接近你已经在使用。

Explanation, if there are no dependents an error is raised and the value of HasDependents stays unchanged from the type default,which is false, thus false is returned. If there are dependents, the count value will never be zero. All non-zero integers convert to true, so when count is assigned as the return value, true is returned. It's pretty close to what you are already using.

这篇关于如何测试Excel中的范围内有单元格？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！