passing a range between modules

fly2079

New Member
Joined
Feb 18, 2002
Messages
4
How do I pass a range that I have modified by intersecting it with others to another macro in another module? Is a range universal just like if you'd named the range manually?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi
I am not really clever at this stuff but I'm going to make a suggestion. If it is stupid, please ignore.
Can you not write your macros so they all refer to a range address sitting in a cell somewhere on your worksheet which automatically updates when you change the range?
Derek
 
Upvote 0
It's not just a value, what I want to pass between modules is a whole range, but it's not named manually it's just made up through vba code based on some conditions. My module got too long in the code so I'm splitting them up but I need the range that I had set in the first module to go into the second one. Maybe the call function?
 
Upvote 0
Hi again
I didn't mean using a value. I meant both macros referring to a range address in a sheet cell. For example, if you type C2:G10 in cell A1 you can have both macros referring to this range, eg Range(Range("A1").Value).Select will select C2:G10.
You can then either manually update the range address in A1 as it changes or get your macros to do it.
I am sure there's a "proper" way to do this in VB code but when I don't know the code I look for ways round it like this.
good luck
Derek
 
Upvote 0
HI __

As Derek says or add in pop up to request the range to be input and so easy editing.....

HTH
Rdgs
==========
Jack
 
Upvote 0
If you have already defined your range as a
Range object then just pass it to a named
procedure as a range object eg

sub test()
Dim myrange as range
'more code

set myrange = something eg intersect 2 ranges

then pass it to a procedure

like;

RunMyNewRange myrange

End sub

Sub RunMyNewRange(PassedRange as Range)

T = application.intersect(PassedRange,Range("A1"))

'other code

End Sub


Ivan
 
Upvote 0
You can also make it a global variable, putting the Dim statement before your procedures... that way it will "remember" its value when changing subs. Should look like this:

Dim MyRange as Range

Sub Test1()
MsgBox MyRange.Count
End Sub

Sub Test2()
MsgBox MyRange.Address
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top