VBA 返回来自不同工作簿的范围值的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20242193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA return sum of range values from different workbook
提问by user3041384
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
我试图在 Excel 工作表中使用 VBA（我们称之为 wbA）来查找另一个工作簿中一系列单元格中的值的总和（我们称之为 wbB）。我尝试使用定制的求和函数
Function sumrange(rng As Range)
summ = 0
For Each cell In rng
summ = summ + cell.Value
Next
sumrange = summ
End Function
which i execute in the wbA here
我在这里执行的 wbA
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
This returns the sum of the set range in wbA instead of wbB
这将返回 wbA 而不是 wbB 中设置范围的总和
i have also tried the worksheetfunction.sum option in the following formats
我还尝试了以下格式的 worksheetfunction.sum 选项
l = book.Worksheets("November2013").Application.WorksheetFunction.Sum(Range(B5:T9"))
and
和
With book.Worksheets("December2014")
p = Application.WorksheetFunction.Sum(Range(B5:T9"))
End With
but both caluclate and return the sum of the range from wbA instead of from wbB
但两者都计算并返回来自 wbA 而不是来自 wbB 的范围的总和
How do i write the code to find sum of range in wbB Thanks
我如何编写代码以找到 wbB 中的范围总和谢谢
采纳答案by Siddharth Rout
Your code works for me with the change that I mentioned. This is what I tried
您的代码适用于我提到的更改。这是我试过的
When you specify a range without fully qualifying it, it will always refer to active sheet. And ActiveSheetmight not be the sheet that you expect it to be and hence fully qualify your objects.
当您指定一个范围而不完全限定它时，它将始终引用活动工作表。并且ActiveSheet可能不是您期望的工作表，因此完全符合您的对象。
Sub test4()
Dim book As Workbook
Dim a
Set book = Workbooks.Open("C:\Book1.xlsx")
a = sumrange(book.Worksheets(1).Range("A1:A4"))
MsgBox a
End Sub
Function sumrange(rng As Range)
summ = 0
For Each cell In rng
summ = summ + cell.Value
Next
sumrange = summ
End Function
I notice that you are using
我注意到你正在使用
Set book = app.Workbooks.Add("...\wbB.xlsm")
Use .OpenLike I have done.
.Open像我一样使用。
EDIT:
编辑：
@SiddharthRout yes i am running it from the VBA window in excel – user3041384 3 mins ago
@SiddharthRout 是的，我正在 excel 中从 VBA 窗口运行它 – user3041384 3 分钟前
In such a case, you don't need to define your excel application. Your code can be re-written as
在这种情况下，您不需要定义您的 excel 应用程序。您的代码可以重写为
Sub test4()
Dim book As Workbook, a
Set book = Workbooks.Open("...\wbB.xlsm")
With book.Worksheets("November2013")
a = sumrange(.Range("B5:T9"))
End With
MsgBox a
End Sub
回答by PowerFritz
For those who are still looking for a one-line-solution:
对于那些仍在寻找单行解决方案的人：
Cells(10, 1) = WorksheetFunction.Sum(Worksheets("data").Range(Worksheets("data").Cells(3, 35), Worksheets("data").Cells(131, 35)))
Just took it from my code, it addresses the range by cells, but you can easily use RANGE("...")instead of the CELLS(,)function).
刚刚从我的代码中取出它，它按单元格寻址范围，但您可以轻松使用RANGE("...")而不是CELLS(,)函数）。
The example line above writes the sum of worksheet "data" (range("AI3:AI131")) to A10 of current worksheet.
上面的示例行将工作表“数据”( range("AI3:AI131"))的总和写入当前工作表的 A10。
