excel - 无法读取设置范围变量的对象属性

Question

我正在用 vba 开发工作簿；其中的工作表可以包含不同数量的命名“CheckCells”，它们充当“布尔”下拉菜单（“是”/“否”）来操作同一工作表中的其他单元格。

我正在尝试开发一个可以在单元格公式中调用的公共函数宏。其目的是将所有这些 CheckCells “折叠”成一个 Long-type 输出——输出将等于我从工作簿中的特殊“存档”工作表中读取的列索引；“存档”表将充当某些值的永久“内存”，因为它们对工作簿功能至关重要，但会在工作簿打开/关闭时丢失。

以下是我需要帮助的代码的缩短版本。如注释的 Debug.Print 行所示，似乎我尝试以任何方式将 Range 类型变量变暗，然后将其设置为等于单元格范围，大多数使用该变量的对象属性读取的尝试都会抛出错误结果，例如“不能获取属性...”、“对象定义的错误...”或“应用程序定义的错误...”。“VersionCheckCell”确实是这里ActiveSheet的一个命名单元。

Public Function CollapseRun() As Long

Dim ActSht As Worksheet
Set ActSht = ThisWorkbook.ActiveSheet

'Variables to get the range of cells to loop through when finding VersionCount.

Dim intVisRows As Long: intVisRows = 1
Dim intVisCols As Long: intVisCols = 1

'[Code to evaluate inVisCols and intVisRows omitted to shorten post...]

Dim EndCell As Range
Set EndCell = ActSht.Cells(intVisRows, intVisCols)

'Debug.Print [VersionCheckCell].Name.Name 'This works. If explicitly giving valid ranges, _
I can read its properties like normal.

'Debug.Print EndCell.Name.Name 'This fails. When I define a _
Range variable, then Set it as some cells, I can't read its object properties.

Dim VisibleRange As Range
Set VisibleRange = ActSht.Range(ActSht.Cells(1, 1), EndCell) 'Cell Range to 
loop through.

Dim intCzCells As Integer: intCzCells = 0
Dim Cell As Range
Dim arrCz() As Variant

'This is the Application-defined error-throwing loop.
For Each Cell In VisibleRange

    If InStr(Cell.Name.Name, "CheckCell") <> 0 Then 'Checks cell's name for "CheckCell". 
        intCzCells = intCzCells + 1

        arrCz(intCzCells) = Cell.Address 'Storing addresses to later check values.

    End If

Next Cell

'[More code after this loop to determine CollapseRun value...]

我在询问之前进行了研究，发现许多其他带有标签的问题，例如“执行时应用程序定义的错误...”等。其中许多问题/解决方案源于 QA 不正确地设置了正在抛出错误的变量读。我已经盯着并修改了这段代码几个小时，无法弄清楚我是如何不正确地设置 VisibleRange 或 EndCell 变量的，所以我倾向于相信其他地方是错误的，但我希望这里有人可以轻推我在正确的方向。

我在使用 Excel 2016 的 Windows 10 上。

（编辑：插入 intCzCells 初始化为 0 的代码的缺失部分。

（编辑 2：修复了评论中指出的不必要的范围评估。）

标签： excelvba

Answer 1

正如 John Coleman 在评论中指出的那样，主要问题是，.Name.Name如果范围不是命名单元格，则会引发错误。

解决此问题的一种方法是在循环中使用解决方法错误处理（我还修复了代码以实际将地址正确添加到数组中）。

For Each Cell In VisibleRange

    On Error GoTo NoName

    If InStr(Cell.Name.Name, "CheckCell") <> 0 Then

        intCzCells = intCzCells + 1

        ReDim Preserve arrCz(intCzCells - 1) 'Array indexed from 0.
        arrCz(intCzCells - 1) = Cell.Address

    End If

NoName:

    Resume NextIteration

NextIteration:

Next Cell