VBA/Excel/Access/Word/Language Basics/With

The With/End With Structure

 
Sub withEnd()
    Range("A1:A10").Font.Bold = True
    Range("A1:A10").Font.Color = vbBlue
    Range("A1:A10").Font.FontStyle = "Italic"
    Range("A1:A10").Font.Size = 14
    Range("A1:A10").Font.name = "Comic Sans Serif"
    
    With Range("A1:A10").Font
        .Bold = True
        .Color = vbBlue
        .FontStyle = "Italic"
        .Size = 14
        .name = "Comic Sans Serif"
    End With
End Sub

Use a With...End With construct with fully qualified properties

 
Sub sheetDemo()
     With Sheets("Sheet1")
           MsgBox WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A10")))
     End With
End Sub

Use nested with statement

 
Sub NewWorkbook4()
  Dim myWorkbook As Workbook, myWorksheet As Worksheet
  Set myWorkbook = Workbooks.Add
  With myWorkbook
    Set myWorksheet = .Worksheets.Add(After:=.Sheets(.Sheets.Count))
    With myWorksheet
      .Name = "January"
      .Range("A1").Value = "Sales Data"
    End With
    .SaveAs Filename:="JanSales.xls"
  End With
End Sub