Results 1 to 4 of 4

Calling another sub() in VBA

This is a discussion on Calling another sub() in VBA within the Excel Questions forums, part of the Question Forums category; I have a sub routine that is run. Within this sub routine, a number of variables are declared and stored ...

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    96

    Default Calling another sub() in VBA

    I have a sub routine that is run. Within this sub routine, a number of variables are declared and stored with certain values. I then would like to call a second sub routine, let's call it "SecondSub".

    Within the first sub routine, I have the code in place to call the SecondSub (Call SecondSub).

    My question is this: what needs to be done for those variables that were stored in the original sub routine to be able to be used in the SecondSub that is called by the original?

    Thanks,

  2. #2
    Board Regular
    Join Date
    Dec 2004
    Posts
    2,074

    Default Re: Calling another sub() in VBA

    Declare them outside the procedures

    Code:
     
    Public Var1 as Long
    Public Var2 as String
     
    Sub RunSub1
     
    '...code
    '...code
    '...code
     
    Call SubRun2
    End Sub
     
    Sub SubRun2
    'will now have access to Var1 and Var2 as defined in SubRun1
    End Sub
    Home: XL07 in Win7

    Work: XL07 in WinXP

    Any answer provided is based on one of these 2 environments, unless you specify otherwise.

    ------------------------------------------------

    Post your excel WS to the board - HTMLMaker Add-In

    ------------------------------------------------

    Always test any code posted on a COPY of your data.

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Posts
    96

    Default Re: Calling another sub() in VBA

    I figured it was something simple like that... I knew I had done it before, but just couldn't remember what I did.... thanks a lot!

  4. #4
    Board Regular
    Join Date
    Apr 2011
    Location
    Boca Raton, FL
    Posts
    860

    Default Re: Calling another sub() in VBA

    if you are declaring the variables in the first sub then use this:

    Code:
    Sub subOne()
        Dim var1 As Long
        Dim var2 As Long
        
        'code
        
        subTwo var1, var2
    End Sub
    
    Sub subTwo(ByVal v1 As Long, ByRef v2 As Long)
        'code
    End Sub
    note the "ByVal" and "ByRef" in this line "Sub subTwo(ByVal v1 As Long, ByRef v2 As Long)"

    ByVal means that only the value is passed to subTwo and subTwo cannot change the value

    ByRef means that a reference is passed to subTwo so subTwo can change the value of the variable and subOne will have access to the changed value

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
  •  


DMCA.com