title: Declare a Workbook as a Global variable
body: <p>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.</p>
  
  <p>First I had:</p>
  
  <pre><code>Global Locations As Excel.Workbook
  Set Locations = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")
  </code></pre>
  
  <p>Which gave me: </p>
  
  <blockquote>
    <p>"Compile error: Invalid outside procedure"</p>
  </blockquote>
  
  <p>After some googling I found the following bit of code somewhere:</p>
  
  <pre><code>Public Const Locations As Excel.Workbook = "Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")"
  </code></pre>
  
  <p>Which gave me: </p>
  
  <blockquote>
    <p>"Compile error: Expected: type name"</p>
  </blockquote>
  
  <hr>
  
  <h2>Edit:</h2>
  
  <p>Using:</p>
  
  <pre><code>Public Const Locations As Excel.Workbook = "Workbooks.Open('M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx')"
  </code></pre>
  
  <p>(Single quotation marks within the Workbooks.Open statement) results as the same error as when using double quotation marks.</p>
  
  <p>Who knows what I am doing wrong?</p>
  
  <h2>Edit2:</h2>
  
  <p>I also tried to declare the variables in the "ThisWorkbook", following <a href="https://stackoverflow.com/questions/5897832/is-it-possible-to-declare-a-public-variable-in-vba-and-assign-a-default-value">this answer</a> using: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>But then it returns an </p>
  
  <blockquote>
    <p>"Object Required" </p>
  </blockquote>
  
  <p>within my  module.</p>
  
  <h2>Edit3:</h2>
  
  <p>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?</p>
  
  <pre><code>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
  </code></pre>