Object Required error Excel VBA

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.

It looks like target is not set to an instance of an object which is giving you the error. when you pass in the target the argument to the function function(argument) is set to an instance of an object. when you set previouscell = target target actually needs to be something or else you will get the exec error.

try setting previouscell = ActiveCell

Two things: first, you need to not use () or include some sort of return value when calling getEffort as a function. You also need to determine if you want that to be a sub/function, right now you are using both. Presumably you are making it a sub?

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** 
'or...
call getEffort(previousCell)

'add this too..
'exit sub
ws_exit:
    Application.EnableEvents = True
    MsgBox Err.Description

End Sub

Private sub getEffort(ByVal cell As range)

' do soemthing

End sub

Also, your main program never exits before your error statement, so it will pop up that message box always. Try adding Exit Sub before the error label to avoid a blank message box always appearing.