Excel VBA 将多个工作表设置为变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17297788/
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
Excel VBA setting multiple sheets as a variable
提问by jprj
I have a workbook that is used for project payback over a number of years. I have set up some code in ThisWorkbook to lock the results worksheets, but allow users to open or close column groups (to allow them to hide or unhide the month columns of each year). My current code, which works nicely, looks like this:
我有一个工作簿，用于多年的项目回报。我在 ThisWorkbook 中设置了一些代码来锁定结果工作表，但允许用户打开或关闭列组（以允许他们隐藏或取消隐藏每年的月份列）。我当前的代码运行良好，如下所示：
Sheet10.Protect Password:="password", UserInterfaceOnly:=True
Sheet10.EnableOutlining = True
Sheet11.Protect Password:="password", UserInterfaceOnly:=True
Sheet11.EnableOutlining = True
and so on for 4 more sheets (and it works).
依此类推，再添加 4 张纸（并且有效）。
What I would like to do is, define a variable that stores the sheet identifiers and run a For Each / Next loop on the real code.
我想要做的是，定义一个变量来存储工作表标识符并在真实代码上运行 For Each / Next 循环。
But I cannot get a variable declaration to work that doesn't throw some compile or runtime error.
但是我无法获得一个不会引发一些编译或运行时错误的变量声明。
My favourite construction is
我最喜欢的建筑是
Dim wSheet as Worksheet
wSheet = Array(Sheet10, Sheet11, Sheet14)
For Each wSheet in Workbook
wSheet.Protect Password:="password", UserInterfaceOnly:=True
wSheet.EnableOutlining = True
Next wSheet
But it fails on my setting wSheet... I have tried several variants but it nearly always fails on that second line (doesn't matter whether I use sheet index, sheet name etc). Any thoughts?
但是它在我的设置 wSheet 上失败了......我尝试了几种变体，但它几乎总是在第二行失败（我是否使用工作表索引、工作表名称等都没有关系）。有什么想法吗？
采纳答案by RBarryYoung
The problem in your code is the declaration in the first line:
您代码中的问题是第一行中的声明：
Dim wSheet as Worksheet
either make it an array declaration:
要么使它成为数组声明：
Dim wSheet() as Worksheet
or make it a variant like this:
或者使它成为这样的变体：
Dim wSheet as Variant
or this:
或这个：
Dim wSheet
回答by ChrisB
I couldn't get the first posted answer to work. Here's what worked for me, taken from an answer to this questionposted by user @Dee:
我无法获得第一个发布的答案。以下是对我有用的方法，摘自用户@Dee 发布的这个问题的答案：
Sub Test()
Dim sheetsArray As Sheets
Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
Dim sheetObject As Worksheet
' change value of range 'a1' on each sheet from sheetsArray
For Each sheetObject In sheetsArray
'Do something
Next sheetObject
End Sub
