在Excel VBA中，如何在不引发运行时错误424的情况下测试Excel.Range对象变量是否丢失其引用? [英] In Excel VBA, how can I test if an Excel.Range object variable loses its reference without raising runtime error 424..?

问题描述

在Excel VBA中，如果变量为Excel.Range，并且其引用的范围已删除，则它将丢失其引用.任何尝试访问变量的尝试都会导致运行时错误424:所需对象.

In Excel VBA, if a variable is Excel.Range, and the range it refers to is deleted, it loses its reference. Any attempt to access the variable results in Runtime Error 424: object required.

Dim rng As Range
Set rng = Sheet1Range("A1")
Sheet1.Rows(1).Delete       'Range has been deleted.
Debug.Print rng.Address()   'Any access attempt now raises runtime error 424.

是否有一种方法可以在没有错误处理程序的情况下测试丢失参考"的状态??

Is there a way to test for this state of "lost reference" without an error handler..?

测试 Nothing ，Vartype()和Typename()都没有用，因为变量仍然是Range.我在对象浏览器中逐字阅读了Excel.Application的全部内容，但未发现任何内容.也许有些东西我在忽略..?例如来自Excel史前版本的那些奇怪的残留函数之一，例如ExecuteExcel4Macro()..

Testing Nothing, Vartype(), and Typename() were all not useful because the variable is still a Range. I literally read through all of Excel.Application in the Object browser, but found nothing. Perhaps there's something I'm overlooking..? Such as one of those strange vestigial functions from prehistoric versions of Excel, like ExecuteExcel4Macro()..?

我已经在Google上搜索了该问题的答案，但没有发现任何帮助.

I've searched Google for the answer to this question, but didn't find anything helpful.

有些人问我为什么要避免错误处理程序.这是我正常的编程哲学，有以下几个原因:

Some have asked why I'm trying to avoid an error handler. This is my normal programming philosophy for a couple reasons:

• 我确实认识到有时错误处理程序是最快的方法，还是唯一的方法.但这不是最优雅的方式.看来，好吧……对我来说是粗鲁的.这就像粉刷栅栏和画猫的画像之间的区别.=-)
• 我避免错误处理程序的另一个原因是教育.很多时候，当寻找替代方案时，我会发现属性，过程，对象，甚至是我以前从未了解过的整个库.这样做的话，我发现有更多的防弹衣可以用来防弹我的代码.

推荐答案

虽然这不是检查是否被自己删除的好方法，但这里的方法应该可以解决该问题.我认为错误处理可能是您最好的方法.

Here's an approach that should be able to workaround the issue, although it isn't a great solution for checking if it was removed by itself. I think error handling is probably your best approach.

Sub Example()
    Dim foo1 As Range
    Dim foo2 As Range
    Dim foo3 As Range
    Dim numberOfCells As Long

    Set foo1 = Sheet1.Range("A1")
    Set foo2 = foo1.Offset(1, 0) 'Get the next row, ensure this cell exists after row deletion!
    Set foo3 = Union(foo1, foo2)
    numberOfCells = foo3.Cells.Count

    Debug.Print "There are " & numberOfCells & " cells before deletion"
    Sheet1.Rows(1).Delete

    Debug.Print "There are now " & foo3.Cells.Count & " cells"

    If foo3.Cells.Count <> numberOfCells Then
        Debug.Print "One of the cells was deleted!"
    Else
        Debug.Print "All cells still exist"
    End If
End Sub

此外，这是一种面向函数的方法，可能是添加到您的代码库中的一种更好的方法.再次，这不是理想的选择，但是它不需要错误处理程序.

Also, here is a more function oriented approach which may be a slightly better approach to add to your codebase. Again, not ideal, but it should not require an error handler.

Private getRange As Range

Sub Example()
    Dim foo         As Range
    Dim cellCount   As Long

    Set foo = Sheet1.Range("A1")
    cellCount = GetCellCountInUnion(foo)
    Sheet1.Rows(1).Delete

    If Not cellCount = getRange.Cells.Count Then
        Debug.Print "The cell was removed!"
    Else
        Debug.Print "The cell still exists!"
    End If

End Sub

Private Function GetCellCountInUnion(MyRange As Range) As Long
    Set getRange = Union(MyRange, MyRange.Parent.Range("A50000")) ‘second cell in union is just a cell that should exist
    GetCellCountInUnion = getRange.Cells.Count
End Function

这篇关于在Excel VBA中，如何在不引发运行时错误424的情况下测试Excel.Range对象变量是否丢失其引用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！