With Statement

Description

Executes a series of statements on a single object or a user-defined type.

Syntax

With object
[statements]

End With

The With statement syntax has these parts:

Part	Description

object	Required. Name of an object or a user-defined type.
statements	Optional. One or more statements to be executed on object.

Remarks

The With statement allows you to perform a series of statements on a specified object without requalifying the name of the object. For example, to change a number of different properties on a single object, place the property assignment statements within the With control structure, referring to the object once instead of referring to it with each property assignment. The following example illustrates use of the With statement to assign values to several properties of the same object.

With MyLabel
    .Height = 2000
    .Width = 2000
    .Caption = "This is MyLabel"
End With

Note Once a With block is entered, object can't be changed. As a result, you can't use a single With statement to affect a number of different objects.

You can nest With statements by placing one With block within another. However, because members of outer With blocks are masked within the inner With blocks, you must provide a fully qualified object reference in an inner With block to any member of an object in an outer With block.

Important Do not jump into or out of With blocks. If statements in a With block are executed, but either the With or End With statement is not executed, you may get errors or unpredictable behavior.

See Also

Do...Loop statement.

Example

This example uses the With statement to execute a series of statements on a single object. The object MyObject and its properties are generic names used for illustration purposes only.

With MyObject
    .Height = 100                    ' Same as MyObject.Height = 100.
    .Caption = "Hello World"        ' Same as MyObject.Caption = "Hello World".
    With .Font
        .Color = Red                    ' Same as MyObject.Font.Color = Red.
        .Bold = True                    ' Same as MyObject.Font.Bold = True.
    End With
End With

Example (Microsoft Excel)

This example creates a formatted multiplication table in cells A1:K11 on Sheet1.

Set dataTableRange = Worksheets("Sheet1").Range("A1:K11")
Set rowInputCell = Worksheets("Sheet1").Range("A12")
Set columnInputCell = Worksheets("Sheet1").Range("A13")

Worksheets("Sheet1").Range("A1").Formula = "=A12*A13"
For i = 2 To 11
    Worksheets("Sheet1").Cells(i, 1) = i - 1
    Worksheets("Sheet1").Cells(1, i) = i - 1
Next i
dataTableRange.Table rowInputCell, columnInputCell
With Worksheets("Sheet1").Range("A1").CurrentRegion
    .Rows(1).Font.Bold = True
    .Columns(1).Font.Bold = True
    .Columns.AutoFit
End With