loop through specified sheets in VBA

Question

I am trying to use a bit of code I found here For Each Function, to loop through specifically named worksheets to 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 Worksheet but this broke the code.

Any help much appreciated,

Cheers

Answer 1

The code you show in your question fails because of:

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

Assets is a Worksheet which is a type of Object and you must use Set when assigning a value to an Object:

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

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

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 Assets and Asset as Variants, as required by VBA, and adding Sheets(Asset).Select to change which worksheet is active. I cannot approve of this because Select is 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.

Before explaining my solutions, some background on Variants.

If I write:

Dim I as Long

I will always be a long integer.

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

 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.

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.

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.

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:

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 WshtNameCrnt as WshtName but I was taught that names should differ by at least three characters to avoid using the wrong one and not noticing.

The Array function returns a variant containing an array. The control variable of a For Each statement must be an object or a variant. This is why I have defined WshtNames and WshtNameCrnt as Variants. Note, your solution worked because a worksheet is an object.

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

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

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 For more convenient than For Each although 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.

Hope this helps.

Answer 2

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

Answer 3

Use variants instead of worksheets.

Array returns a Variant array of string so cant be cast to Worksheet, the Each variable must be a 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