Performing Multiple Actions on an Object

Procedures often need to perform several different actions on the same object. For example, you might need to set several properties for the same cell. One way to do this is to use several statements, as in the following example.

ActiveSheet.Cells(1, 1).Formula = "=SIN(180)"
ActiveSheet.Cells(1, 1).Font.Name = "Arial"
ActiveSheet.Cells(1, 1).Font.Bold = True
ActiveSheet.Cells(1, 1).Font.Size = 8

Notice that all these statements use the same object reference. You can make this code easier to enter, easier to read, and more efficient by using the With...End With statement.

With ActiveSheet.Cells(1, 1)
    .Formula = "=SIN(180)"
    .Font.Name = "Arial"
    .Font.Bold = True
    .Font.Size = 8
End With

You can also nest With...End With statements. The following example shows how you can rewrite the preceding code using one With...End With statement nested inside another one.

With ActiveSheet.Cells(1, 1)
    .Formula = "=SIN(180)"
    With .Font
        .Name = "Arial"
        .Bold = True
        .Size = 8
    End With
End With

The object reference following the With...End With statement can be a reference to either a single object or a collection. The following example sets the font properties of all the text boxes on the active sheet.

With ActiveSheet.TextBoxes
    .Font.Name = "Arial"
    .Font.Size = 8
End With