Home » Excel VBA Rangeオブジェクト » CurrentRegionプロパティ » CurrentRegionで取得したRangeコレクションから単独のRangeを取得する場合の謎

CurrentRegionで取得したRangeコレクションから単独のRangeを取得する場合の謎

対象：Excel2010, Excel2013, Windows版Excel2016

RangeオブジェクトのCurrentRegionプロパティで取得した、アクティブセル領域を表すコレクションとしてのRangeオブジェクトから、最終セルを表すRangeオブジェクトを取得するコードをご紹介しています。

このコレクションとしてのRangeから単独のRangeを取得するコードで、不思議な挙動がありますので記事にしておきます。

~~なぜこのような挙動をするのか残念ながら今の私には説明できませんが。~~追記あり 7/21 6:40

［スポンサードリンク］

CurrentRegionの先頭セルを取得するサンプル

最終セルに限らず、アクティブ領域から単独のセルを取得する場合に見られる挙動ですので、先頭セルを取得するコードを見てみます。

Sub CurrentRegionから単独セル_シート指定なし()
　MsgBox _
　　　Range("A1").CurrentRegion(1).Address(False, False)
End Sub

上記のSubプロシージャを実行すると、A1セルを含むアクティブセル領域の先頭セルのアドレスである「A1」がメッセージボックスに表示されます。

ワークシート指定のCurrentRegionから単独セル取得時にエラーが発生する

ところが、以下のようなSubプロシージャを実行した場合には、
「実行時エラー'450' 引数の数が一致していません。または不正なプロパティを指定しています。」
と実行時エラーが発生してしまいます。

Sub CurrentRegionから単独セル_ActiveSheetプロパティ_実行時エラー()
　MsgBox _
　　　ActiveSheet.Range("A1").CurrentRegion(1).Address(False, False)
End Sub

先ほどのコードに「ActiveSheet.」をつけただけにも関わらず。

以下のSubプロシージャでも同じく実行時エラーが発生します。

Sub CurrentRegionから単独セル_Sheetsプロパティ_実行時エラー()
　MsgBox _
　　　Sheets(1).Range("A1").CurrentRegion(1).Address(False, False)
End Sub

つまり、RangeオブジェクトのCurrentRegionプロパティで取得したコレクションとしてのRangeオブジェクトから、Rangeオブジェクトの既定プロパティを使って単独のRangeオブジェクトを取得しようとする場合に、ActiveSheetプロパティやSheetsプロパティを使ったコードを前に書くと実行時エラーが発生するということです。

しかし、以下のSubプロシージャならば正常に動作します。

Sub CurrentRegionから単独セル_オブジェクト名でシート指定()
　MsgBox _
　　　Sheet1.Range("A1").CurrentRegion(1).Address(False, False)
End Sub

ワークシートのオブジェクト名（Worksheet.CodeName）が「Sheet1」のケースです。

ActiveSheetやSheetsなどのプロパティを使ってWorksheetオブジェクトを取得するのではなく、オブジェクト名を直接指定すればOKということです。

ワークシート指定のCurrentRegionから単独セル取得時にエラーが発生しないように

ところが、以下のようにItemプロパティをちゃんと書けば、ActiveSheetやSheetsなどのプロパティを使ってWorksheetオブジェクトを取得した場合にも、実行時エラーは発生しません。

Sub CurrentRegionから単独セル_ActiveSheetプロパティ()
　MsgBox _
　　　ActiveSheet.Range("A1").CurrentRegion.Item(1).Address(False, False)
End Sub

Sub CurrentRegionから単独セル_Sheetsプロパティ()
　MsgBox _
　　　Sheets(1).Range("A1").CurrentRegion.Item(1).Address(False, False)
End Sub

コレクションオブジェクトから単独のオブジェクトを取得するItemプロパティについて、知っておかないとまずいということです。

CellsでもOK

ItemプロパティではなくCellsプロパティを使って、
　　ActiveSheet.Range("A1").CurrentRegion.Cells(1).Address(False, False)
　　Sheets(1).Range("A1").CurrentRegion.Cells(1).Address(False, False)
としてもOKです。

_Defaultではダメ

ちなみに、既定プロパティを明示した以下の書き方では、
　　ActiveSheet.Range("A1").CurrentRegion.[_Default](1).Address(False, False)
　　Sheets(1).Range("A1").CurrentRegion.[_Default](1).Address(False, False)
やっぱり実行時エラーが発生します。

追記 7/21 6:40

この記事をご覧いただいた、いみひとさんが解決策を教えてくださいました。

`ActiveSheet.Range("A1").CurrentRegion()(1).Address(False, False)`
のように`CurrentRegion`の後ろに`()`をつけて、CurrentRegionには引数を指定していないことを明示すると、無事解決できる模様
— いみひと (@nukie_53) 2018年7月20日

ありがとうございます！！確かに、
　　　ActiveSheet.Range("A1").CurrentRegion()(1).Address(False, False)
あるいは、
　　　Sheets(1).Range("A1").CurrentRegion()(1).Address(False, False)
ならばOKです。

多くのExcel VBAユーザーは、「.CurrentRegion()(1)」といった書き方を見慣れないでしょうから、コレクションとしてのRangeオブジェクトから単独のRangeオブジェクトを取得するItemプロパティを、いずれかの段階でしっかりお伝えするほうがイイという思いを強くしています。