Unable to read object properties of Set Range variables

Question

I am developing a workbook with vba; in it are worksheets that can contain a varying amount of named "CheckCells" which act as "boolean" dropdowns ("Yes"/"No") to manipulate other cells within the same worksheet.

I'm trying to develop a Public Function macro which can be called within a formula of a cell. Its purpose is to "collapse" all of these CheckCells into a single Long-type output -- the output will equal the column index from which I read off of special "archive" worksheets within the workbook; the "archive" sheets will act as permanent "memory" for certain values, as they are critical to the workbook function but would be lost upon workbook open/close.

The following is a shortened version of the code I need help with. As indicated by commented Debug.Print lines, it appears that any way I try to Dim a Range-type variable, then Set it to equal a range of cells, most attempts at reading using that variable's object properties throw errors results such as "Cannot get property...", "Object-defined error...", or "Application-defined error...". "VersionCheckCell" is indeed a named cell of the ActiveSheet here.

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...]

I did research before asking and found many other questions with labels such as "Application-defined error when doing ...", etc. The problems/solutions of many of these stemmed from the QA improperly Setting the variable which was throwing errors when being read. I've stared and tinkered with this code for several hours and cannot figure out how I might be improperly setting the VisibleRange or the EndCell variables, so I'm inclined to believe something else is wrong, but I'm hoping someone here can nudge me in the right direction.

I'm on Windows 10 using Excel 2016.

(Edit: Insert missing portion of code where intCzCells was initialized as 0.

(Edit 2: Fixed unnecessary range evaluation as pointed out in a comment.)

Okay, sure. I tested that line with just Debug.Print EndCell.Name.Name but it still throws the error. To answer your edit to your comment, .Name.Name is meant to access the cell's name. As stated in the commented Debug.Print line above, it successfully gives the name "VersionCheckCell". — MidgeOnSO
No, Debug.Print EndCell.Address outputs $AE$66 for this worksheet I tested the code in. — MidgeOnSO
The problem is that if EndCell is not a named cell, then EndCell.Name hence EndCell.Name.Name will throw an error. You might need to use some error trapping. — John Coleman
Oh, I see. I incorrectly assumed that by default the names would be the address (so cell A1, I thought, would be named "A1", etc.). I have seen elsewhere that there are ways to check if a cell has a name, I'll update if that works when implemented. — MidgeOnSO

MidgeOnSO MidgeOnSO · Accepted Answer · 2018-10-06T23:16:05

The main issue, as pointed out by John Coleman in comments, was that .Name.Name would throw errors if the range wasn't a named cell.

One way to resolve this issue is to use workaround error handling within the loop (I also fixed the code to actually add the addresses to the array properly).

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

Unable to read object properties of Set Range variables

1 Answers