VBA/Excel/Access/Word/Language Basics/With
The With/End With Structure
<source lang="vb">
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
</source>
Use a With...End With construct with fully qualified properties
<source lang="vb">
Sub sheetDemo()
With Sheets("Sheet1") MsgBox WorksheetFunction.Sum(.Range(.Range("A1"), .Range("A10"))) End With
End Sub
</source>
Use nested with statement
<source lang="vb">
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
</source>