vba 如何遍历工作表的子集？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1591442/
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
How can I loop through a subset of worksheets?
提问by Kirk Hings
I know how to loop through all the worksheets in a workbook, and how to exit once I reach an 'end-flag' worksheet:
我知道如何遍历工作簿中的所有工作表，以及如何在到达“结束标志”工作表后退出：
For Each ThisWorkSheet In Worksheets
If ThisWorkSheet.Name = "FlagEnd" Then Exit For
MsgBox "This worksheet name is: " & ThisWorkSheet.Name
Next
However I cannot get the loop to begin on a 'start-flag' worksheet (or even better on the worksheet right after the start-flag worksheet. For example the flagged start/end worksheets are in the middle of a bunch of other worksheets, so beginning or end traversing is not workable.
但是，我无法在“开始标志”工作表上开始循环（或者甚至在开始标志工作表之后的工作表上更好。例如，标记的开始/结束工作表位于一堆其他工作表的中间，所以开始或结束遍历是不可行的。
There could be hundreds of worksheets before that 'FlagStart' sheet, so I really need to start on the right sheet.
在“FlagStart”工作表之前可能有数百个工作表，所以我真的需要从正确的工作表开始。
Tried:
尝试：
Set ThisWorkSheet = Sheets("FlagNew")
and
和
For Each Sheets("FlagNew") In Worksheets
Ideas?
想法？
Solution: Mathias was very close, but dendarii was that tiny step closer with the custom ending index. I actually figured out my final solution on my own, but wanted to give credit. Here was my final solution:
解决方案：Mathias 非常接近，但 dendarii 与自定义结束索引更接近。我实际上自己想出了我的最终解决方案，但想给予信任。这是我的最终解决方案：
Private Sub CommandButtonLoopThruFlaggedSheets_Click()
' determine current bounds
Dim StartIndex, EndIndex, LoopIndex As Integer
StartIndex = Sheets("FlagNew").Index + 1
EndIndex = Sheets("FlagEnd").Index - 1
For LoopIndex = StartIndex To EndIndex
MsgBox "this worksheet is: " & Sheets(LoopIndex).Name
' code here
Next LoopIndex
End Sub
采纳答案by dendarii
If this is not a particularly changeable workbook (i.e. worksheets are not being added and deleted all the time), you could store the names of the worksheets in a range on a hidden sheet and loop through them by name.
如果这不是一个特别多变的工作簿（即工作表不会一直被添加和删除），您可以将工作表的名称存储在隐藏工作表的范围内，并按名称循环遍历它们。
However, it sounds like they are stored consecutively in the workbook so, building on Mathias' solution, you could use a function to return the indices of the start and end worksheets and then loop through:
但是，听起来它们是连续存储在工作簿中的，因此，基于 Mathias 的解决方案，您可以使用函数返回开始和结束工作表的索引，然后循环：
Public Function GetStartIndex() As Integer
On Error Resume Next
GetStartIndex = ThisWorkbook.Worksheets("MyStartingWorksheet").Index + 1
End Function
Public Function GetEndIndex() As Integer
On Error Resume Next
GetEndIndex = ThisWorkbook.Worksheets("MyEndingWorksheet").Index - 1
End Function
Sub LoopThrough()
Dim wks As Worksheet
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer
iStart = GetStartIndex()
iEnd = GetEndIndex()
If iStart > 0 And iEnd > 0 And iEnd > iStart Then
For i = iStart To iEnd
Set wks = ThisWorkbook.Worksheets(i)
MsgBox wks.Name
Next i
End If
End Sub
回答by Mathias
I believe that if you use "foreach" you won't have any control over the starting sheet. For that matter, I am not even sure you are guaranteed the order in which the iteration will take place.
I think what you should do is first, get the index of the sheet you are interested in (get the sheet by name, and get its index), and then iterate using a for loop, over the indexes of the sheets starting at the flag sheet index.
[Edit: I hacked through a quick example]
我相信如果您使用“foreach”，您将无法控制起始表。就此而言，我什至不确定您是否能保证迭代发生的顺序。
我认为您应该首先获取您感兴趣的工作表的索引（按名称获取工作表，并获取其索引），然后使用 for 循环迭代从标志开始的工作表索引表索引。
[编辑：我破解了一个简单的例子]
Sub Iterate()
Dim book As Workbook
Dim flagIndex As Integer
Dim flagSheet As Worksheet
Set book = ActiveWorkbook
Set flagSheet = book.Worksheets("Sheet3")
flagIndex = flagSheet.Index
Dim sheetIndex As Integer
Dim currentSheet As Worksheet
For sheetIndex = flagIndex To book.Worksheets.Count
Set currentSheet = book.Worksheets(sheetIndex)
Next
End Sub
回答by Ade Miller
How about?
怎么样？
For Each ThisWorkSheet In Worksheets
If ThisWorkSheet.Name = "FlagStart" Then output = true
If ThisWorkSheet.Name = "FlagEnd" Then Exit For
If output = true Then MsgBox "This worksheet name is: " & ThisWorkSheet.Name
Next
This code might not be quite right. I'm writing it in the SO editor not VBA, but you get the idea.
这段代码可能不太正确。我是在 SO 编辑器中而不是 VBA 中编写它的，但是您明白了。
回答by SnakeWasTheNameTheyGaveMe
Do the sheets you iterate over have a common name format?
您迭代的工作表是否具有通用名称格式？
Ex)
前任）
Sheets(0).name > "Reports"
Sheets(1).name > "Start Here"
Sheets(2).name > "emp.0001"
Sheets(3).name > "emp.0002"
Sheets(4).name > "emp.0003"
Sheets(5).name > "emp.0004"
Sheets(6).name > "End Here"
If so, in your for each loop, just do a Left(ThisWorkSheet.name, 4) = "emp"to verify if it's a sheet you want to reference.
如果是这样，在您的 for each 循环中，只需执行 aLeft(ThisWorkSheet.name, 4) = "emp"以验证它是否是您要引用的工作表。
回答by Samir
In Excel VBA 2013 if you have the worksheets you want to update between tabs "Blankfirst" and "Blanklast" this works.
在 Excel VBA 2013 中，如果您有要在“Blankfirst”和“Blanklast”选项卡之间更新的工作表，则此方法有效。
Use the code below to test it brings back your tab names and then replace your manipulating code in place of MsgBox wks.Namepart.
使用下面的代码来测试它会带回您的选项卡名称，然后替换您的操作代码代替MsgBox wks.Name部分。
Sub Macro2()
On Error Resume Next
GetStartIndex = ThisWorkbook.Worksheets("Blankfirst").Index + 1
On Error Resume Next
GetEndIndex = ThisWorkbook.Worksheets("Blanklast").Index - 1
Dim wks As Worksheet
Dim i As Integer
Dim iStart As Integer
Dim iEnd As Integer
iStart = GetStartIndex
iEnd = GetEndIndex
If iStart > 0 And iEnd > 0 And iEnd > iStart Then
For i = iStart To iEnd
Set wks = ThisWorkbook.Worksheets(i)
MsgBox wks.Name
Next i
End If
End Sub
回答by Rob
Public Sub ITERATE_WORKSHEETS()
On Error Resume Next
Dim x As Long
For x = 0 To 100
MsgBox Worksheets(x).Name
Next x
On Error GoTo 0
MsgBox "all done"
End Sub
