ゆんの業務改善ブログ

①生産性向上 ②業務改善 ③自動化　について情報発信しています。VBAプログラムは本当の初心者から他のアプリケーションを呼び出して使う上級者的な使い方まで幅広いレベルで解説していきます。

トップ > VBAの勉強 > VBA 5分で分かるWith~End Withの使い方

VBA 5分で分かるWith~End Withの使い方

VBAの勉強

このブログの記事一覧にもどる

VBAのステートメントであるWith~End Withステートメントについて解説します。単なる文法（シンタックス）の掲示ではなく、オブジェクトの階層構造を使って、自由自在に使えるようになることを目指します。

この記事は中級です。
レベルについてはExcel VBAの実力（レベル）を定義してみる　初心者～三段をご参照ください。

目次

With~End Withを解説する前に知っておきたいこと

オブジェクトの階層構造を簡単に理解しておく

オブジェクトの階層構造とは難しい言葉に感じるかもしれませんが、大丈夫です。簡単に解説します。

オブジェクトはWorkbookやSheets、Rangeと言ったそこにある「モノ」です。そしてRangeはSheetsに属しており、SheetsはWorkbookに属しています。この関係を図で示すと下の図の様になります。

オブジェクトの階層構造のピラミッドの概念図
オブジェクトの階層構造のピラミッド

上の図がピラミッドを上から見たところ、下の図は、ピラミッドを横から見た所です。上に行くほど、狭い範囲になっており、下に行くほど裾野が広くなっています。上の段は下の段に属しています。このピラミッド型の構造をオブジェクトの階層構造と言います。

With~End Withは単に文法を覚えるだけでなく、オブジェクトのの階層構造を理解しておくとスムーズに、より深く理解出来ますので、覚えておきましょう。

より詳しい解説はVBA オブジェクトの階層構造で解説しましたのでそちらをご覧下さい。

With~End Withステートメントはピラミッドの下の部分をまとめる事が目的

With~End Withステートメントの目的は一番下の部分からある階層までの部分をまとめる事です。Active Cellに値を書き込む場合は、単にCells(1,1) = "こんにちは"と言うような書き方で問題ありませんが、別シートに書き込む時のことを考えて、Sheets(1)などのより下層*1のオブジェクトを指定する場合を考えます。

標準モジュールを挿入して下記のコードを記述してみます。Sheets(1)がアクティブな状態になっています。

Sub ピラミッドの下層を省略しないで書く()

    ActiveWorkbook.Sheets(1).Range("A1") = "こんにちは"

End Sub

A1セルに「こんにちは」と表示されました。今度は最下層だけ省略してみます。

Sub ピラミッドの最下層のみ省略して書く()

    Sheets(1).Range("A1") = "こんにちは"

End Sub

同じく、A1セルに「こんにちは」と表示されます。当たり前ですか？他にWorkbookがあったら、他にもSheets(1)はあるはずですよね。案外当たり前じゃないかもしれません。それでもちゃんとActiveWorkbookのSheets(1)に「こんにちは」が記述されます。

これは、ActiveWorkbookを省略した時点で、「ああ、これはActiveWorkbookを省略したんだな」とエクセルさんが思ってくれるからです。だからこそ、標準モジュールではActiveWorkbookを省略する事ができますし、さらに同じ考え方でActiveSheetも省略して単にRange(”A1”) = 「こんにちは」と書くことができるのです。オブジェクトの階層構造のピラミッドを先に勉強したことで、理解が深まりましたね！

実際にWith~End Withステートメントを使ってみる

いよいよWith~Endステートメントを実際に使ってみます。目的の項目で解説したように、With~End Withはピラミッドの下層部分をまとめる事が目的です。これにより、Sheet(1)がアクティブな時にSheet(1)を省略して書く事ができたのと同じような事ができます。

If ~ End If 文と同様、先にWith と End With を記述していまいましょう。

Sub Withを使って標準モジュールのように省略できる()

    With ActiveWorkbook
        
    End With

End Sub

適当なSubプロシジャを作って、WithとEnd Withの部分だけ記述しました。Withの後にはActiveWorkbookを記述しました。これで、WithとEnd Withの間はActiveWorkbookを省略することができるようになりました。

Sub Withを使って標準モジュールのように省略できる()

    With ActiveWorkbook
        'これでActiveWorkbook.Sheets(1).Range("A1") = "こんにちは"と同じ意味
        .Sheets(1).Range("A1") = "こんにちは"
    End With

End Sub

見えにくいですが、「.」がSheets(1)の前についています。まさにActiveWorkbookの続きを書いている感じですね。

Sheets(1)も省略してみます。ちょっとずつ改善するスタイル。

Sub Withを使って標準モジュールのように省略できる()

    With ActiveWorkbook.Sheets(1)
        'これもActiveWorkbook.Sheets(1).Range("A1") = "こんにちは"と同じ意味
        .Range("A1") = "こんにちは"
    End With

End Sub

イメージとしては数学で 2x + 4y を2(x+2y)に書き換えているイメージです。ActiveWorkbookやActiveWorkbook.Sheets(1)の部分を( )でくくって外に出したんですね。カッコの中を増やしてみると具体的にイメージできます。

Sub Withを使って標準モジュールのように省略できる()

    With ActiveWorkbook.Sheets(1)
        'これもActiveWorkbook.Sheets(1).Range("A1") = "こんにちは"と同じ意味
        .Range("A1") = "こんにちは"
        .Range("A2") = "さようなら"
    End With

End Sub

With~End Withの中身を2行にしたことによって、（）でくくった感じが理解出来ましたね。

With ~ End Withの真価

今回は、オブジェクトの階層構造から理解を深めて、With~End Withステートメントの使い方を勉強しました。でもいまいちメリットが分かりにくいですよね。それはこの例がActiveSheetのRangeを動かしている岳だからです。省略できるんだったら、そもそも要りません。

逆に真価を発揮するのはActiveではないオブジェクトを操作する時です。これをうまく使えば、あるブックにあるシートのセルに値を書き込む為に、わざわざそのシートをActivateする必要がなくなります。

また、一つのオブジェクトに対する命令がたくさんある場合も力を発揮します。この2つの場合に当てはまるのが、表などで罫線をつける作業です。表全体を過不足なく含む範囲がActiveであるとは考えがたいので範囲を指定する必要があります。そしてその範囲に対して左辺、右辺、上辺、下辺と内側の縦線と横線に罫線をつけるとなると、これだけで6種類の操作をRangeに対して行わなければいけません。表に色をつけたりしたいならなおさらです。

いまのうちに、With~End Withをマスターしておくようにしましょう。ネットに転がっているサンプルコードもWith～End Withを使っていることが多いので、理解度が高まりますよ。


＜お知らせ＞
Excel VBAでクラスやオブジェクトの概念と使い方を丁寧に解説し、ワンランク上の実力を目指すガイドを書きました。この本で本物の実力を身に付けて一皮むけてみませんか？
books.rakuten.co.jp

*1:今回はピラミッドでオブジェクトの階層構造を説明しているため、「下層」と表現していますが、一般的には上下が逆です。SheetsはRangeの下位であり、Workbooksの下位です。他の人と話すときは木をつけましょう