Excel VBA Rangeオブジェクトの書き方で困った方へ。: shigeの言いたい放題

2020年07月07日

Excel VBA Rangeオブジェクトの書き方で困った方へ。

調子に乗って早速VBAの記事　第2弾です。

体系的に書く気はさらさらありません（＾＾；

Tips的な事を思いついた順に書いていきます、、

今回は、Rangeの書き方。

一つ、または複数のセルを処理の対象とするときにCellsやRangeを使いますよね。

一つのセルの場合にはCellsでもRangeでもお好きな方を使えばいいのですが、ある連続した範囲の複数のセルを一度に操作しようとするときはRangeを使うしかないですね。

もちろん

Range("A1:B10")

のように一定の範囲を指定するなら特に問題はないのですが、その時の状況によって範囲を変えたいという場合はCellsを併用して

Range(Cells(1,1),Cells(10,2))

というような書き方をするしかないですね。

ここで、ときどきはまるのが

実行時エラー '1004':
'Range'メソッドは失敗しました: '_Worksheet' オブジェクト

と言う奴。

プログラムを何度見てもおかしくないし、デバッガで止めながら、操作をしているはずのシートを確認しながら動かしてみると何のことはなく動いてしまったり、、、

これって、実は、単にRange、Cellsと書いた時にはActiveSheet.Range、ActiveSheet.Cellsの略と見なされるのです。
（ま、もっと言うとActiveWorkbook.ActiveSheetなんですけどね。）

なので、標準モジュールで

Sheet2.Range(Cells(1, 1), Cells(10, 2)).ClearContents

と書いて、このコードが走るときにSheet1がアクティブになっていると、

Sheet2.Range(Sheet1.Cells(1, 1), Sheet1.Cells(10, 2)).ClearContents

と解釈されてしまいます。
Sheet2の範囲を指定するのにSheet1のセルを使用するというおかしなことになるのでエラーになるのです。

そして、デバッガで止めながら、Sheet2を見ながら動かすと、Sheet2がActiveSheetになるのでちゃんと動いてしまうのです。

なので、少々面倒ですが

Sheet2.Range(Sheet2.Cells(1, 1), Sheet2.Cells(10, 2)).ClearContents

または

With Sheet2
.Range(.Cells(1, 1), .Cells(2, 10)).ClearContents
End With

と書いたほうが安全ですね。

上記の通り、Sheet2もActiveWorkbook.WorkSheets("Sheet2")の略ですから、ブックもちゃんと指定したほうが良いですね。
私は大抵

Dim aSheet As Worksheet

Set aSheet = ThisWorkbook.Worksheets("Sheet2")

aSheet.Range(aSheet.Cells(1, 1), aSheet.Cells(10, 2)).ClearContents

こんな感じに書きますね。

でも、中にはActiveSheetを逆に利用する人もいますね。

つまり

WorkSheets("Sheet2").Activate

Range(Cells(1,1),Cells(10,2)).ClearContents

と書けばちゃんと動くのです。

でも、これって私は嫌いです。

常に.Activateとセットで書いてくれればいいのですが、
あるシートがアクティブな事を前提に何十行も書かれると、はて、これはどのシートの操作をしているのだろうというのが分かりにくいのです。

また、あとから何かの都合で、途中でほかのシートをアクティブにするコードを挿入したら途端に動かなくなるのです。

なので、ちゃんとブック、シートを指定したうえでRangeやCellsを使ってほしいです。