VBA：如果用户删除单元格，Range对象会发生什么？ [英] VBA: What happens to Range objects if user deletes cells?

问题描述

假设我在 vba 中有一些模块范围的变量 r 。假设在某个时刻，我存储一个Range对象（例如活动单元格）。现在我的问题：如果用户删除单元格（单元格，而不仅仅是其值）， r 的值会发生什么？

Suppose I have some module in vba with some variable r of type Range. Suppose that, at some point, I store a Range object there (e.g. the active cell). Now my question: What happens to the value of r if the user deletes the cell (the cell, not only its value)?

我试图在VBA中得出结论，但没有成功。结果很奇怪 r 不是没有任何，报告的价值 r 为$ code> Range ，但如果我尝试在调试器窗口中查看其属性，则每个属性的值都将被报告为对象必需。

I tried to figure this out in VBA, but without success. The result is strange. r is not Nothing, the value of r is reported to be of type Range, but if I try to look at its properties in the debugger window, each property's value is reported as "object required".

我如何以编程方式确定变量 r 是否处于此状态？

How can I, programmatically, determine whether variable r is in this state or not?

我可以这样做而不会产生错误并捕获它吗？

推荐答案

！我以前从来没有想过，但是我认为这个功能会识别一个被初始化的范围 - 不是Nothing，但现在处于Object Required状态，因为它的单元被删除：

Nice question! I've never thought about this before, but this function will, I think, identify a range that was initialzed - is not Nothing - but is now in the "Object Required" state because its cells were deleted:

Function RangeWasDeclaredAndEntirelyDeleted(r As Range) As Boolean
Dim TestAddress As String

If r Is Nothing Then
    Exit Function
End If
On Error Resume Next
TestAddress = r.Address
If Err.Number = 424 Then    'object required
    RangeWasDeclaredAndEntirelyDeleted = True
End If
End Function

您可以测试如下：

Sub test()
Dim r As Range

Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
Set r = ActiveSheet.Range("A1")
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
r.EntireRow.Delete
Debug.Print RangeWasDeclaredAndEntirelyDeleted(r)
End Sub

这篇关于VBA：如果用户删除单元格，Range对象会发生什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！