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:

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, if you have a number of different properties to change on a single object, it is more convenient to 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

You can nest With statements by placing one With loop within another. Each object must be unique.

See Also

Do...Loop Statement, While...Wend 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
    ' Same as MyObject.Height = 100
    .Height = 100        
    .Caption = "Hello World" 
    With .Font
        ' Same as MyObject.Font.Color = Red.
        .Color = Red
        .Bold = True
    End With
End With

The following example adds a new menu bar and then activates it. After running the example, you can use MenuBars(xlModule).Activate to restore the Visual Basic Module menu.

With MenuBars.Add("Stock Chart")
    With .Menus.Add("File")
        .MenuItems.Add "Update", "UpdateProc"
        .MenuItems.Add "Print", "PrintProc"
    End With
End With
MenuBars("Stock Chart").Activate

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