Why is it not necessary to set an object variable before using a For Each Next loop?

user_10239691 0
Viewed 109

I'm studying this simple macro but I don't understand why it isn't necessary to set the object variable named ws with an object reference before using the For Each ... Next loop. My logic is:

Dim ws As worksheet simply creates the memory space to hold a worksheet object reference. So as far I am concerned, it's an empty object variable. It doesn't hold an object reference yet. Only its "datatype" has been specified.

So when we reference ws in the line For each ws In ActiveWorkbook.Worksheets, isn't the ws variable technically empty???? Shouldn't there be some line where we take ws = ActiveSheet so that the variable actually contains an object reference to a worksheet? So confused.

Sub FormatFormulas()
    Dim ws As worksheet
    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
        With ws.Cells.SpecialCells(xlCellTypeFormulas)
            .NumberFormat = ”#,##0”
            .Interior.ColorIndex = 36
            .Font.Bold = True
        End With
    Next ws
End Sub
vba excel
3 Answers

The run-time behavior in For Each loops is spelled out in section 5.4.2.4 of the VBA language specification.

For arrays:

  • If the declared type of the array is Object, then the <bound-variable-expression> is Set-assigned to the first element in the array. Otherwise, the <bound-variable-expression> is Let-assigned to the first element in the array.
  • After <bound-variable-expression> has been set, the <statement-block> is executed. If a <nested-for-statement> is present, it is then executed.
  • Once the <statement-block> and, if present, the <nested-for-statement> have completed execution, <bound-variable-expression> is Let-assigned to the next element in the array (or Set-assigned if it is an array of Object). If and only if there are no more elements in the array, then execution of the <for-each-statement> immediately completes. Otherwise, <statement-block> is executed again, followed by <nested-forstatement> if present, and this step is repeated.

For other enumerable types (in your case Workbook.Worksheets), this is the run-time behavior:

  • The data value of <collection> must be an object-reference to an external object that supports an implementation-defined enumeration interface. The <bound-variable-expression> is either Let-assigned or Set-assigned to the first element in <collection> in an implementation-defined manner.
  • After <bound-variable-expression> has been set, the <statement-block> is executed. If a <nested-for-statement> is present, it is then executed.
  • Once the <statement-block> and, if present, the <nested-for-statement> have completed execution, <bound-variable-expression> is Set-assigned to the next element in <collection> in an implementation-defined manner. If there are no more elements in <collection>, then execution of the <for-each-statement> immediately completes. Otherwise, <statement-block> is executed again, followed by <nested-for-statement> if present, and this step is repeated.

In your case, what this basically boils down to is that there is an implicit call generated by the compiler that performs the following assignment when entering the loop for the first time and for each iteration. It would be something similar to this once it's compiled (in reality it's a bit more involved than a check for Nothing):

Set ws = ActiveWorkbook.Worksheets.[_NewEnum]
Do While ws Is Not Nothing
    With ws.Cells.SpecialCells(xlCellTypeFormulas)
        .NumberFormat = "#,##0"
        .Interior.ColorIndex = 36
        .Font.Bold = True
    End With
    Set ws = ActiveWorkbook.Worksheets.[_NewEnum]
Loop

Note that this is why you shouldn't alter the contents of a collection while you're iterating over it- the next element is retrieved by calling [_NewEnum]. This means that the items returned by the underlying collection are "yielded" one at a time, so altering the contents of the collection inside the loop will either effect which items are returned or could potentially cause an error (the behavior of [_NewEnum] is implementation specific).

user_40888520

Here the ws reference is used to fetch the ActiveWorkbook worksheets one by one.

For Each ws In ActiveWorkbook.Worksheets

The reference to above ws in

Dim ws As worksheet

doesn't initiate the object but declares that the ws variable would be used as the worksheet.

user_13582430

Okay, thanks to Tim's answer, I was able to figure it out:

We COULD initiate the ws variable using Set ws = Worksheets(1) BUT it's not necessary for a For Each Next loop because it sets the variable with each iteration (and it always starts with the first item in the collection based on the index number of the items).

user_102396910
Related