VBA：可以使用字符串调用工作表吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5427045/
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: Possible to call a worksheet using a string?
提问by Andy
I have a macro that I would like to use several (>20) times, but each time it needs to reference a different worksheet. For example, if my sheets are named S1, S2, S3, etc., for one particular use of the macro, it needs to call S2.
我有一个宏，我想使用几次（> 20）次，但每次都需要引用不同的工作表。例如，如果我的工作表被命名为 S1、S2、S3 等，对于宏的一个特定用途，它需要调用 S2。
I've tried defining a public string as "Public CS As String" and then nesting the macros to achieve the goal (without copying and pasting the entire macro 20+ times).
我尝试将公共字符串定义为“公共 CS 作为字符串”，然后嵌套宏以实现目标（无需复制和粘贴整个宏 20 次以上）。
For example,
例如，
Sub Macro_2()
CS = "S2"
Macro
End Sub
However, I get an error message, "Subscripts out of range" referring to this line of code in the main macro where I call the desired worksheet:
但是，我收到一条错误消息，“下标超出范围”，指的是我调用所需工作表的主宏中的这一行代码：
Sheets(" 'CS' ").Select
I appreciate the help!!!
我感谢帮助！！！
回答by GSerg
Um.. Parameters?
嗯..参数？
public sub YourMacro(byval SheetName as string)
Sheets(SheetName).select
end sub
Sub Macro_2()
YourMacro "S2"
End Sub
回答by Jean-Fran?ois Corbett
You can write a subroutine that calls your macro for all your sheets like this:
您可以编写一个子例程，为所有工作表调用宏，如下所示：
Sub ProcessAllSheets()
numberOfSheets = 10 ' Or whatever
' Loop through all sheets
For i = 1 To numberOfSheets
sheetName = "S" & i ' sheetName will be "S1" when i=1, "S2" when i=2 etc.
Call Macro_2(sheetName)
Next i
End Sub
or just one sheet at a time:
或者一次只有一张：
Sub ProcessOneSheet()
sheetName = "S7"
Call Macro_2(sheetName)
End Sub
Your macro will need a small edit to accept the sheetNameargument:
你的宏需要一个小的编辑来接受sheetName参数：
Sub Macro_2(sheetName)
' Macro_2 is not a very useful name...
' Call it something more descriptive!
CS = sheetName
' CS isn't a very good variable name either...
' Why not just replace CS with e.g. sheetName
'Macro code goes here...
End Sub
NB: Most programmers will advise you to generally stay away from Publicvariables, unless you specifically know that this is the best way to solve your problem (which it isn't in your case). Publica.k.a. global variables often result in hard-to-find bugs.
注意：大多数程序员会建议您通常远离Public变量，除非您明确知道这是解决问题的最佳方法（这不是您的情况）。Public又名全局变量通常会导致难以发现的错误。
