passing a range between modules

[fly2079]

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?

[Derek]

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

[fly2079]

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?

[Derek]

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

[Jack in the UK]

HI __

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

HTH
Rdgs
==========
Jack

[Ivan F Moala]

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

[Juan Pablo González]

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