Declare a Workbook as a Global variable
I think the most universal way for workbook global variable would be creating a module with a Public Property Get
procedure. You can refer to it without calling any code first, and you don't have to worry if the file is open or not.
Here is the sample module code for one of the variables:
Private wLocations As WorkbookPublic 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 = wLocationsEnd Property
You can use it anywhere in the code as a global variable:
Sub Test() Debug.Print Locations.Worksheets.CountEnd Sub
Your question implies that you want a global workbook constant, not a variable. Because VBA doesn't allow objects to be initialised outside of a procedure, you can't have an object constant. The best you can do is have a public workbook variable that's initialised in an event.
You can declare a global variable, but you can't execute code to assign a value outside of a procedure:
Public myBook As Excel.WorkbookSub AssignWorkbook() Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside subEnd SubSub TestItWorked() MsgBox myBook.NameEnd Sub
So in a normal module you could have:
Public myBook As Excel.Workbook
And in your Workbook_Open()
event:
Private Sub Workbook_Open() Set myBook = Workbooks.Open("C:\SomeOtherBook.xlsx")End Sub
Then you can use myBook
elsewhere in your code without having to re-assign it.
It might be worth having a look at Chip Pearson's article about variable scope in VBA here
what you want is some sort of Factory with static properties, for example in a separate module
mFactoryWkbs
Private m_WkbLocations As WorkbookPrivate m_WkbMergeBook As WorkbookPublic Property Get LOCATIONS() As Workbook If m_WkbLocations Is Nothing Then Set m_WkbLocations= Workbooks.Open("wherever") End If Set LOCATIONS = m_WkbLocationsEnd PropertyPublic 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 you need it, no extra variables (or Sets for them) required.
TotalRowsMerged = MERGEBOOK.Worksheets("Sheet1").UsedRange.Rows.Count