Macro & Passing a range

O

Otto Moehrbach

Excel 2002, WinXP
It seems to me this is simple but somehow I have gotten it twisted in my
mind.
Say that I have a range set, MyRng, in sheet "One".
Now I want to call another macro and pass MyRng in the call.
But at the time of this call, the active sheet is sheet "Two".
If I say:
Call Test2(Range(MyRng.Address))
it seems to me that I will pass a range with the same address but from sheet
"Two", whereas I want to pass the range that I originally set in sheet "One"

I tested this and that is exactly what happens. I used the following 2
macros for this test. In A1 of each sheet I typed the sheet name.
Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
MsgBox "Test1" & " " & MyRng(1).Value 'Shows "One"
Sheets("Two").Activate
Call Test2(Range(MyRng.Address))
End Sub

Sub Test2(TheRng As Range)
MsgBox "Test2" & " " & TheRng(1).Value 'Shows "Two"
End Sub

I realize that I can simply call the second macro while the first sheet is
active, but in reality, the first sheet is never active and I set that range
with a With/End With construct.
How can I pass a range in a macro call without running into this problem?
Thanks for your help. Otto
 
K

KL

Otto,

You don't need to do this:

Call Test2(Range(MyRng.Address))

Just do:

Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
Sheets("Two").Activate
Call Test2(MyRng)
End Sub

Your issue is not the variable, but the way you use the address property.
Also, by doing Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.

Regards,
KL
 
T

Tom Ogilvy

Your advice to use MyRng directly is right on, but
Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.
Click to expand...

this doesn't affect the definition of MyRng at all - it is duplicative
(range to string to range) and it does refer to the active sheet, but it
doesn't recreate MyRng. MyRng still refers to Sheet1 - the passed range
reference does not.

From the immediate window:

set myRng = worksheets("Sheet1").Range("A1")
Sheet2.Activate
? Range(myRng.Address).Address(external:=True),
myrng.Address(External:=True)
[Book1]Sheet2!$A$1 [Book1]Sheet1!$A$1
 
D

Dave Peterson

Just pass the range itself:

Call Test2(MyRng)



Otto said:
Excel 2002, WinXP
It seems to me this is simple but somehow I have gotten it twisted in my
mind.
Say that I have a range set, MyRng, in sheet "One".
Now I want to call another macro and pass MyRng in the call.
But at the time of this call, the active sheet is sheet "Two".
If I say:
Call Test2(Range(MyRng.Address))
it seems to me that I will pass a range with the same address but from sheet
"Two", whereas I want to pass the range that I originally set in sheet "One"

I tested this and that is exactly what happens. I used the following 2
macros for this test. In A1 of each sheet I typed the sheet name.
Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
MsgBox "Test1" & " " & MyRng(1).Value 'Shows "One"
Sheets("Two").Activate
Call Test2(Range(MyRng.Address))
End Sub

Sub Test2(TheRng As Range)
MsgBox "Test2" & " " & TheRng(1).Value 'Shows "Two"
End Sub

I realize that I can simply call the second macro while the first sheet is
active, but in reality, the first sheet is never active and I set that range
with a With/End With construct.
How can I pass a range in a macro call without running into this problem?
Thanks for your help. Otto
Click to expand...
 
K

KL

Tom,

You are right and I meant exactly that. I was trying to say that seemingly
Otto's intention was to recreate the range stored in the variable, I don't
think I said "you are recreating".

KL

Tom Ogilvy said:
Your advice to use MyRng directly is right on, but
Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.
Click to expand...

this doesn't affect the definition of MyRng at all - it is duplicative
(range to string to range) and it does refer to the active sheet, but it
doesn't recreate MyRng. MyRng still refers to Sheet1 - the passed range
reference does not.

From the immediate window:

set myRng = worksheets("Sheet1").Range("A1")
Sheet2.Activate
? Range(myRng.Address).Address(external:=True),
myrng.Address(External:=True)
[Book1]Sheet2!$A$1 [Book1]Sheet1!$A$1



--
Regards,
Tom Ogilvy

KL said:
Otto,

You don't need to do this:

Call Test2(Range(MyRng.Address))

Just do:

Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
Sheets("Two").Activate
Call Test2(MyRng)
End Sub

Your issue is not the variable, but the way you use the address property.
Also, by doing Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.

Regards,
KL
Click to expand...
Click to expand...
 
K

KL

Tom,

I re-read your post and now I also think that it was my English - I probably
used an incorrect word (recreate) as for me it didn't mean "to define the
variable again", but something like "build the range reference in a
duplicative way".

KL


KL said:
Tom,

You are right and I meant exactly that. I was trying to say that seemingly
Otto's intention was to recreate the range stored in the variable, I don't
think I said "you are recreating".

KL

Tom Ogilvy said:
Your advice to use MyRng directly is right on, but
Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.
Click to expand...

this doesn't affect the definition of MyRng at all - it is duplicative
(range to string to range) and it does refer to the active sheet, but it
doesn't recreate MyRng. MyRng still refers to Sheet1 - the passed range
reference does not.

From the immediate window:

set myRng = worksheets("Sheet1").Range("A1")
Sheet2.Activate
? Range(myRng.Address).Address(external:=True),
myrng.Address(External:=True)
[Book1]Sheet2!$A$1 [Book1]Sheet1!$A$1



--
Regards,
Tom Ogilvy

KL said:
Otto,

You don't need to do this:

Call Test2(Range(MyRng.Address))

Just do:

Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
Sheets("Two").Activate
Call Test2(MyRng)
End Sub

Your issue is not the variable, but the way you use the address
property.
Also, by doing Range(MyRng.Address) you are actually trying to recreate
MyRng which is a redundancy.

Regards,
KL

Excel 2002, WinXP
It seems to me this is simple but somehow I have gotten it twisted in
my
mind.
Say that I have a range set, MyRng, in sheet "One".
Now I want to call another macro and pass MyRng in the call.
But at the time of this call, the active sheet is sheet "Two".
If I say:
Call Test2(Range(MyRng.Address))
it seems to me that I will pass a range with the same address but from
sheet "Two", whereas I want to pass the range that I originally set in
sheet "One"

I tested this and that is exactly what happens. I used the following
2
macros for this test. In A1 of each sheet I typed the sheet name.
Sub Test1() 'Sheet "One" is active
Dim MyRng As Range
Set MyRng = Range("A1:A5")
MsgBox "Test1" & " " & MyRng(1).Value 'Shows "One"
Sheets("Two").Activate
Call Test2(Range(MyRng.Address))
End Sub

Sub Test2(TheRng As Range)
MsgBox "Test2" & " " & TheRng(1).Value 'Shows "Two"
End Sub

I realize that I can simply call the second macro while the first
sheet is
active, but in reality, the first sheet is never active and I set that
range with a With/End With construct.
How can I pass a range in a macro call without running into this problem?
Thanks for your help. Otto
Click to expand...
Click to expand...
Click to expand...
 
O

Otto Moehrbach

KL, Tom, Dave
I had an inkling that I was chasing my tail on this one. Thanks for
clearing that up. Otto
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Macro If Cell have "x" value 0
Not getting "argument type mismatch" 4
Convert list to UPPER, lower & Proper cases. 21
Scattered array cells copy to scattered array cells another workbook 14
entering value without acivating sheet 4
Protection kills macro 2
Run macro from active workbook whose name keeps changing 20
VBA set thick borders around non empty cells in range 0

Top