Range definition problem

[Peter Chatterton]

I'm trying to pass a Range to a Function but it doesn't recognize it
-- it gives an 'object required' msg.

If I set up a Watch on the Range, it says its type is Range/Range.

The Range itself looks ok on the screen.

Any ideas?
Peter.

Sub testMacro()
Dim dataRange As Range
Set dataRange = ActiveWorkbook.Sheets("Portfolio Performance"). _
Range(ActiveWorkbook.Sheets("Portfolio
Performance").Cells(10, 1), _
ActiveWorkbook.Sheets("Portfolio
Performance").Cells(20, 25))
dataRange.Interior.ColorIndex = 3 ' ok
TestThisVariant (dataRange) ' ok
TestThisRange (dataRange) ' object required
End Sub

Function TestThisRange(ByVal rRange As Range) As Boolean
TestThisRange = True
End Function

Function TestThisVariant(vRange)
End Function

 

[Frank Kabel]

Hi
try:

Dim ret_value as boolean
'...
ret_value=TestThisRange (dataRange)

 

[Guest]

Two things.. First your object required issue arises because the function
wants to return a value, but you are not catching the value.

Secondly if I rememeber correctly VB/VBA does not like to pass ByVal into a
function and it defaults to ByRef no matter what.

Hope that helps...

 

[Guest]

I just double checked and the ByVal thing is fine. I must be losing it...

 

[Peter Chatterton]

Function TestThisRange(ByVal rRange As Range) As Boolean

TestThisRange = True
End Function

I've tried coding this without the Boolean, with the same result.

I also stand by the way it's coded.

Thanks,
Peter.

 

[Chip Pearson]

Peter,

Get rid of the parentheses in the call
TestThisRange (dataRange) ' object required
When you enclose arguments in parentheses to a procedure call
that does not return a value (or ignores the return value, as is
the case in your code), the argument within the parens is
evaluated and passed ByVal. Since the Value property is the
default property of a Range object, your code is equivalent to
TestThisRange dataRange.Value
And since TestThisRange expects a Range object, you get the
"Object Expected" error message.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peter Chatterton]

Sorry, your point 1 is right, I have to check the return value,
even tho it doesn't complain about it.
Peter