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 Documents\MSC Thesis\Italy\Merged\locXws.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 Documents\MSC Thesis\Italy\Merged\locXws.xlsx")"

Which gave me:

"Compile error: Expected: type name"

---

Edit:

Using:

Public Const Locations As Excel.Workbook = "Workbooks.Open('M:\My Documents\MSC Thesis\Italy\Merged\locXws.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 Documents\MSC Thesis\Italy\Merged\locXws.xlsx")
MergeBook = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\DURUM 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 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

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

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

Answer 2

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.Workbook

Sub AssignWorkbook()
    Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside sub
End Sub

Sub TestItWorked()
    MsgBox myBook.Name
End 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