Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: passing a range between modules

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    HI __

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

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

  6. #6
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •