title: Can't access new Workbook from inside a function
body: <p>I'm trying to access information from other workbooks and populate that information in a central location. Specifically, I'm trying to write a function that can be used in a cell that will return the UsedRange.Count of the target Workbook.</p>
  
  <p>The problem is that I keep getting an Err #9 or #91 when I try to point to the workbook object. I am 99.99% sure I am naming the file correctly.</p>
  
  <p>What's more, when I run the code as a Sub as opposed to a Function it works fine.</p>
  
  <p>Here is the working Sub:</p>
  
  <pre><code>Sub test()
  
      Dim wb As Workbook
      Dim wb2 As Workbook
  
      Dim fileName As String
      Dim rootDir As String
  
      Dim BigSum As Integer
  
      rootDir = ActiveWorkbook.Path
      fileName = "TestSheet1"
  
      Set wb = ActiveWorkbook
  
      MsgBox rootDir
  
      On Error Resume Next
  
      Set wb2 = Workbooks(fileName &amp; ".xlsx")
  
      Set wb2 = Workbooks.Open(rootDir &amp; "\Test1\" &amp; fileName &amp; ".xlsx")
  
      MsgBox wb2.Worksheets("Sheet1").Range("A3").Value2
  
      BigSum = wb2.Worksheets("Sheet1").UsedRange.Count - 2
  
      MsgBox BigSum
  
  End Sub
  </code></pre>
  
  <p>Now here's an example of the Function:</p>
  
  <pre><code>Function Adder(exTension As String, fileName As String) As Integer
  
      Dim wb As Workbook
      Dim wb2 As Workbook
  
      Dim rootDir As String
  
      rootDir = ActiveWorkbook.Path
  
      Set wb = ActiveWorkbook
  
      On Error GoTo errHandler
  
      Set wb2 = Workbooks(fileName &amp; ".xlsx")
  
      Set wb2 = Workbooks.Open(rootDir &amp; "\" &amp; exTension &amp; "\" &amp; fileName &amp; ".xlsx")
  
      Adder = wb2.Worksheets("Sheet1").UsedRange.Count - 2
  
      MsgBox Adder
  
  errHandler:
  
      MsgBox Err.Number
  
      Resume exitHere
  
  exitHere:
  
  End Function
  </code></pre>
  
  <p>As far as I can tell, there's no real difference between these. Is this a issue with scope?</p>