Object Required error Excel VBA

Question

I am creating a VBA application, and I have the following code:

---

Dim previousCell As range


Private Sub Worksheet_SelectionChange(ByVal target As range)

Application.EnableEvents = False
On Error GoTo ws_exit:


Set previousCell = target
getEffort (previousCell) '**Here i get object required** 

ws_exit:
    Application.EnableEvents = True
    MsgBox Err.Description

End Sub

Private Function getEffort(ByVal cell As range)

' do soemthing

End Sub

I'm not sure why I get the error message: Object required error at getEffort(previousCell). If I pass in the Target, it works.

Thanks

Answer 1

As others have suggested, the problem is the parentheses. What no one has adequately explained is why it is the parentheses.

When you say this:

getEffort previousCell

Then you are passing the previousCell Range object into the getEffort procedure. That is what the procedure expects and so it is happy.

When you say this:

getEffort (previousCell)

The parentheses around previousCell cause VBA to evaluate the previousCell object. When VBA evaluates an object it returns that object's default property. The default property of the Range object is .Value, which is a string.

So previousCell is evaluated and a string gets passed on to getEffort. Of course getEffort is expecting a Range object, so you receive the error message.

The fact that you are assigning Target to previousCell is a red herring. You likely introduced the parentheses when you switched to previousCell. If you don't believe me, try this:

getEffort (Target)

You will get the same error message.