How to make Excel VBA variables available to multiple macros?

excel, vba

Solution

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

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:

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

Problem

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!

Original source