Worksheets(Index).でインテリセンスが効かない理由

Excel VBA（Visual Basic for Applications）にある程度慣れた方から、
「ワークシートから指定する書き方をしたときに、インテリセンスが表示されないのは何故なのでしょう？」
といったご質問をいただくことがあります。

Worksheetオブジェクトではなく、
Workbookオブジェクトから指定するオブジェクト式を書いた場合、インテリセンス機能が効いて、下図のように自動メンバー表示が行われます。

ところが、
Worksheetオブジェクトから指定して「Worksheets(1).」といったオブジェクト式を書いた場合には、自動メンバー表示が行われません。

この理由を知りたいというのが、
「ワークシートから指定する書き方をしたときに、インテリセンスが表示されないのは何故なのでしょう？」
といったご質問の主旨です。

最初に結論を申し上げると、「Worksheets(Index).」というオブジェクト式では、返されるオブジェクトが曖昧だからです。

Workbookオブジェクトでインテリセンスが効く理由

VBE（Visual Basic Editor）に付属する便利ツール・オブジェクトブラウザーを使って理解しましょう。

まずは、インテリセンスが機能するWorkbookオブジェクトを確認してみましょう。

Workbookオブジェクトを、Workbooksコレクションオブジェクトから取得する場合、コード上はWorkbooksプロパティから記述します。

Workbooksプロパティをオブジェクトブラウザーで確認すると、の詳細ペインに「As Workbooks」と、戻りがWorkbooksオブジェクトであることが明示されています。

つづいて、Workboksプロパティで返される、Workbooksコレクションオブジェクトの、既定メンバーを確認すると「As Workbook」とWorkbookオブジェクトが返されることが明示されています。

つまり「Workbooks(Index).」というオブジェクト式は、必ず、Workbookオブジェクトが返されると決まっているわけです。

コードの入力途中に表示される自動クイックヒントにも、

「_Default(Index) As Workbook」と、Workbooksオブジェクトのデフォルトメンバーである「_Default」の戻りが「Workbook」であることが明示されています。

「Workbooks(1).」といったオブジェクト式に続くのは、必ずWorkbookオブジェクトのプロパティかメソッドですから、インテリセンス機能が動いて、Workbookオブジェクトの持つメンバーが自動的に表示されるわけです。

Worksheets(Index).でインテリセンスが働かない理由

続いてこの記事の本題、「Worksheets(Index).」というオブジェクト式ではインテリセンスが効かない、自動メンバーが表示されない理由です。

Worksheetsプロパティをオブジェクトブラウザーで確認すると、戻りは「As Sheets」とSheetsコレクションオブジェクトが返されると定義されています。

「As Worksheets」とはなっていないのが重要なポイントです。

なぜMicrosoftがここを「As Worksheets」という形で作っておいてくれなかったのか疑問として残りますが、Worksheetsプロパティの戻りはWorksheetsコレクションオブジェクトではなく、Sheetsコレクションオブジェクトになっています。

そして、Sheetsコレクションオブジェクトの既定のプロパティを確認すると「As Object」と、何らかのオブジェクトが返されると定義されています。

Sheetsコレクションオブジェクトには、ワークシートを表すWorksheetオブジェクトも、グラフシートを表すChartオブジェクトも含まれていますから、Sheetsオブジェクトの既定メンバーが何らかのObjectを返す作りになっているのはやむを得ません。

つまり「Worksheets(Index).」というオブジェクト式は、コードを書いているヒトからすると、必ずWorksheetオブジェクトを取得しているように見えますが、実はそうではなくObjectを返すように作られてしまっているのです。

「Worksheets(Index).」というオブジェクト式だけはどういうオブジェクトなのかがわからないため、その後ろに続くオブジェクト式にも何が続くのかを決められません。
ですから、自動メンバー表示も行われないのです。

Worksheetオブジェクトのインテリセンスを利用するには

ユーザーとしては対策のほうが大切かもしれません。

インテリセンスを利用したいのなら、「Worksheets(Index).」というオブジェクト式を直接書くのではなく、Worksheet型のオブジェクト変数を利用しましょう。

上記のプロシージャのように、まずWorksheet型のオブジェクト変数を宣言しておいてから、
　　Dim sh As Worksheet
そのオブジェクト変数に「Worksheets(1)」をセットしてやれば、
　　Set sh = Worksheets(1)
オブジェクト変数・shはWorksheetと宣言されているので、下図のとおり自動メンバー表示が行われます。