How to make Excel VBA variables available to multiple macros?
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...
You may consider declaring the variables with moudule level scope. Module-level variable is available to all of the procedures in that module, but it is not available to procedures in other modules
For details on Scope of variables refer this link
Please copy the below code into any module, save the workbook and then run the code.
Here is what code does
The sample subroutine sets the folder path & later the file path. Kindly set them accordingly before you run the code.
I have added a function IsWorkBookOpen to check if workbook is already then set the workbook variable the workbook name else open the workbook which will be assigned to workbook variable accordingly.
Dim wbA As WorkbookDim wbB As Workbook
Sub MySubRoutine()
Dim folderPath As String, fileNm1 As String, fileNm2 As String, filePath1 As String, filePath2 As String
folderPath = ThisWorkbook.Path & "\"
fileNm1 = "file1.xlsx"
fileNm2 = "file2.xlsx"
filePath1 = folderPath & fileNm1
filePath2 = folderPath & fileNm2
If IsWorkBookOpen(filePath1) Then
Set wbA = Workbooks(fileNm1)
Else
Set wbA = Workbooks.Open(filePath1)
End If
If IsWorkBookOpen(filePath2) Then
Set wbB = Workbooks.Open(fileNm2)
Else
Set wbB = Workbooks.Open(filePath2)
End If
' your code here
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Using Prompt to select the file use below code.
Dim wbA As Workbook
Dim wbB As Workbook
Sub MySubRoutine()
Dim folderPath As String, fileNm1 As String, fileNm2 As String, filePath1 As String, filePath2 As String
Dim filePath As String
cmdBrowse_Click filePath, 1
filePath1 = filePath
'reset the variable
filePath = vbNullString
cmdBrowse_Click filePath, 2
filePath2 = filePath
fileNm1 = GetFileName(filePath1, "\")
fileNm2 = GetFileName(filePath2, "\")
If IsWorkBookOpen(filePath1) Then
Set wbA = Workbooks(fileNm1)
Else
Set wbA = Workbooks.Open(filePath1)
End If
If IsWorkBookOpen(filePath2) Then
Set wbB = Workbooks.Open(fileNm2)
Else
Set wbB = Workbooks.Open(filePath2)
End If
' your code here
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Private Sub cmdBrowse_Click(ByRef filePath As String, num As Integer)
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.AllowMultiSelect = False
fd.Title = "Select workbook " & num
fd.InitialView = msoFileDialogViewSmallIcons
Dim FileChosen As Integer
FileChosen = fd.Show
fd.Filters.Clear
fd.Filters.Add "Excel macros", "*.xlsx"
fd.FilterIndex = 1
If FileChosen <> -1 Then
MsgBox "You chose cancel"
filePath = ""
Else
filePath = fd.SelectedItems(1)
End If
End Sub
Function GetFileName(fullName As String, pathSeparator As String) As String
Dim i As Integer
Dim iFNLenght As Integer
iFNLenght = Len(fullName)
For i = iFNLenght To 1 Step -1
If Mid(fullName, i, 1) = pathSeparator Then Exit For
Next
GetFileName = Right(fullName, iFNLenght - i)
End Function
Create a "module" object and declare variables in there. Unlike class-objects that have to be instantiated each time, the module objects are always available. Therefore, a public variable, function, or property in a "module" will be available to all the other objects in the VBA project, macro, Excel formula, or even within a MS Access JET-SQL query def.