循环遍历 VBA 中的指定工作表

loop through specified sheets in VBA

提问by squar_o

I am trying to use a bit of code I found here For Each Function, to loop through specifically named worksheetsto loop through specified sheets in a workbook, run a small amount of code and move to next sheet.

我正在尝试使用我在此处为每个函数找到的一些代码，循环遍历特定命名的工作表以遍历工作簿中的指定工作表，运行少量代码并移至下一张工作表。

Sub LoopThroughSheets()
Dim Assets As Worksheet
Dim Asset As Worksheet


Assets = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")
For Each Asset In Assets
'my code here
   MsgBox ActiveSheet.Name 'test loop

Next Asset

End Sub

This is not looping through the sheets. I tried Dim Assets as Worksheetbut this broke the code.

这不是遍历床单。我试过了，Dim Assets as Worksheet但这破坏了代码。

Any help much appreciated,

非常感谢任何帮助，

Cheers

干杯

回答by Tony Dallimore

The code you show in your question fails because of:

您在问题中显示的代码失败的原因是：

Assets = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

Assetsis a Worksheet which is a type of Object and you must use Setwhen assigning a value to an Object:

Assets是一个工作表，它是一种对象，在为对象Set分配值时必须使用：

Set Assets = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

This would fail because Array("…")is not a worksheet.

这将失败，因为Array("…")它不是工作表。

You imply that an earlier version of your code would run but did not loop through the worksheets. The reason is:

您暗示您的代码的早期版本会运行，但不会循环遍历工作表。原因是：

MsgBox ActiveSheet.Name

This displays the name of the active worksheet but nothing in this loop changes the active worksheet.

这将显示活动工作表的名称，但此循环中没有任何内容更改活动工作表。

I am not happy with your solution although there is nothing explicitly wrong with it. I have seen too many programs fail because the programmer did too much in a single statement. Firstly, the more complicated a statement, the longer it will take to get right in the first place and the longer it takes to understand during subsequent maintenance. Sometimes the original programmer got the statement slightly wrong; sometimes the maintenance programmer got it wrong when trying to update it. In every case, any saving in runtime was not justified by the extra time spend by the programmers.

我对你的解决方案不满意，尽管它没有任何明显的错误。我见过太多程序失败，因为程序员在一条语句中做了太多事情。首先，语句越复杂，一开始就做对的时间越长，后续维护时理解的时间也就越长。有时原程序员得到的语句略有错误；有时维护程序员在尝试更新它时会出错。在每种情况下，程序员花费的额外时间都不能证明运行时的任何节省是合理的。

Alex K has fixed your code by redefining Assetsand Assetas Variants, as required by VBA, and adding Sheets(Asset).Selectto change which worksheet is active. I cannot approve of this because Selectis a slow statement. In particular, if you do not include Application.ScreenUpdating = False, the duration of your routine can go through the roof as the screen is repainted from each Select.

Alex K 通过重新定义Assets和Asset作为 VBA 要求的变体来修复您的代码，并添加Sheets(Asset).Select更改哪个工作表处于活动状态。我不能同意这一点，因为这Select是一个缓慢的声明。特别是，如果您不包括Application.ScreenUpdating = False，则您的例程的持续时间可能会随着屏幕从每个Select.

Before explaining my solutions, some background on Variants.

在解释我的解决方案之前，先介绍一些关于变体的背景知识。

If I write:

如果我写：

Dim I as Long

Iwill always be a long integer.

I将始终是一个长整数。

At runtime, the compiler/interpreter does not have to consider what Iis when it encounters:

在运行时，编译器/解释器不必考虑I它遇到的情况：

 I = I + 5

But suppose I write:

但假设我写：

Dim V as Variant

V = 5
V = V + 5
V = "Test"
V = V & " 1"

This is perfectly valid (valid but not sensible) code because a Variant can contain a number, a string or a worksheet. But every time my code accesses V, the interpreter has to check the type of the current contents of V and decide if it is appropriate in the current situation. This is time consuming.

这是完全有效（有效但不合理）的代码，因为 Variant 可以包含数字、字符串或工作表。但是每次我的代码访问 V 时，解释器都必须检查 V 的当前内容的类型，并决定它是否适合当前情况。这很耗时。

I do not want to discourage you from using Variants when appropriate because they can be incredibly useful but you need to be aware of their overheads.

我不想阻止您在适当的时候使用 Variants，因为它们非常有用，但您需要了解它们的开销。

Next I wish to advocate the use of meaningful and systematic names. I name my variables according to a system that I have used for years. I can look at any of my programs/macros and know what the variables are. This is a real time saver when I need to update a program/macro I wrote 12 or 15 months ago.

接下来我要提倡使用有意义的和系统的名称。我根据多年来使用的系统命名我的变量。我可以查看我的任何程序/宏并知道变量是什么。当我需要更新我在 12 或 15 个月前编写的程序/宏时，这是一个真正的节省时间。

I do not like:

我不喜欢：

Dim Assets As Variant
Assets = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

because "pipe_mat_tables" and so on are not assets; they are the names of worksheets. I would write:

因为“pipe_mat_tables”等不是资产；它们是工作表的名称。我会写：

Dim WshtNames As Variant
WshtNames = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

My first offering is:

我的第一个提议是：

Option Explicit
Sub Test1()

  Dim WshtNames As Variant
  Dim WshtNameCrnt As Variant

  WshtNames = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

  For Each WshtNameCrnt In WshtNames
    With Worksheets(WshtNameCrnt)
      Debug.Print "Cell B1 of worksheet " & .Name & " contains " & .Range("B1").Value
    End With
  Next WshtNameCrnt

End Sub

I could have named WshtNameCrntas WshtNamebut I was taught that names should differ by at least three characters to avoid using the wrong one and not noticing.

我可以命名WshtNameCrnt为，WshtName但我被告知名称应该至少相差三个字符，以避免使用错误的一个而不会引起注意。

The Arrayfunction returns a variant containing an array. The control variable of a For Eachstatement must be an object or a variant. This is why I have defined WshtNamesand WshtNameCrntas Variants. Note, your solution worked because a worksheet is an object.

该Array函数返回一个包含数组的变体。For Each语句的控制变量必须是对象或变体。这就是我将WshtNames和定义WshtNameCrnt为变体的原因。请注意，您的解决方案有效，因为工作表是一个对象。

I have used With Worksheets(WshtNameCrnt)which means any code before the matching End Withcan access a component of this worksheet by having a period at the beginning. So .Nameand .Range("B1").Valuereference Worksheets(WshtNameCrnt)without selecting the worksheet. This is faster and clearer than any alternative.

我已经使用With Worksheets(WshtNameCrnt)这意味着匹配之前的任何代码End With都可以通过在开头有一个句点来访问此工作表的组件。因此.Name，无需选择工作表即可.Range("B1").Value参考Worksheets(WshtNameCrnt)。这比任何替代方案都更快、更清晰。

I have used Debug.Printrather than MsgBoxbecause it is less bother. My code runs without my having to press Returnfor every worksheet and I have a tidy list in the Immediate Window which I can examine at my leisure. I often have many Debug.Printstatements within my code during development which why I have output a sentence rather than just a worksheet name or cell value.

我用过Debug.Print而不是MsgBox因为它不那么麻烦。我的代码无需按Return每个工作表即可运行，而且我在即时窗口中有一个整洁的列表，我可以在闲暇时检查。Debug.Print在开发过程中，我的代码中经常有很多语句，这就是为什么我输出一个句子而不仅仅是工作表名称或单元格值的原因。

My second offering is:

我的第二个提议是：

Sub Test2()

  Dim InxW As Long
  Dim WshtNames As Variant

  WshtNames = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

  For InxW = LBound(WshtNames) To UBound(WshtNames)
     With Worksheets(WshtNames(InxW))
      Debug.Print "Cell B1 of worksheet " & .Name & " contains " & .Range("B1").Value
    End With
  Next InxW

End Sub

This macro has the same effect as the first. I sometimes find Formore convenient than For Eachalthough I can see no advantage either way in this case. Note that I have written LBound(WshtNames)even though the lower bound of WshtNames will always be zero. This is just me being (over? excessively?) precise.

此宏与第一个具有相同的效果。我有时会发现For比For Each尽管在这种情况下我看不到任何优势更方便。请注意，LBound(WshtNames)即使 WshtNames 的下限始终为零，我也已写入。这只是我（过度？过度？）精确。

Hope this helps.

希望这可以帮助。

回答by squar_o

Solved it but always happy to hear other methods

解决了它，但总是很高兴听到其他方法

Sub loopsheets()

Dim Sh As Worksheet
For Each Sh In Sheets(Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables"))

MsgBox Sh.Range("b1")

Next
End Sub

Cheers

干杯

回答by Alex K.

Use variants instead of worksheets.

使用变体而不是工作表。

Arrayreturns a Variant array of string so cant be cast to Worksheet, the Eachvariable must be a Variant.

Array返回一个 Variant 字符串数组，因此不能强制转换为Worksheet，Each变量必须是Variant。

Dim Assets As Variant
Dim Asset  As Variant

Assets = Array("pipe_mat_tables", "pipe_diam_tables", "pipe_length_tables")

For Each Asset In Assets
    'my code here
    Sheets(Asset).Select
    MsgBox ActiveSheet.Name 'test loop
Next Asset