CODEWITHSUNDEEP
vbaexcelexcel-2010
Colby Ackerman
Colby Ackerman

Reputation: 15

Pass a Range as a Function Parameter

I am writing a function that needs to pass a specified range into it, but I keep getting an error whenever I call the function. Here's some example code--the code in the function is unimportant, as all I want to do is be able to use the passed in range for anything within.

My Declaration:

Sub exampleFunction (exampleRange as Range)

    exampleRange.Borders(xlEdgeLeft).Weight = xlMedium

End Sub

But when I try to call the function with this code: 

Dim myRange As Range
Set myRange = Sheet1.Range ("C2") 

exampleFunction (myRange) ' <-- This is what doesn't work

I get an error that says "Compile Error: Expected: =".

What do I need to do to pass in myRange correctly during the function call?

Upvotes: 0

Views: 193

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

exampleFunction (myRange)

should be

exampleFunction myRange

or for multiple arguments:

exampleFunction myRange, myString, myInt

You only use () when your method returns a value or when using the Call keyword.

If you wrap your argument in () - and you're not calling a Function or using Call - then VBA will first evaluate the argument before passing it to your method - you often don't want that.

You can see the impact of this evaluation in the Immediate window:

? typename( Range("A1") )    '>> "Range"

? typename( (Range("A1")) )  '>> "Double" (if A1 has a numeric value)
                             '   "String" (if A1 has a text value)

Upvotes: 1

Related Questions