passing a range to a sub 3

[RobBroekhuis]

Hi all,
This should be an easy one. I'm trying to pass a range variable dim'd in one sub to a second sub, using the following sections of code:

Sub FormatReference()
    Dim cell As Range
    For Each cell In Selection
       FormatRefCell (cell)
    Next cell
End Sub

Sub FormatRefCell(cell As Range)
...
End Sub

I get the error "Object required" whenever I try to run this. I've tried both ByVal and ByRef style argument passing. In the immediate window, I can confirm during debugging that typename(cell)="Range". Does this happen because the standard property of a range object is .value? How do I get around it?
Thanks for your help
Rob

 

[kevinclark]

I am not sure of your ultimate goal so I included three options. Maybe one of them will help.

Sub FormatReference()

'Option 1
'You could get the address of each individual
'cell in your selection and do what you wish
'with each cell referenced including passing
'to your function one cell at a time.

Dim c As Object
For Each c In Selection
MsgBox c.Address
Next

'Option 2
'or you could just use 'Selection' to change cell
'formating in one shot with one line of code.

'Selection.Interior.ColorIndex = 1

'Option 3
'or you could pass the entire range address to your
'function and manipulate it there.

FormatRefCell Selection.Address

End Sub


Sub FormatRefCell(Cell As String)
MsgBox Cell
Sheets(ActiveSheet.Name).Range(Cell).Interior.ColorIndex = 1
End Sub

 

[Mossoft]

Dont use 'Cell' as the name of your object - it is a reserved word. Call it something else.

M

 

[RobBroekhuis]

Mossoft - I use "cell" as a range variable all the time, and never encounter problems. Still, I renamed all references to "cell" (new name "c&quot, and the problem is still there.

Kevin - Thanks for your suggestions. I agree that there are several workarounds to solve the problem I describe, one of which is to pass the address of the range instead of the range object. But I asked this question because I want to find out what is wrong with the way I am trying to achieve it. To me, my method is the most consistent with principles of structured object-oriented programming - any workarounds will muddle the code in my opinion.

Still looking for an answer...
Rob

 

[Mossoft]

I removed the brackets from around Cell on the line:

FormatRefCell (cell)

and it works OK !?.

M

 

[kevinclark]

At first glance I thought the word 'Cell' might cause a problem but actually it is the word 'Cells' that is reserved. Still could get to be confusing though.

 

[RobBroekhuis]

Excellent Mossoft! That solved the problem. Anybody care to explain the underlying problem with the sub call with parentheses?
Rob

 

[logius]

Actually, when you're passing a variable like this, and you absolutely want the parenthesis to stay, you'll have to use the

Call

method. Instead of:

FormatRefCell (cell)

use

Call FormatRefCell(cell)

By omitting the parenthesis the code will work fine, though. Just a matter of preference. ----------------------------------------
If you are reading this, then you have read too far...

lightwarrior@hotmail.com

 

[Kaah]

In VB when you enclose a parameter in brackets it indicates that you're expecting the function/method to return a result. In the above example, you'll find that if you let your function return a result to somthing then you can keep the brackets there. ie.

'\\this will work
sMyResult = FormatRefCell (cell)
'\\this will not work
FormatRefCell (cell)
'\\this will work
FormatRefCell cell
'\\thiswill not work
sMyresult = FormatRefCell cell


Of course this is assuming that FormatRefCell is a function and can indeed return a result.


'\\another example
sMyresult = msgbox("prompt", vbyesno, "mytitle&quot
msgbox "prompt"



On a sideline, mossoft was right to say dont use "cell" as a variable name. dont use anything that even remotely looks like a system/reserved word. the best way to do this is to get in the habit of using hungarian notation. Hungarian notation is a system where you name all of your vars and objects with a prefix that indicates what it is.

in hungarian notation:
cell is called rCell (Range Cell)
MyResult is called sMyResult (string result)
FormatRefCell is called fncFormatRefCell (function FormatRefCell)
etc etc

if you get into this habit you'll wonder how you ever did without it...makes your code twenty time more readable later! there are lots of resources on hungarian notation so look it up...

Hope this is all okay, let me knoe if you need more info on anything...

k.