How to make Excel VBA variables available to multiple macros?

itemprop="text">

I have a string of macros that call
upon each other and refer to workbooks A and B. I want the first macro to prompt the
user to select document A and B and these Selections to become the workbook A and B
variables I refer to in the various macros.

How
do I make the selected documents the referred to variable throughout all the
macros?

Thanks in
advance!

itemprop="text">

class="normal">Answer

Declare
them outside the subroutines, like
this:

Public wbA as
            Workbook
Public wbB as Workbook
Sub MySubRoutine()
 Set
            wbA = Workbooks.Open("C:\file.xlsx")
 Set wbB =
            Workbooks.Open("C:\file2.xlsx")
 OtherSubRoutine
End
            Sub

Sub OtherSubRoutine()
 MsgBox wbA.Name,
            vbInformation
End
            Sub

Alternately, you
can pass variables between
subroutines:

Sub
            MySubRoutine()
Dim wbA as Workbook
Dim wbB as
            Workbook

 Set wbA = Workbooks.Open("C:\file.xlsx")
 Set
            wbB = Workbooks.Open("C:\file2.xlsx")
 OtherSubRoutine wbA, wbB
End
            Sub
Sub OtherSubRoutine(wb1 as Workbook, wb2 as Workbook)
 MsgBox
            wb1.Name, vbInformation
 MsgBox wb2.Name, vbInformation
End
            Sub

Or use
Functions to return
values:

Sub
            MySubroutine()
 Dim i as Long
 i = MyFunction()
 MsgBox
            i
End Sub
Function MyFunction()
 'Lots of code that does
            something
 Dim x As Integer, y as Double

 For x = 1 to
            1000
 'Lots of code that does something
 Next
 MyFunction
            = y
End
            Function

In the second
method, within the scope of OtherSubRoutine you refer to them
by their parameter names wb1 and wb2.
Passed variables do not need to use the same names, just the same variable types. This
allows you some freedom, for example you have a loop over several workbooks, and you can
send each workbook to a subroutine to perform some action on that
Workbook, without making all (or any) of the variables public in
scope.

A Note About User
Forms

Personally I
would recommend keeping Option Explicit in
all of your modules and forms (this prevents you from instantiating variables with typos
in their names, like lCoutn when you meant
lCount etc., among other reasons).

If you're using Option
Explicit (which you should), then you should
qualify module-scoped variables for style and to avoid ambiguity, and you
must qualify user-form Public
scoped variables, as these are not "public" in the same sense. For instance,
i is undefined, though it's Public in
the scope of
UserForm1:

href="https://i.stack.imgur.com/DQ8Kx.png" rel="noreferrer"> src="https://i.stack.imgur.com/DQ8Kx.png" alt="enter image description
here">

You can refer to it as
UserForm1.i to avoid the compile error, or since forms are
New-able, you can create a variable object to contain reference
to your form, and refer to it that way:

href="https://i.stack.imgur.com/vRWZn.png" rel="noreferrer"> src="https://i.stack.imgur.com/vRWZn.png" alt="enter image description
here">

NB: In the above
screenshots x is declared Public x as
Long in another standard code module, and will not
raise the compilation error. It may be preferable to refer to this as
Module2.x to avoid ambiguity and possible shadowing in case you
re-use variable names...