Range definition problem
 

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

Range definition problem
 

Hi
try:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Peter Chatterton" schrieb im Newsbeitrag
.. .
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

Range definition problem
 

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...

"Peter Chatterton" wrote:

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

Range definition problem
 

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

"Jim Thomlinson" wrote:

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...

"Peter Chatterton" wrote:

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

Range definition problem
 

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.

"Peter Chatterton" wrote in message
.. .
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

Range definition problem
 

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" wrote in message
.. .
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

Range definition problem
 

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

"Jim Thomlinson" wrote in message
...
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...

"Peter Chatterton" wrote:

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