vba 获取 Excel 工作表名称并在宏中用作变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7176445/
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 Excel sheet name and use as variable in macro
提问by Mr_Thomas
I'm trying to find a way to use an Excel sheetname as a variable in a macro that I've written. Every month I deal with a workbook that is sent to me with 2 sheets. Part of the macro uses the 'Open File' interface to navigate to the folder and open the file.
我正在尝试找到一种方法，将 Excel 工作表名称用作我编写的宏中的变量。每个月我都会处理一本工作簿，里面有 2 张纸。宏的一部分使用“打开文件”界面导航到文件夹并打开文件。
The first sheet in the file is called 'Report', which is static. All I do with that is delete it (because I don't need it).
文件中的第一个工作表称为“报告”，它是静态的。我所做的就是删除它（因为我不需要它）。
The second sheet could be called anything and that's the sheet upon which I need to run the macro. Is there a way to say:
第二个工作表可以称为任何东西，这就是我需要运行宏的工作表。有没有办法说：
shtName = %whatever_the_sheetname_is_called%
and then use the 'shtName' variable throughout the macro? I suppose getting this new filename as a variable would help as well.
然后在整个宏中使用“shtName”变量？我想将这个新文件名作为变量也会有所帮助。
回答by MikeD
in a Visual Basic Macro you would use
在您将使用的 Visual Basic 宏中
pName = ActiveWorkbook.Path ' the path of the currently active file
wbName = ActiveWorkbook.Name ' the file name of the currently active file
shtName = ActiveSheet.Name ' the name of the currently selected worksheet
The first sheet in a workbook can be referenced by
工作簿中的第一张工作表可以通过
ActiveWorkbook.Worksheets(1)
so after deleting the [Report] tab you would use
所以在删除 [Report] 选项卡后，您将使用
ActiveWorkbook.Worksheets("Report").Delete
shtName = ActiveWorkbook.Worksheets(1).Name
to "work on that sheet later on" you can create a range object like
要“稍后处理该工作表”，您可以创建一个范围对象，例如
Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(shtName).[A1]
and continue working on MySheet(rowNum, colNum)etc. ...
并继续工作MySheet(rowNum, colNum)等等......
shortcut creation of a range object without defining shtName:
在不定义 shtName 的情况下创建范围对象的快捷方式：
Dim MySheet as Range
MySheet = ActiveWorkbook.Worksheets(1).[A1]
