title: VBA return sum of range values from different workbook
body: <p>I am trying to use VBA in an excel sheet (lets call it wbA) to find the sum of the values in a range of cells in another workbook (lets call that wbB). I have tried using a custom made summing function  </p>
  
  <pre><code>Function sumrange(rng As Range)
  
  summ = 0
  For Each cell In rng
  summ = summ + cell.Value
  Next
  sumrange = summ
  
  End Function
  </code></pre>
  
  <p>which i  execute in the wbA here</p>
  
  <pre><code>Sub test4()
  Dim app As New Excel.Application
  
  Dim book As Excel.Workbook
  Set book = app.Workbooks.Add("...\wbB.xlsm")
  
  With book.Worksheets("November2013")
  
  a = sumrange(Range("B5:T9"))
  
  End With
  MsgBox a
  End Sub
  </code></pre>
  
  <p>This returns the sum of the set range in wbA instead of wbB</p>
  
  <p>i have also tried the worksheetfunction.sum option in the following formats</p>
  
  <pre><code>l = book.Worksheets("November2013").Application.WorksheetFunction.Sum(Range(B5:T9"))
  </code></pre>
  
  <p>and </p>
  
  <pre><code>With book.Worksheets("December2014")
  p = Application.WorksheetFunction.Sum(Range(B5:T9"))
  End With
  </code></pre>
  
  <p>but both caluclate and return  the sum of the range from wbA instead of from wbB</p>
  
  <p>How do i write the code to find sum of range in wbB 
  Thanks</p>