How to declare a Workbook as a Global variable ?

How to declare a Workbook as a Global variable ?

Asked on October 27, 2018 in VBA.
Add Comment


  • 3 Answer(s)

    The most universal way for workbook global variable would be creating a module with a Public Property Get procedure.
    Then refer to it without calling any code first, and no need to worry if the file is open or not.

    Here is the code:

    Private wLocations As Workbook
    Public Property Get Locations() As Workbook
        Const sPath As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"
        Dim sFile As String
     
        If wLocations Is Nothing Then
            'extract file name from full path
            sFile = Dir(sPath)
     
            On Error Resume Next
     
            'check if the file is already open
            Set wLocations = Workbooks(sFile)
     
            If wLocations Is Nothing Then
                Set wLocations = Workbooks.Open(sPath)
            End If
     
            On Error GoTo 0
        End If
        Set Locations = wLocations
    End Property
    

    We can use it anywhere in the code as a global variable:

    Sub Test()
        Debug.Print Locations.Worksheets.Count
    End Sub
    
    Answered on October 27, 2018.
    Add Comment

    We can declare the global variable, but we cannot execute the code to assign the value outside of the procedure:

    Public myBook As Excel.Workbook
     
    Sub AssignWorkbook()
        Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside sub
    End Sub
     
    Sub TestItWorked()
        MsgBox myBook.Name
    End Sub
    
    Answered on October 27, 2018.
    Add Comment

    As we want to sort the Factory with the static properties, for our reference lets take separate module:

    mFactoryWkbs
    Private m_WkbLocations     As Workbook
    Private m_WkbMergeBook     As Workbook
     
    Public Property Get LOCATIONS() As Workbook
        If m_WkbLocations Is Nothing Then
            Set m_WkbLocations= Workbooks.Open("wherever")
        End If
        Set LOCATIONS = m_WkbLocations
    End Property
    Public Property Get MERGEBOOK () As Workbook
        If m_WkbMergeBook Is Nothing Then
            Set m_WkbMergeBook = Workbooks.Open("wherever")
        End If
        Set MERGEBOOK = m_WkbMergeBook
    End Property
    

    To use, just call the property where & when we need it, and no extra variables are required.

    TotalRowsMerged = MERGEBOOK.Worksheets("Sheet1").UsedRange.Rows.Count
    
    Answered on October 27, 2018.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.