Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Range to a Function Problem
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 -- Regards, Rocky McKinley |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Range to a Function Problem
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 "Rocky McKinley" wrote in message ... 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 -- Regards, Rocky McKinley |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Range to a Function Problem
Thanks Chip, I appreciate the help.
-- Regards, Rocky McKinley "Chip Pearson" wrote in message ... 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 "Rocky McKinley" wrote in message ... 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 -- Regards, Rocky McKinley |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Range to a Function Problem
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 -- Regards, Rocky McKinley "Chip Pearson" wrote in message ... 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 "Rocky McKinley" wrote in message ... 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 -- Regards, Rocky McKinley |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing a Range to a Function Problem
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 -- Regards, Tom Ogilvy Rocky McKinley wrote in message ... 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 -- Regards, Rocky McKinley "Chip Pearson" wrote in message ... 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 "Rocky McKinley" wrote in message ... 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 -- Regards, Rocky McKinley |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Passing a range to a user defined function | Excel Discussion (Misc queries) | |||
passing a range to a user defined function using a form | Excel Programming | |||
Passing an unkown range to a function | Excel Programming | |||
Passing range as argument | Excel Programming |