Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver Problem Definition | Excel Worksheet Functions | |||
VBA Range definition: Code needed | Excel Discussion (Misc queries) | |||
How come this range definition is invalid? | Excel Programming | |||
variable range definition | Excel Programming | |||
Using Cells( ) for Range definition | Excel Programming |