vba 获取包含代码的 Excel 工作表的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18129800/
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 name of an Excel worksheet containing code
提问by Chris
I have code that resides on a page and references the content of the workbook. When executed from a different worksheet, I would like to get the name of the specific Worksheet containing the code.
我有驻留在页面上并引用工作簿内容的代码。当从不同的工作表执行时，我想获取包含代码的特定工作表的名称。
I have worksheets that contain data. The code is added to that worksheet and the run - which produces a Summary worksheet. From the Summary worksheet, I would like to run the code on the data worksheet. This means I can't use ActiveSheetand I would have to reference the data sheet by name.
我有包含数据的工作表。代码被添加到该工作表和运行 - 这会生成一个摘要工作表。从摘要工作表中，我想在数据工作表上运行代码。这意味着我不能使用ActiveSheet，我必须按名称引用数据表。
How can I get the name of the worksheet containing the code without having to hard-code the name?
如何获取包含代码的工作表的名称而无需对名称进行硬编码？
回答by Sam73
Use the "Me" object.
使用“我”对象。
Me.Name is the property you seek, wich will give you the name of the sheet containing the code regardless the active sheet.
Me.Name 是您要查找的属性，无论活动工作表如何，它都会为您提供包含代码的工作表的名称。
回答by SeanC
There are 2 application properties that would interest you for this.
有 2 个应用程序属性会让您对此感兴趣。
Application.ThisWorkbookProperty (Excel)
Application.ThisWorkbook属性 (Excel)
Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.
返回一个 Workbook 对象，该对象表示当前运行宏代码的工作簿。只读。
and:
和：
Application.ThisCellProperty (Excel)
Application.ThisCell属性 (Excel)
Returns the cell in which the user-defined function is being called from as a Range object.
返回从中调用用户定义函数的单元格作为 Range 对象。
回答by Cor_Blimey
To query the actual code structure of your project you will need to allow access to the VBA project object model (Excel settings> Trust Center > Macro Settings then add a reference to Microsoft Visual Basic for Application Extensibility vX) where vX is the version like 5.3. You can use the objects in this to identify which sheets have what code within them.
要查询项目的实际代码结构，您需要允许访问 VBA 项目对象模型（Excel 设置 > 信任中心 > 宏设置，然后添加对 Microsoft Visual Basic for Application Extensibility vX 的引用），其中 vX 是 5.3 之类的版本. 您可以使用其中的对象来识别哪些工作表中包含哪些代码。
However, I would recommend doing it another way.
但是，我建议以另一种方式进行。
Instead, iterate through the worksheets in your workbook and then, within an error wrapper, run the macro using Application.Run
相反，遍历工作簿中的工作表，然后在错误包装器中使用 Application.Run 运行宏
Note that it would be better practice to refactor your code and put it all in a standard module, then pass in the worksheets as arguments (see my second example)
请注意，最好重构代码并将其全部放入标准模块中，然后将工作表作为参数传入（请参阅我的第二个示例）
E.g.:
例如：
'With code distributed in each worksheet
Sub blah()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
On Error Resume Next
Application.Run ws.CodeName & ".CollectInfoMacro"
If Err.Number = 1004 Then Debug.Print "Skipping "; ws.Name; ", No macro defined"
On Error GoTo 0
Next ws
End Sub
'Otherwise, better practice would be to refactor
'and not have code on each sheet, instead in a standard module:
Sub blahblah()
Dim ws As Worksheet
Dim results As Collection
Set results = New Collection
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Summary" Then 'or whatever
results.Add getYourInfoForTheSummary(ws), ws.Name
End If
Next ws
'Process your results (i.e. dump to the summary worksheet etc)
...
End Sub
Function getYourInfoForTheSummary(ws As Worksheet) As Collection 'or return whatever
Dim results As Collection
Set results = New Collection
With ws
'do something
End With
Set getYourInfoForTheSummary = results 'or whatever
End Function
