CODEWITHSUNDEEP
vbaexcel
Coltrane
Coltrane

Reputation: 51

How to use a Range variable as subroutine argument

I have a very basic question about VBA sub routines. What I'm trying to do is

  1. Set activeCell to Range variable
  2. Pass that variable to Sub routine
  3. Do something with the activeCell

What I tried to do is 

Sub myMacro()

    Dim myCell As Range

    Set myCell = ActiveCell

    mySub(myCell)

End Sub

Sub mySub(cell As Range)
    ' Do something
End Sub

I keep getting an "Object required" error.
What is the problem here?

Upvotes: 1

Views: 825

Answers (2)

CaBieberach
CaBieberach

Reputation: 1768

When you pass arguments to a function, you dont need to use parenthesis. Eg:

     mySub myCell

You could also use Lance sugention and use use Call AND parenthesis. (Press F1 over Call in the VBA Editor to get more info about it)

     Call mySub(myCell)

Either way is correct, but the first notation might prove more helpful while learning VBA as the great mojority of Answers in forums use the first one.

Upvotes: 3

Lance Roberts
Lance Roberts

Reputation: 22842

You need to use the Call statement on the subroutine.

Call mySub(myCell)

Upvotes: 0

Related Questions