每个工作表循环的 Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21918166/
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 For Each Worksheet Loop
提问by Dakota
I am working on code to basically go through each sheet in my Workbook, and then update column widths. Below is the code I wrote; I don't receive any errors, but it also doesn't actually do anything. Any help is greatly appreciated!
我正在编写代码以基本上浏览我的工作簿中的每个工作表，然后更新列宽。下面是我写的代码；我没有收到任何错误，但它实际上也没有做任何事情。任何帮助是极大的赞赏！
Option Explicit
Dim ws As Worksheet, a As Range
Sub forEachWs()
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns
Next
End Sub
Sub resizingColumns()
Range("A:A").ColumnWidth = 20.14
Range("B:B").ColumnWidth = 9.71
Range("C:C").ColumnWidth = 35.86
Range("D:D").ColumnWidth = 30.57
Range("E:E").ColumnWidth = 23.57
Range("F:F").ColumnWidth = 21.43
Range("G:G").ColumnWidth = 18.43
Range("H:H").ColumnWidth = 23.86
Range("i:I").ColumnWidth = 27.43
Range("J:J").ColumnWidth = 36.71
Range("K:K").ColumnWidth = 30.29
Range("L:L").ColumnWidth = 31.14
Range("M:M").ColumnWidth = 31
Range("N:N").ColumnWidth = 41.14
Range("O:O").ColumnWidth = 33.86
End Sub
回答by Dmitry Pavliv
Try to slightly modify your code:
尝试稍微修改您的代码：
Sub forEachWs()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns(ws)
Next
End Sub
Sub resizingColumns(ws As Worksheet)
With ws
.Range("A:A").ColumnWidth = 20.14
.Range("B:B").ColumnWidth = 9.71
.Range("C:C").ColumnWidth = 35.86
.Range("D:D").ColumnWidth = 30.57
.Range("E:E").ColumnWidth = 23.57
.Range("F:F").ColumnWidth = 21.43
.Range("G:G").ColumnWidth = 18.43
.Range("H:H").ColumnWidth = 23.86
.Range("i:I").ColumnWidth = 27.43
.Range("J:J").ColumnWidth = 36.71
.Range("K:K").ColumnWidth = 30.29
.Range("L:L").ColumnWidth = 31.14
.Range("M:M").ColumnWidth = 31
.Range("N:N").ColumnWidth = 41.14
.Range("O:O").ColumnWidth = 33.86
End With
End Sub
Note, resizingColumnsroutine takes parametr - worksheet to which Ranges belongs.
请注意，resizingColumns例程采用参数 - 范围所属的工作表。
Basically, when you're using Range("O:O")- code operats with range from ActiveSheet, that's why you should use With wsstatement and then .Range("O:O").
基本上，当您使用Range("O:O")- 范围为ActiveSheet 的代码操作时，这就是为什么您应该使用With wsstatement 然后.Range("O:O").
And there is no need to use global variables (unless you are using them somewhere else)
并且不需要使用全局变量（除非你在其他地方使用它们）
回答by Jerome Montino
Try this more succinct code:
试试这个更简洁的代码：
Sub LoopOverEachColumn()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
ResizeColumns WS
Next WS
End Sub
Private Sub ResizeColumns(WS As Worksheet)
Dim StrSize As String
Dim ColIter As Long
StrSize = "20.14;9.71;35.86;30.57;23.57;21.43;18.43;23.86;27.43;36.71;30.29;31.14;31;41.14;33.86"
For ColIter = 1 To 15
WS.Columns(ColIter).ColumnWidth = Split(StrSize, ";")(ColIter - 1)
Next ColIter
End Sub
If you want additional columns, just change 1 to 15to 1 to Xwhere Xis the column index of the column you want, and append the column size you want to StrSize.
如果你想其他列，只是改变1 to 15来1 to X这里X是你想要的列的列索引，并添加你想要的列大小StrSize。
For example, if you want P:Pto have a width of 25, just add ;25to StrSizeand change ColIter...to ColIter = 1 to 16.
例如，如果你想P:P拥有的宽度25，只需添加;25到StrSize和变化ColIter...来ColIter = 1 to 16。
Hope this helps.
希望这可以帮助。
回答by Jacob
You need to put the worksheet identifier in your range statements as shown below ...
您需要将工作表标识符放在范围语句中，如下所示...
Option Explicit
Dim ws As Worksheet, a As Range
Sub forEachWs()
For Each ws In ActiveWorkbook.Worksheets
Call resizingColumns
Next
End Sub
Sub resizingColumns()
ws.Range("A:A").ColumnWidth = 20.14
ws.Range("B:B").ColumnWidth = 9.71
ws.Range("C:C").ColumnWidth = 35.86
ws.Range("D:D").ColumnWidth = 30.57
ws.Range("E:E").ColumnWidth = 23.57
ws.Range("F:F").ColumnWidth = 21.43
ws.Range("G:G").ColumnWidth = 18.43
ws.Range("H:H").ColumnWidth = 23.86
ws.Range("i:I").ColumnWidth = 27.43
ws.Range("J:J").ColumnWidth = 36.71
ws.Range("K:K").ColumnWidth = 30.29
ws.Range("L:L").ColumnWidth = 31.14
ws.Range("M:M").ColumnWidth = 31
ws.Range("N:N").ColumnWidth = 41.14
ws.Range("O:O").ColumnWidth = 33.86
End Sub
回答by F.Rut
Instead of adding "ws." before every Range, as suggested above, you can add "ws.activate" before Call instead.
而不是添加“ws”。在每个 Range 之前，如上所述，您可以在 Call 之前添加“ws.activate”。
This will get you into the worksheet you want to work on.
这将使您进入要处理的工作表。
