vba - Declare a Workbook as a Global variable

Question

I am starting to write a code that will become applicable to multiple workbooks, but always uses the same reference workbook. The code will have many subs, and as I am trying to avoid to dim a variable to the reference workbook in every sub I would like to declare them Global.

First I had:

Global Locations As Excel.Workbook
Set Locations = Workbooks.Open("M:My DocumentsMSC ThesisItalyMergedlocXws.xlsx")

Which gave me:

"Compile error: Invalid outside procedure"

After some googling I found the following bit of code somewhere:

Public Const Locations As Excel.Workbook = "Workbooks.Open("M:My DocumentsMSC ThesisItalyMergedlocXws.xlsx")"

Which gave me:

"Compile error: Expected: type name"

---

Edit:

Using:

Public Const Locations As Excel.Workbook = "Workbooks.Open('M:My DocumentsMSC ThesisItalyMergedlocXws.xlsx')"

(Single quotation marks within the Workbooks.Open statement) results as the same error as when using double quotation marks.

Who knows what I am doing wrong?

Edit2:

I also tried to declare the variables in the "ThisWorkbook", following this answer using:

Private Sub Workbook_Open()
Dim Locations As Excel.Workbook
Dim MergeBook As Excel.Workbook
Dim TotalRowsMerged As String


Locations = Workbooks.Open("M:My DocumentsMSC ThesisItalyMergedlocXws.xlsx")
MergeBook = Workbooks.Open("M:My DocumentsMSC ThesisItalyMergedDURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
End Sub

But then it returns an

"Object Required"

within my module.

Edit3:

I now have this which works, but has the downside of having to copy the SET lines into every Sub, there has to be a better way to do this?

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks("DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

Answer 1

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 Workbook

Public Property Get Locations() As Workbook
  Const sPath As String = "M:My DocumentsMSC ThesisItalyMergedlocXws.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

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

Sub Test()
  Debug.Print Locations.Worksheets.Count
End Sub