excel error 424 object required when calling sub

Question

I spent hours looking into my code but I can't figure out what is wrong.

I keep getting this 424 error, for no obvious reason to me. here is my piece of code.

I just want to give row as a parameter to mySub to process it.

Option Explicit

Private Sub mySub(row As Range)
    Debug.Print ("mySub") ' not printed
    Dim line As Collection

    Set line = New Collection
End Sub

Private Sub CalcClients()
    Dim data_sheet As Worksheet
    Dim last_row As Long
    Dim last_col As String
    Dim line As Long
    Dim cols As Range
    Dim row As Range

    Set data_sheet = Worksheets("DATA")
    Let last_row = data_sheet.Range("A1").End(xlDown).row
    Let last_col = Col_Letter(data_sheet.Range("A1").End(xlToRight).column)
    Set cols = data_sheet.Range("A2:" & last_col & last_row)

    For Each row In cols.Rows
        ' type_name(row) => "Range"
        Debug.Print (row.Cells(1, 1).Value) '=> THEEXPECTEDVALUE
        mySub (row) ' Error 424, object required
    Next

End Sub

Answer 1

Here is the reason for the observed behavior.

Your subroutine, mySub() takes one parameter as a range type.

This means you must pass it a range.

In your code you set an object variable row to a series of ranges, one at a time.

To use that range variable row as a parameter for mySub the syntax should be like this:

mySub row

Or...

Call mySub(row)

...but instead you are doing this:

mySub (row)

So, what's the difference? When you place parentheses around any variable that is standing alone (as in the above), that variable gets evaluated immediately and prior to whatever you plan to do with it.

The parentheses are a common way to override a procedure's ByRef argument and instead force a one-time ByVal parameter pass. This is because the parentheses force an evaluation of the variable and the resultant VALUE is passed instead of a reference to the variable.

In your case, you do not want to do this (in fact, in most cases you do not want to do this). When you sandwich row with parentheses, the range object is no longer passed to the routine. Instead it is evaluated and its values are passed as a Variant Array.

And since the mySub definition calls for a range object parameter, you get error 424. mySub is complaining, "Hey, this is not a range object and a range object is required!"

Answer 2

Add a call before it. You could also remove the () and it should work too ^_^ If you remove (), also remove the call.