vba 从工作表名称和单元格地址中获取值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19325320/
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
Get a value from the sheet name and cell address
提问by user2103670
Many times I need to get a value from multiple sheets (to some crazy workbooks - it can be up to 200 sheets). The problem is all these 200 sheets have the same structure even their name and it is crazy for me go to link one by one though these 200 sheets. Is there any way to create a user-defined function, something like
很多时候我需要从多张工作表中获取一个值（对于一些疯狂的工作簿 - 最多可达 200 张）。问题是所有这 200 张纸甚至它们的名字都具有相同的结构，我很疯狂地将这 200 张纸一张一张地链接起来。有没有办法创建一个用户定义的函数，比如
=getValue(sheetName,cell address)
I tried
我试过
Function GetValue(sheetName As String, cellAddress As String) As Variant
GetSheetValue = Range(sheetName & "!" & cellAddress).Value
End Function
which works well until I switch between Excel files. The function starts to return #Value which my feeling is that it tries to search for SheetA,B,C,D @A1 on other open workbooks.
在我在 Excel 文件之间切换之前，它运行良好。该函数开始返回#Value，我的感觉是它试图在其他打开的工作簿上搜索 SheetA、B、C、D @A1。
回答by Santosh
Try below code
试试下面的代码
Function GetValue(sheetName As String, cellAddress As String) As Variant
GetSheetValue = ThisWorkbook.Sheets(sheetName).Range(cellAddress)
End Function
回答by Aaron Thomas
This can be solved in VBA fairly easily. A For...Next loop would go through all 200, worksheets, returning the values. For example:
这可以在 VBA 中很容易地解决。For...Next 循环将遍历所有 200 个工作表，返回值。例如：
my_index = 1
For Each ws In Worksheets
If ws.Name <> worksheet_to_store_values_on Then
worksheet_to_store_values_on.Cells(my_index, 1).Value = _
ws.Range(myrange).Value
my_index = my_index + 1
End If
Next
... this will loop through all worksheets in your active workbook, find the value in myrangeon each sheet, and stores this value in the worksheet "worksheet_to_store_values_on" in row "my_index" of column A. You can adapt to your particular situation.
...这将遍历活动工作簿中的所有工作表，找到每张工作表上myrange 中的值，并将此值存储在 A 列“my_index”行的工作表“worksheet_to_store_values_on”中。您可以适应您的特定情况。
