Object required error when looping through cells in selection

Question

I am looping through some cells, in a vertical selection, in Excel, and then passing that cell as a parameter to a procedure.

I have done it this way, so I don't have the contents of ProcessCells twice, in the code, once for the while loop, and the second time in the For loop.

If I try and get the value of the cell written out, in the for loop, it works. If I put the contents of the ProcessCells procedure in the for loop, it also works.

But if I try to pass it as a parameter, into ProcessCells, I am getting an error

'Object Required'

Here is the code, if you want to check it out:

Sub loopThroughCells()

Dim c As Range
Dim autoSelect As String
Dim X, Y As Integer

autoSelect = Cells(3, 2).Value

If StrComp(autoSelect, "Y") = 0 Then
    Y = 5
    X = 4
    While Not IsEmpty(Cells(Y, X).Value)
        ProcessCells (Cells(Y, X))
        Y = Y + 1
    Wend
Else
    For Each c In Selection
        ProcessCells (c)
    Next c
End If
End Sub

Sub ProcessCells(ce As Range)
End Sub

How is

Cells(n,m)

different from

c In Selection

?

The error happens in the For loop, but it doesn't happen in the while loop.

It should probably be `For Each c In Selection.Cells` (and you should avoid using `Select` as much as possible. Also, see this doc : http://stackoverflow.com/documentation/vba/7363/passing-arguments-byref-or-byval#t=201701181030371271291 — R3uK, Jan 18 '17 at 10:33
Do you alter the cells in `ProcessCells`? Because you can't change the value of an element in a `For Each` loop (I know, it is a pain...) — R3uK, Jan 18 '17 at 10:41
Nope, and even if the ProcessCells procedure is empty, I get the same error. — Wilhelm Sorban, Jan 18 '17 at 10:43

score 3 · Accepted Answer · edited May 23 '17 at 11:45

3

Here is how you should do it:

Option Explicit

Sub TestMe()

    Dim c   As Range

    For Each c In Selection
        Call ProcessCells(c)
    Next c
End Sub


Sub ProcessCells(ce As Range)
End Sub

You should refer with call, because you have an argument in parenthesis. Or like this, if you do not like the call:

Option Explicit

Sub TestMe()

    Dim c   As Range

    For Each c In Selection
         ProcessCells c
    Next c
End Sub


Sub ProcessCells(ce As Range)
End Sub

Plus a small edition of your code. Make your declarations like this:

Dim X as Long, Y As long

In your code X is declared as a variant, and integer is slower and smaller than long - Why Use Integer Instead of Long?

Here is some good explanation when to put the argument in parenthesis and when to use the call - How do I call a VBA Function into a Sub Procedure

edited May 23 '17 at 11:45

Community

1
1

answered Jan 18 '17 at 10:43

Vityata

42,633
8
55
100

2

Thanks, using 'Call' fixed it. But you are mentioning that I need to use 'Call' because I have an argument in parenthesis. Isn't it the same case, in the While loop? – Wilhelm Sorban Jan 18 '17 at 10:47
No. In the while loop you are using an XL built in function - IsEmpty(). It takes arguments in parenthesis. Here is a good reference about it - http://stackoverflow.com/questions/1072075/how-do-i-call-a-vba-function-into-a-sub-procedure – Vityata Jan 18 '17 at 10:55
Nono, I meant I am calling the same procedure, that I also call in the For loop: 'ProcessCells' But instead of the looped cell, I am passing the built in 'Cells' parameter. – Wilhelm Sorban Jan 18 '17 at 11:05
1

I see now. I suppose `Cell` is an object https://msdn.microsoft.com/en-us/library/office/aa221403(v=office.11).aspx , thus the VBA treats it with more respect (first-class citizen, etc.), and concerning the `c` range, it is part of iEnumerable, thus the VBA realizes later about it (although it is declared in the top). – Vityata Jan 18 '17 at 11:18

Object required error when looping through cells in selection

1 Answers1