Passing a Range to a Function Problem

  • Thread starter Thread starter Rocky McKinley
  • Start date Start date
R

Rocky McKinley

Hi when I run the TestIt sub below I get an error "Object Required". What
am I doing wrong?

Function FindIt(Ranger As Range) As String
Dim C As Range
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
FindIt (Range("a1:a4"))
End Sub
 
Rocky,

The problem is that you are enclosing the argument in parentheses
when you call the FindIt function. When you do this, VBA
evaluates that argument, and since the Value property is the
default property of a Range, VBA attempts to pass the value of
the range, not the actual range itself. You should enclose
arguments in parentheses only when the function call is returning
a value, or you want to force a ByRef argument to ByVal. Get rid
of the parentheses in the function call and you should be all
set. E.g.,

FindIt Range("a1:a4")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi Chip there is still a problem, FindIt should return "" or an address.

Function FindIt(Ranger As Range) As String
Dim C As Range
FindIt = ""
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
X = FindIt Range("a1:a4")
MsgBox X
End Sub
 
since you are using the returned value of the function, you should enclose
the argument in brackets. this worked fine for me:

Function FindIt(Ranger As Range) As String
Dim C As Range
FindIt = ""
Set C = Ranger.Find("BALER", LookIn:=xlValues)
If Not C Is Nothing Then FindIt = C.Address
End Function

Sub TestIt()
Dim x As String
x = FindIt(Range("a1:a4"))
MsgBox x
End Sub
 
Back
Top