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

1407 views vba
6

So 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

answered question

It's the job of the For Each to assign a value to ws each time through the loop

1 Answer

13

For each ws In ActiveWorkbook.Worksheets

Here the ws reference is used to fetch the ActiveWorkbook worksheets one by one. 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.

posted this

Have an answer?

JD

Please login first before posting an answer.