Excel VBAでIndexもシート名も使わず、シートオブジェクトをダイレクトに指定する方法

Excel VBAでシートを指定するには、名前指定・インデックス指定・オブジェクト名の直接指定がある。
最後の方法はあまり知られていないと思うが、とても便利なので是非活用してみてほしい。
一応おさらいとして、名前指定とインデックス指定のコードも掲載しておく。

この記事では以下のようなシート構成のブックにおいて「帳票シート」を指定することを想定する。
f:id:t-hom:20171206065131p:plain

Sub シートの名前指定()
    Worksheets("帳票").Range("A1").Value = "Hello"
End Sub

Sub シートのインデックス指定()
    Worksheets(2).Range("A2").Value = "GoodBye"
End Sub

前述のシート構成の場合、VBエディタでプロジェクトエクスプローラーを見ると、帳票シートは「Sheet2(帳票)」という表示になっている。
f:id:t-hom:20171206070110p:plain

シート名を変えても、シートの並び順を変えても、このSheet2という表記は変わらない。これがオブジェクト名である。
オブジェクト名は、プロジェクトエクスプローラーからシートを選んで、プロパティウインドウ※から変更することができる。
※プロパティウインドウが出てなければF4で表示

f:id:t-hom:20171206072451p:plain

試しに「Report」に変更してみよう。帳票シートは「Report(帳票)」という表示に変更される。
また、プロジェクトエクスプローラーはオブジェクト名のアルファベット順に並ぶので、先頭に移動される。
f:id:t-hom:20171206070947p:plain

さて、VBAではシートをオブジェクト名で直接指定することができる。
以下のコードを入力してみよう。

Sub シートのオブジェクト名指定()
    Report.Range("A3").Value = "Good Morning!"
End Sub

この方法はシート名やシートの並びに影響を受けないため、特に、固定のシートを使い続けるマクロでは非常に使いやすいと思う。
また、嬉しい効果としてオブジェクト名に続けてドットを入力すると入力候補が表示されることだ。

以下のように「Report.r」まで入力してTabキーで確定させることができる。
f:id:t-hom:20171206071519p:plain

次に「("A3").v」まで入力しカーソルキー「↓」でValueを選択してTabキーで確定。ここでも入力候補が表示される。
f:id:t-hom:20171206071746p:plain

もう一度、最初の名前指定と、インデックス指定を試してみよう。こちらの方法では入力候補は表示されない。

Sub シートの名前指定()
    Worksheets("帳票").Range("A1").Value = "Hello"
End Sub

Sub シートのインデックス指定()
    Worksheets(2).Range("A2").Value = "GoodBye"
End Sub

なぜそうなるかは、以下に詳しく書いたので参考までに。
thom.hateblo.jp

最後に、オブジェクト名の直接指定は便利だけれど、シートを自動生成するようなマクロや、対象のシートが削除されてしまうマクロでは使えないことに留意してほしい。

以上。

t-hom’s diary