How to declare a Workbook as a Global variable ?
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
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
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