【VBA】With ステートメントの入れ子（ネスト）の方法と活用例

With オブジェクト

.プロパティ1 = 値
.プロパティ2 = 値
.メソッド1

End With

With オブジェクト1

.プロパティ1 = 値
.プロパティ2 = 値
With .プロパティのオブジェクト

.プロパティA = 値
.プロパティB = 値

End With

End With

Sub FormatCellsNestedWith()

With Range("A1:A10")

.Interior.Color = RGB(255, 255, 0) ' セルの背景色を黄色に

With .Font

.Bold = True
.Size = 12
.Color = RGB(255, 0, 0) ' 文字色を赤に

End With

End With

End Sub

Sub SetHeaderFooter()

With ActiveSheet.PageSetup

With .CenterHeader

.Text = "会社名 - レポート"

End With

With .CenterFooter

.Text = "作成日: &[Date] ページ &[Page] / &[Pages]"

End With

End With

End Sub

Sub FormatChartWithNestedWith()

Dim chartObj As ChartObject
Set chartObj = ActiveSheet.ChartObjects(1)

With chartObj.Chart

.ChartTitle.Text = "売上データ"

With .Axes(xlCategory)

.HasTitle = True
.AxisTitle.Text = "月"

End With

With .Axes(xlValue)

.HasTitle = True
.AxisTitle.Text = "売上金額"

End With

End With

End Sub

' ❌ ネストが深くて読みにくい
With Range("A1:A10")

With .Font

With .Bold

' ここまで深くすると分かりにくい

End With

End With

End With

Sub FormatCellsWithComments()

With Range("A1:A10")

.Interior.Color = RGB(255, 255, 0) ' 背景色を黄色に

With .Font

.Bold = True ' 太字
.Size = 12 ' 文字サイズ
.Color = RGB(255, 0, 0) ' 赤色

End With

End With

End Sub

Sub FormatCellsWithVariable()

Dim rng As Range
Set rng = Range("A1:A10")

With rng

.Interior.Color = RGB(255, 255, 0)
.Font.Bold = True
.Font.Size = 12
.Font.Color = RGB(255, 0, 0)

End With

End Sub