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:
|