VBA Sheetsの後にドットを入れても入力候補の自動補完がされない理由

'============▼2016/5/31追記▼============

Worksheetsオブジェクトは存在しないと書きましたが、Microsoft MVPの伊藤さんから誤りを指摘いただいたので修正しました。伊藤さん、ありがとうございます。

【参考】
http://www.relief.jp/itnote/archives/excel-vba-sheet-sheets-worksheet-worksheets.php

'============▲20165/31追記▲============

表題の件、いつかの記事で書いたような気はするが、ブログ内を検索してもうまく見つからなかったので改めて紹介する。

ご存じのとおり、VBAにはオブジェクトに続けてドットを入力すると、入力候補を表示してくれる機能が備わっている。
↓こういうやつだ。

これはMicrosoftがVBエディタに搭載しているインテリセンス(IntelliSense)という機能の一部である。

ならばThisWorkbook.Sheets(1).と入力すればRangeやCellsなどのプロパティが自動で候補に出てきてもよさそうなものだが、実際には何も表示されず、Rangeと手打ちするはめになる。さらにRangeに対するValueやInterior.Colorなどもすべて手入力である。

これには理由がある。

なぜ自動補完が利かないのか

実はExcelシートの種類はワークシートだけではない。
適当なシートのツマミを右クリックして挿入メニューを選ぶと、4種類のシート種類が出てくる。

たとえばグラフシートにはRangeやCellsなどのプロパティが存在しない。
VBAコンパイラはプログラムが記述される段階ではSheets(1)がどのタイプのシートか判別できないため、一旦汎用的に利用できるObject型として取り扱う。そのため、前述の入力補完が利用できないのだ。

VBAコンパイラにとってObject型というのは実際に動かしてみるまでどんなオブジェクトなのかわからない謎のオブジェクトである。（それゆえに何のオブジェクトでも入れられるのだが）

謎のオブジェクトが持つRangeプロパティは、ExcelのRangeオブジェクトであるとは限らない※。オブジェクト型であるかどうかもわからず、ただの値かもしれないし、そんなプロパティ自体そもそも存在しないかもしれないのだ。
※たとえばWordのDocumentオブジェクトもRangeプロパティを持っており、こちらはExcelのRangeとは別物である。

VBAコンパイラがオブジェクトの中身を保障できない以上、馴染みのRangeという名称が登場してもそのRangeがValueやInteriorなどのプロパティを持っているなどと、どうして言い切れるだろうか。
だから、ValueやInterior.Colorなども候補表示できない。だいいち、候補に表示しておいてそこから選んだ結果がエラーになったらみんな怒るだろう。
VBAエディタはそんな無責任なことはしないのだ。

VBAコンパイラが型を認識している場合、間違った記述をすると実行の前のコンパイルでエラーになる。下図では赤線のThisWorkbookがWorkbook型であると認識されているため、「Workbook型にそんなプロパティはないよ」というのを予め教えてくれるわけだ。

ところが、VBAコンパイラが型を認識していない場合、間違った記述をしても実行するまではエラーにならない。
以下の場合はコンパイルエラーではなく、実行時エラーになる。

Ahahahahahahhaなんていうプロパティは一目でオカシイと分かりそうなものだが、コンパイル段階で指摘してくれないのはなぜだろうか。

これは、赤線の部分がObject型(つまり謎のオブジェクト)として認識されるため、Ahahahahahahhaというプロパティが存在するか存在しないかはっきりしないからだ。

いや、まあ、たぶんオカシイんだけどその気になれば自作クラスにAhahahahahahhaって名前のプロパティを実装することはできるので、VBエディタは無責任にコンパイル段階でオカシイなどと指摘するわけにいかない。

で、実行時に「試してみたけど(やっぱり)そんなプロパティなかったよ」ってことである。

入力候補を自動補完させる方法

変数を使用する方法

Object型ではなくWorksheet型として認識させれば自動補完されるので、Worksheet型変数を作って代入してからその変数を操作すれば良い。

下図のようにWorksheet型の変数WSにセットすれば、最後までバッチリ自動補完が切れることがない。

これの応用で、新規ブックを開く際に最初からシート型オブジェクトに対象ブックのシートをセットしてしまうテクニックがある。
以下がそのコードである。

Sub Sample()
    Dim WS As Worksheet
    Set WS = Workbooks.Open("C:\Work\test.xlsx").Sheets(1)
    WS.Range("A1").Value = 1
    WS.Parent.Save
    WS.Parent.Close
End Sub

ブックを保存やクローズしたいときは、シートのParentプロパティで操作できる。
※シートに対していろいろ操作する場合は便利だが、Parentに対しては入力補完が利かないので、上の例のようにRangeを1ついじる程度ならブックを開いたほうが効率が良い。

オブジェクト名を使用する方法

または、最初からシートオブジェクトを指定してやる方法もある。
下図の赤線で示した表示がオブジェクト名である。

オブジェクト名はプロパティから青枠の箇所で変更できる。また、緑線で示した個所はExcel上で表示・変更できるシート名である。

この方法は自分のブックで固定のシート(マクロ中で削除されたり追加されたりしない)を扱う場合に便利である。
マクロで開いた別ブックのシートを参照するような場合には使えない。

【参考記事】
thom.hateblo.jp

WorksheetsプロパティとSheetsプロパティの違い

Workbookオブジェクトには、Sheetsプロパティの他にWorksheetsプロパティも存在する。
これを使えば最初からWorksheet型になりそうなものであるが、残念ながらそうはならない。

Sheetsプロパティはすべての種類のシートを含むSheetsオブジェクトを返す。
Worksheetsプロパティはすべてのワークシートを含むSheetsオブジェクトを返す。

つまり、Worksheetsプロパティはワークシート型のシートだけを返すのだが、どちらもSheetsオブジェクトなのだ。
Sheetsオブジェクトのデフォルトプロパティに引数としてシート番号を渡すと、シートがObject型として返るという仕組みである。

以下のコードで、もう少し具体的にみていこう。

ThisWorkbook.Sheets(1).Range("A1").Value = 1

上記コードはまずThisWorkbookが評価されてWorkbookオブジェクトになる。

【参考】評価という言葉に引っかかったらこちら。
thom.hateblo.jp

Workbookオブジェクト.Sheetsプロパティが評価され、Sheetsオブジェクトになる。
Sheets(シート番号)は実際には、Sheets.Item(シート番号)の短縮系である。

短縮せずに書くならこうなる。

ThisWorkbook.Sheets.Item(1).Range("A1").Value = 1

※厳密にはItemではなく[_Default]という特殊なプロパティなのだが、話がややこしくなるのでここではItemとしておく。

さて、Sheetsオブジェクト.Itemプロパティ(1)が評価されると1番目のシートオブジェクトになる。しかし、Worksheetオブジェクトになるか、それともChartオブジェクト(グラフシート)になるかは実行してみるまでわからないので、Object型として扱われる。

Worksheetsプロパティを使用した場合はワークシート型のシートだけが選別されるが、結局Sheetsオブジェクトを返す時点で、そのデフォルトプロパティがシートをObject型として扱うため、以降の自動補完はされない。

プロパティ名と、そのプロパティが返すオブジェクト名の関係はちょっとややこしいので以下にまとめておく。

• SheetsプロパティはSheetsオブジェクトを返す。
• WorksheetsプロパティもSheetsオブジェクトを返す。
• Worksheetsという名称のオブジェクトは存在しない。
• Worksheetオブジェクトは存在するが、Sheetオブジェクトは存在しない。

※sの有無に気を付けて。

以上