Pass a range to a method?
 

Hi
maybe the following is what you're trying to do?
Sub testit()
Debug.Print "Result: ", Test(Range("test"))
End Sub

Function Test(retireDate As Range)
Dim foo
foo = retireDate.Name
Test = foo
End Function

--
Regards
Frank Kabel
Frankfurt, Germany


Lucifer wrote:
I am trying to write a function that will take a different action
based upon the name of a range passes to it. As I understand it the
Range.Name returns a Name object. However, I can't get by the
invalid use of Property

Sub testit()
Debug.Print "Result: ", Test(Range("Retire_Date_Primary"))
End Sub

Function Test(retireDate As Range) As Integer
Dim foo As Name
foo = retireDate.Name
End Function

Pass a range to a method?
 

Frank,

If I read it correctly, this should be

Function Test(retireDate As Range)
Test = retireDate.Name.Name
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
maybe the following is what you're trying to do?
Sub testit()
Debug.Print "Result: ", Test(Range("test"))
End Sub

Function Test(retireDate As Range)
Dim foo
foo = retireDate.Name
Test = foo
End Function

--
Regards
Frank Kabel
Frankfurt, Germany


Lucifer wrote:
I am trying to write a function that will take a different action
based upon the name of a range passes to it. As I understand it the
Range.Name returns a Name object. However, I can't get by the
invalid use of Property

Sub testit()
Debug.Print "Result: ", Test(Range("Retire_Date_Primary"))
End Sub

Function Test(retireDate As Range) As Integer
Dim foo As Name
foo = retireDate.Name
End Function

Pass a range to a method?
 

Hi Bob
yes, correct :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Bob Phillips wrote:
Frank,

If I read it correctly, this should be

Function Test(retireDate As Range)
Test = retireDate.Name.Name
End Function



"Frank Kabel" wrote in message
...
Hi
maybe the following is what you're trying to do?
Sub testit()
Debug.Print "Result: ", Test(Range("test"))
End Sub

Function Test(retireDate As Range)
Dim foo
foo = retireDate.Name
Test = foo
End Function

--
Regards
Frank Kabel
Frankfurt, Germany


Lucifer wrote:
I am trying to write a function that will take a different action
based upon the name of a range passes to it. As I understand it
the
Range.Name returns a Name object. However, I can't get by the
invalid use of Property

Sub testit()
Debug.Print "Result: ", Test(Range("Retire_Date_Primary"))
End Sub

Function Test(retireDate As Range) As Integer
Dim foo As Name
foo = retireDate.Name
End Function

Pass a range to a method?
 

Frank

I'm rather suprised this works. As I understand it the Range("").Name method returns a Name object. What I want to do is interrogate the Name object and take a different path depending upon the name of the range passed into the function

Why doesn't this compile

Function Test(retireDate As Range) As Intege
Dim foo As Nam
foo = retireDate.Nam
Debug.Print fo
End Functio

So in my example, foo is a Name object and range.Name should return a Name object..... Confusing....

Pass a range to a method?
 

It doesn't, for the reasons you state. See my reply.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Lucifer" wrote in message
...
Frank,

I'm rather suprised this works. As I understand it the Range("").Name
method returns a Name object. What I want to do is interrogate the Name
object and take a different path depending upon the name of the range passed
into the function.

Why doesn't this compile:

Function Test(retireDate As Range) As Integer
Dim foo As Name
foo = retireDate.Name
Debug.Print foo
End Function

So in my example, foo is a Name object and range.Name should return a Name
object..... Confusing....

Pass a range to a method?
 

Range.Name does return a Name object. If you declare a Name variable and set
that to the Range.Name you will get a Name object (Note the Set, it's an
object).

But if you want to take action based upon the name of the range as you
originally said.you need to get the Name of the Name object. So you use

Set foo = retireDate.Name

or

foo = retureDate.Name.Name

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Lucifer" wrote in message
...
Bob,

I don't understand. The Range().Name method should return a Name object.
If I declare a varable of type Name, why can't I assign Range().Name to
this?

Function Test(retireDate As Range) As Integer
Dim foo As Name
foo = retireDate.Name
End Function

Pass a range to a method?
 

Bob

I did miss the "Set". Thanks for that. However, there is still something here I don't understand. Here is my code

Sub testit(
Test (Range("foobar")
End Su

Function Test(retireDate As Range
Dim foo As Nam
Set foo = retireDate.Nam
Debug.Print "Range(retireDate).Name", foo.Nam
End Functio

I keep getting an error "Object required" on the Subroutine call statement. Apparently I must not be passing a range object in the call. Can you make sense of this for me?

Pass a range to a method?
 

Try removing the brackets around (Range("foobar"))

Lucifer wrote:
Bob,

I did miss the "Set". Thanks for that. However, there is still something here I don't understand. Here is my code:

Sub testit()
Test (Range("foobar"))
End Sub

Function Test(retireDate As Range)
Dim foo As Name
Set foo = retireDate.Name
Debug.Print "Range(retireDate).Name", foo.Name
End Function

I keep getting an error "Object required" on the Subroutine call statement. Apparently I must not be passing a range object in the call. Can you make sense of this for me?

Pass a range to a method?
 

Where else are you calling from?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Lucifer" wrote in message
...
Bob,

Interestingly, when called from Excel, this functions works fine. There
is something in the paramater I'm passing to it from the Sub.

=test( Retire_Date_Primary )

Pass a range to a method?
 

Bob

I call from Excel using this and it works fine

=test( Retire_Date_Primary

I call it from another subroutine and it fails

Sub testit(
Test (Range("foobar")
End Su

Function Test(retireDate As Range
Dim foo As Nam
Set foo = retireDate.Nam
Debug.Print foo.Nam
End Function

Pass a range to a method?
 

Yes, the brackets caused the argument to be evaluated before passing it.

This caused it to pass the range's default property instead of the whole
range. To be honest I'm not sure what the default property of a range
object is.

You should not normally put brackets around the arguments to a sub
unless you are using "Call" or you have some special reason to evaluate
the only argument.

I can't think off-hand of a case where the evaluation would not be
better performed before calling the sub.

Lucifer wrote:
Ouch!

That works. Can you explain why? Do the brackets change the type of data being passed?

Pass a range to a method?
 

Hi
try
Sub testit()
dim ret
ret=Test (Range("foobar"))
End Sub

Function Test(retireDate As Range)
Dim foo As Name
Set foo = retireDate.Name
Debug.Print "Range(retireDate).Name", foo.Name
End Function

--
Regards
Frank Kabel
Frankfurt, Germany

"Lucifer" schrieb im Newsbeitrag
...
Bob,

I did miss the "Set". Thanks for that. However, there is still
something here I don't understand. Here is my code:

Sub testit()
Test (Range("foobar"))
End Sub

Function Test(retireDate As Range)
Dim foo As Name
Set foo = retireDate.Name
Debug.Print "Range(retireDate).Name", foo.Name
End Function

I keep getting an error "Object required" on the Subroutine call
statement. Apparently I must not be passing a range object in the
call. Can you make sense of this for me?

Pass a range to a method?
 

What happens with:

Sub testit()
Test Range("foobar")
End Sub

or:

Sub testit()
Call Test(Range("foobar"))
End Sub

Lucifer wrote:

Bob,

I call from Excel using this and it works fine:

=test( Retire_Date_Primary )

I call it from another subroutine and it fails:

Sub testit()
Test (Range("foobar"))
End Sub

Function Test(retireDate As Range)
Dim foo As Name
Set foo = retireDate.Name
Debug.Print foo.Name
End Function

--

Dave Peterson