Tukukkk

Inside my loop (see code below), why should I NOT turn the ws.Cells.SpecialCells(xlCellTypeFormulas) reference into a Range object variable? (Based on good coding habits, are we not supposed to turn all Range, Worksheet, and Workbook object references into object variables? Regardless if they are used once or many times?)

I did try it with success but I have this feeling I shouldn't but I don't know exactly why which leads me to ask for specific reasons.

Sub FormatAllFormulas()

    Dim ws As Worksheet

    Dim wsc As Sheets



    Set wsc = ActiveWorkbook.Worksheets



    For Each ws In wsc

        With ws.Cells.SpecialCells(xlCellTypeFormulas) 

            .Style = "Currency"

            .Font.Bold = True

            .Interior.Color = 4908260

        End With

    Next ws

End Sub

You are getting a reference to a Range variable - it is just held by the With block itself. 5.4.2.21 of the language specification explains:

The With block variable is classified as a variable and has the same
declared type as <expression>.

At runtime, this is what happens:

If the value type of the evaluated expression is a class, it is
Set-assigned to an anonymous With block variable. Then,
<statement-block> is executed. After <statement-block> executes,
Nothing is assigned to the anonymous With block variable.

Functionally, this code from your example...

For Each ws In wsc

    With ws.Cells.SpecialCells(xlCellTypeFormulas) 

        .Style = "Currency"

        .Font.Bold = True

        .Interior.Color = 4908260

    End With

Next ws

...is exactly the same as this:

For Each ws In wsc

    Dim WithBlockVariable As Range

    Set WithBlockVariable = ws.Cells.SpecialCells(xlCellTypeFormulas)

    WithBlockVariable.Style = "Currency"

    WithBlockVariable.Font.Bold = True

    WithBlockVariable.Interior.Color = 4908260

    Set WithBlockVariable = Nothing

Next ws

As far as which you should use, that's pretty much a judgement call. As pointed out in the comments, the With block can be much more readable that a local variable. The other consideration is that since the reference is implicit, you can't accidentally use it after the With block exits. This can be important in your example specifically, because declared variables always have procedure scope. That means in the expanded example, you could use WithBlockVariable outside the loop. So, if you don't intend to use it outside the block, you might as well make it impossible to use outside the block.

One last thing to note (and this is where people can get into performance problems) is that With blocks can save de-referencing operations if you find yourself repeatedly using more than one . in expressions. For example, if you're setting more than one of the properties on the Font:

With ws.Cells(1, 1)

    .Font.Bold = True

    .Font.Color = vbRed

    .Font.Italic = True

End With

In this case, there's absolutely no reason to get an explicit reference to the Font, but notice that you're repeatedly calling the Range.Font property. This is always better:

With ws.Cells(1, 1).Font

    .Bold = True

    .Color = vbRed

    .Italic = True

End With