ID: 12

Category: Excel Programming

Question: In VBA, how do I pass information from one subroutine to another

Answer: 

There are two ways to exchange information between subroutines.  The first is to store the information in global variables. I consider this the lazy way of programming since it is conducive to inadvertent updates and can be difficult to maintain.

Option Explicit
dim x as long, y as string
sub call1()
    x=1
    y="Hello"
    end sub
sub call2()
    msgbox x
    end sub
sub call3()
    msgbox y
    end sub
sub main()
    call1
    call2
    call3
    end sub

In the above code, changes made to variable x by Call1 are 'retained' as long as the code is running. So, the subsequent call to Call2 will display the updated value of x.

[If you make x public it can be accessed from other modules and other workbooks. For more on the scope, accessibility, and interaction of private/public modules and private/public variables see XL VBA help.]

The other way -- and the one I prefer is to pass the required variables to the appropriate procedure as parameters (also known as arguments).

Option Explicit

sub call1(byref x as long,byref y as string)
    x=1
    y="Hello"
    end sub
sub call2(byval x as long)
    msgbox x
    end sub
sub call3(byval y as string)
    msgbox y
    end sub
sub main()
    dim x as long, y as string
    call1 x,y
    call2 x
    call3 y
    end sub

In this case, the interaction between the different subs and the variables belonging to main is clearly defined.

ExtendedLink: