[vba] How to make Excel VBA variables available to multiple macros?

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!

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.

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 Workbook
Dim 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