vba For Each Function，循环遍历特定命名的工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21761697/
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
For Each Function, to loop through specifically named worksheets
提问by Solaire
Im trying to figure out the right way to code a macro that goes through 12 worksheets with specific names (Jan,Feb,...,Dec). I thought maybe for each function will be a good choice so i tried the following:
我试图找出正确的方法来编写一个宏，该宏通过具有特定名称（一月、二月、...、十二月）的 12 个工作表。我想也许对于每个功能都会是一个不错的选择，所以我尝试了以下操作：
dim crntSht as worksheet
set crntsht=("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
for each crntsht in worksheets
.
.
.
end for
this obviously did not work since I defined crntsht in the wrong manner.
这显然不起作用，因为我以错误的方式定义了 crntsht。
Can anyone suggest the best way to loop through all 12 sheets once each, and skip all other sheets in the same workbook?
任何人都可以建议最好的方法来循环遍历所有 12 个工作表，并跳过同一工作簿中的所有其他工作表吗？
Thanks
谢谢
回答by ARich
Ah, Tim beat me... my answer is slightly different however...
啊，蒂姆打败了我……不过我的回答略有不同……
Sub LoopThroughSheets()
Dim Months As Variant
Dim Month As Variant
Months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", _
"Aug", "Sep", "Oct", "Nov", "Dec")
For Each Month In Months
'Code goes here.
Next Month
End Sub
回答by Tim Williams
Alternative to Siddharth's answer:
悉达多答案的替代方案：
dim arrSht, i
arrSht = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
for i = lbound(arrSht) to ubound(arrSht)
with worksheets(arrSht(i))
'work with sheet
end with
next i
回答by Siddharth Rout
Use the Microsoft Excel MONTHNAMEfunction which returns a string representing the month given a number from 1 to 12.
使用 Microsoft ExcelMONTHNAME函数返回一个字符串，该字符串表示给定从 1 到 12 的数字的月份。
Syntax
句法
MonthName( number, [ abbreviate ] )
月名（数字，[缩写]）
abbreviate is optional. This parameter accepts a boolean value, either TRUE or FALSE. If this parameter is set to TRUE, it means that the month name is abbreviated. If this parameter is set to FALSE, the month name is not abbreviated.
缩写是可选的。此参数接受一个布尔值，TRUE 或 FALSE。如果此参数设置为TRUE，则表示月份名称是缩写的。如果此参数设置为 FALSE，则不缩写月份名称。
Example
例子
?MonthName(1,True)
will give you JAN
会给你 JAN
Using this to our benefit
使用这个对我们有利
Sub Sample()
Dim ws As Worksheet
Dim i As Long
For i = 1 To 12
Set ws = ThisWorkbook.Sheets(MonthName(i, True))
With ws
'
'~~> Rest of the code
'
End With
Next i
End Sub
回答by Henry Chow
I think this is probably better
我认为这可能更好
dim v as variant
for each v in thisworkbook.sheets
' do something
' msgbox v.name
' msgbox v.index
next
