How to make Excel VBA variables available to multiple macros?

by

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:

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:

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…

More Related Contents:

  1. to make this code simpler
  2. VBA – How to grab the cell below if a cell has text?
  3. VBA code takes long time to execute in excel
  4. Find text and show value if condition is met
  5. How can I insert variable into formula in VBA
  6. Excel VBA deleting rows in a for loop misses rows
  7. VBA – destroy a modeless UserForm instance properly
  8. What is the lifetime of a global variable in excel vba?
  9. Download attachment from Outlook and Open in Excel
  10. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  11. Embed picture in outlook mail body excel vba
  12. Copy data from another Workbook through VBA
  13. Excel VBA Load worksheet in Function
  14. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  15. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  16. Load csv file into a VBA array rather than Excel Sheet
  17. Insert line break in wrapped cell via code
  18. Change color of certain characters in a cell
  19. Combine multiple Excel workbooks into a single workbook
  20. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  21. Copy filtered data to another sheet using VBA
  22. VBA Macro crashes after 32000 rows
  23. How to create dynamic variable names VBA
  24. How to break a long string into multiple lines
  25. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  26. simple vba code gives me run time error 91 object variable or with block not set
  27. How to have vba execute every 10 minutes?
  28. Loop through cells and add to a range
  29. Read and write from/to registry in VBA
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment