title: Declaration of global range variable causes automation error
body: <p>This question is about a peculiar occurence of Excel's somewhat mysterious error message "Automation Error -- Catastrophic Failure". It occurs when I open a specific Excel file that has VBA code where some range variables are declared at the modular level, outside of any sub or function. Details follow.</p>
  
  <p><em>UPDATE: After further troubleshooting I think I have isolated the problem. I might post a new question since a complete update would probably be too confusing. END UPDATE</em></p>
  
  <p><em>UPDATE 2: Now I've have posted <a href="https://stackoverflow.com/q/46058096/5457466">a new question</a> with some additional clarification.</em></p>
  
  <p>I have an Excel file with VBA code used to track inventory. On one sheet I have a table with merchendise items with several fields such as item number, item name, etc. I have written several user-defined functions that return various item properties as a function of the item number. These UDFs are based on a combination of the regular worksheet functions <code>INDEX</code> and <code>MATCH</code>.</p>
  
  <p>In particular, I have a function called <code>ITEM_NAME()</code> as illustrated in the screenshot (table minimized for simplicity):</p>
  
  <p><a href="https://i.stack.imgur.com/ie7Ja.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ie7Ja.png" alt="Function ITEM_NAME illustrated"></a></p>
  
  <p>The code for <code>ITEM_NAME()</code> follows; note that the two <em>Range</em> variables are declared outside the scope of the function (the function <code>ITEM_NUMBER_EXISTS</code> called by <code>ITEM_NAME</code> basically checks the validity of the provided argument, but the code is not shown here):</p>
  
  <pre><code>Option Explicit
  
  Dim mrngItemNumber As Range
  Dim mrngItemName As Range
  
  Public Function ITEM_NAME(varItemNumber As Variant) As String
  ' Returns Item Name as a function of Item Number.
      ' Return value error if argument is invalid:
      If Not ITEM_NUMBER_EXISTS(varItemNumber) Then
          ITEM_NAME = CVErr(xlErrNA)
          Exit Function
      End If
      ' The rest is executed only if the argument is a valid item number.
      Set mrngItemNumber = Range("A4:A6")
      Set mrngItemName = Range("B4:B6")
      ITEM_NAME = Application.WorksheetFunction.Index(mrngItemName, _
      Application.WorksheetFunction.Match(varItemNumber, mrngItemNumber))
  End Function
  </code></pre>
  
  <p>Since I have quite a lot of functions (not shown here) in the same module, I prefer to declare all the range variables as modular variables in the beginning of the code, as in the example above, and then assign them with the <code>Set</code> keyword inside the respective functions (and avoid having to declare them separately in each function).</p>
  
  <p>Now, with the code written as in this example, I get the annoying error message when the file is opened. But the error disappears if I do any of the following:</p>
  
  <p>1) change the variable type of <code>mrngItemNumber</code> and <code>mrngItemName</code> from <code>Range</code> to something else, such as <code>Integer</code> (although this obviously destroys the functions); or</p>
  
  <p>2) move the declarations into the scope of the function <code>ITEM_NAME</code>.</p>
  
  <p>After the error message appears, I can't really detect any actual problems with the file, it seems to be working normally.</p>
  
  <p>To add to the confusion, I fail to replicate the issue with my minimal example above. But the problem has now occured with two different files: The first time I just suspected it was a compatibility issue since the original file was created on Office for Mac 2008, but the second time I'm working with a file that was built from scratch in Excel 2016 on Windows.</p>
  
  <p>I've seen similar problems reported elsewhere (at <a href="https://stackoverflow.com/q/31888880">Stackoverflow</a> and <a href="https://social.technet.microsoft.com/Forums/office/en-US/341479ae-903a-423e-9a51-c3456ae7ad13/automation-error-catastrophic-failure?forum=excel" rel="nofollow noreferrer">Microsoft Technet</a>), but none with this particular flavor. Solutions suggested include checking that external references are working, and that no Excel process is running in the background. I have checked for both these issues but they don't seem to be connected to what I am experiencing. I've also checked that I have the latest update of Excel. (The file has one external reference (to <code>personal.xlsb</code>), and it is working as normal.)</p>
  
  <p>I can't figure out why I get this error?</p>