Passing a range in a macro call

[Otto Moehrbach]

Excel 2002, WinXP
I feel a little silly asking this question, but I have tried every which way
to do this to no avail.
When calling one macro from another, how do you pass a range in the call?
Not the value of the range, but the range itself. Say it's MyRng. Thanks
for your help. Otto

 

[Guest]

Hi
sub foo_main()
dim myrng as range
set myrng=activesheet.range("A1:B2")
foo_sub(myrng)
end sub

sub foo_sub(rng as range)
dim cell as range
for each cell in rng
msgbox cell.value
next
end sub

 

[Otto Moehrbach]

Frank
Thanks for your help but there is a problem with it. I get an "Object
required." error in the foo-main macro on line "foo_sub (myrng)". Otto

 

[Tom Ogilvy]

Sub Main()
Dim rng as Range
set rng = Range("A1")
processRange rgn
End Sub

Sub ProcessRange(MyRange as Range)
msgbox typename(MyRange)
End Sub

 

[Tom Ogilvy]

That is because Frank has committed a serious syntax error. when calling a
subroutine (without using call) you don't enclose the arguments in Parens -
else they will be evaluated such as the error you have now.

foo_sub(myrng)

should be

foo_sub myrng

or

Call foo_sub(myrng)

 

[Otto Moehrbach]

Tom
You have come to the rescue again. I know to use "Call" when calling a
macro and passing something. But this time it completely slipped my mind.
Thanks. Otto

 

[Guest]

Hi Tom
thanks for the correction. should have run it first in the VBA editor(one of
my common mistakes...)