vba 按代号完全引用工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25203173/
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
Fully reference a worksheet by codename
提问by WillborHaven
When I reference Excel worksheets using the CodeName property, how do I fully qualify them including the workbook reference?
当我使用 CodeName 属性引用 Excel 工作表时，如何完全限定它们，包括工作簿引用？
I want to guard against a scenario with two different workbooks open and two sheets having the same CodeName. I use ThisWorkbookbecause I want to reference the workbook the code is running from.
我想防止出现两个不同的工作簿打开并且两个工作表具有相同 CodeName 的情况。我使用ThisWorkbook是因为我想引用运行代码的工作簿。
I imagine something like this:
我想象这样的事情：
Dim tgWs As Worksheet
Set tgWs = ThisWorkbook.WsSummary
tgWs.Cells(1,1).Value = "Test"
where WsSummaryis the sheet's codename.
工作WsSummary表的代号在哪里。
回答by Jean-Fran?ois Corbett
Referring to a sheet by its codename always implies the sheet in ThisWorkbook, i.e. the workbook that contains the code you are executing.
通过代号引用工作表总是暗示 中的工作表ThisWorkbook，即包含您正在执行的代码的工作簿。
There seems to be no straightforward way to fully qualify a sheet in a different workbook using its codename.
似乎没有直接的方法可以使用其代号完全限定不同工作簿中的工作表。
This function will help you do this:
此功能将帮助您执行此操作：
Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
Dim iSheet As Long
If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
For iSheet = 1 To wb.Worksheets.Count
If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
Set GetSheetWithCodename = wb.Worksheets(iSheet)
Exit Function
End If
Next iSheet
End Function
Example usage:
用法示例：
GetSheetWithCodename("Sheet1", Workbooks("Book2")).Cells(1, 1) = "Sheet1 in Book2"
GetSheetWithCodename("Sheet1", ActiveWorkbook).Cells(1, 1) = "Sheet1 in ActiveWorkbook"
GetSheetWithCodename("Sheet1").Cells(1, 1) = "Sheet1 in ThisWorkbook"
Note that the last line is equivalent to simply saying:
请注意，最后一行相当于简单地说：
Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook"
because, as mentioned above, referring to a sheet by its codename always imply the sheet in ThisWorkbook.
因为，如上所述，通过代号引用工作表总是暗示ThisWorkbook.
回答by citizenkong
You can do this by adding the second Workbook as a reference, and calling the workbook by the VBA Project name.
您可以通过添加第二个工作簿作为参考并按 VBA 项目名称调用该工作簿来完成此操作。
Obviously, it's a good idea to changethe VBA project name from the default 'VBAProject'!
显然，将 VBA 项目名称从默认的“VBAProject”更改为一个好主意！
I tested this by creating two new workbooks each with one sheet, with a value of 1 or 2 in cell A1. The VBA projects were named 'Proj1' and 'Proj2', and the worksheets' CodeNames were left as 'Sheet1'.
我通过创建两个新工作簿来测试这一点，每个工作簿都有一张纸，单元格 A1 中的值为 1 或 2。VBA 项目被命名为“Proj1”和“Proj2”，而工作表的代码名称则保留为“Sheet1”。
This is the code I used:
这是我使用的代码：
Sub test()
Debug.Print Proj1.Sheet1.Cells(1, 1)
Debug.Print Proj2.Sheet1.Cells(1, 1)
End Sub
Yielding an output of:
产生以下输出：
1
2
回答by pathDongle
Worksheets can be referred to by their codename when the code is in the same workbook so fully qualifying is not necessary. You can't refer to another workbooks sheet by codename directly unless you loop each sheet and check the codename
当代码位于同一工作簿中时，工作表可以通过其代号引用，因此不需要完全限定。您不能直接按代号引用另一个工作簿表，除非您循环每个表并检查代号
so this is enough without needing to create a variable
所以这就足够了，不需要创建一个变量
with WsSummary
.Cells(1,1).Value = "Test"
end with
回答by Mario Di Noia
Dim wb as Workbook
Dim ws as worksheet
Set wb = "Your Workbook full path and name"
For each ws in wb.worksheets
If ws.codename = "Your Codename" then exit for
next ws
wswill now contain the reference to the worksheet in other workbook with the desired codename with no user changeable dependencies
ws现在将包含对其他工作簿中具有所需代号的工作表的引用，没有用户可更改的依赖项
Hope this helps
希望这可以帮助
回答by lwnuclear
Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook"without the ThisWorkBookprefix works fine without any extra methods
Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook"没有ThisWorkBook前缀工作正常，没有任何额外的方法
