Passing a range in a macro call
 

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

Passing a range in a macro call
 

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" wrote:

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

Passing a range in a macro call
 

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

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

Passing a range in a macro call
 

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

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

--
Regards,
Tom Ogilvy

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

Passing a range in a macro call
 

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)

--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote in message
...
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
"Frank Kabel" wrote in message
...
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" wrote:

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

Passing a range in a macro call
 

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
"Tom Ogilvy" wrote in message
...
Sub Main()
Dim rng as Range
set rng = Range("A1")
processRange rgn
End Sub

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

--
Regards,
Tom Ogilvy

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

Passing a range in a macro call
 

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

"Tom Ogilvy" wrote:

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)

--
Regards,
Tom Ogilvy


"Otto Moehrbach" wrote in message
...
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
"Frank Kabel" wrote in message
...
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" wrote:

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