How to make Excel VBA variables available to multiple macros

Question

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

User · Answer

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

User · Answer

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

User · 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     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

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

Examples related to vba

Examples related to excel