Range(Cells,Cells)の親シートを指定する方法

Range(Cells,Cells)の記述によりセル範囲を取得する際、
その親シートを指定する方法を解説します。

基本形

RangeプロパティにCellsプロパティを二つ渡し、

Range(Cells(r1, c1), Cells(r2, c2))

という記述でセル範囲を指定することができます。

これをActiveSheetとは別のシートに対して行いたい場合は、

• Cells2つにシートの指定を付ける
• RangeとCells2つの計3つすべてにシートの指定を付ける

のいずれかで指定してください。

' 中身のCellsの両方にシート指定を付ける
Range(Worksheets("○○").Cells(r1, c1), Worksheets("○○").Cells(r2, c2))

' Range,Cells1,Cells2の3つすべてにシート指定を付ける
Worksheets("○○").Range(Worksheets("○○").Cells(r1, c1), Worksheets("○○").Cells(r2, c2))

いずれの方法でも「Cellsのシート指定は必ず必要になる」というのが重要で、
直観的に書いてしまいがちな「先頭のRangeだけシートを指定する」このコード↓は、

Worksheets("○○").Range(Cells(r1, c1), Cells(r2, c2))

以下のいずれかのエラーになります。

• アプリケーション定義またはオブジェクト定義のエラーです
• Rangeオブジェクトは失敗しました

といいつつ「必ずエラーになるわけではない」というがまた厄介なポイントで、
「ActiveSheetがたまたまWorksheets("○○")だと成功してしまう」
というのもひとつの罠になります。

成功したりしなかったりするコードは調査と対策がかなり面倒ですので、
しっかりこの書き方のルールを覚えておきましょう。

シート指定の短縮方法

さてルールは上記の通りでしたが、

Worksheets("○○").Range(Worksheets("○○").Cells(r1, c1), Worksheets("○○").Cells(r2, c2))

こんなコードはいちいち書いていられません。

このシート指定を短縮する方法はいくつかありますが、
まずおすすめしたいのが以下の2つです。

「.」のみでシートを指定できるようになるWithステートメント

With Worksheets("○○") ' ← ここから先は「.」だけでこのシートを意味するようになる

    .Range(.Cells(r1, c1), .Cells(r2, c2)) ' ← 「.」を3つつけるだけでOK

End With ' Withの設定ここまで

ワークシートを変数に格納

Dim ws As Worksheet
Set ws = Worksheets("○○") ' ← ここから先は変数wsでこのシートを指定できる

ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2)) ' ← wsを3つつけるだけでOK

この2つの方法をとると、お手軽にコードを読みやすくできます。

Withの方が簡単ですが、Withはひとつのシートしか扱えませんので、

• 1つのシートしか扱わないマクロであればWith
• 2つ以上のシートを扱うマクロなら変数

と使い分けて利用してみてください。

また、もう一つ見方を変えた方法として、
取得したセル範囲をRange変数に入れるという方法もあります。

' 取得したい範囲を変数に入れる
Dim 処理範囲 As Range
Set 処理範囲 = Range(Worksheets("○○").Cells(r1, c1) _
                              , Worksheets("○○").Cells(r2, c2))

' その変数へ処理を書くことで読みやすくなる
処理範囲.ClearContents

' 特に何度も出てくるときに使いまわせるのが便利
処理範囲.Interior.ColorIndex = 0
処理範囲.Borders.LineStyle = True

この例では「セル範囲をクリア⇒背景色も消す⇒罫線も消す」と処理していますが、
かなり読みやすくなっているのがわかると思います。

この方法であれば、最初の1行くらいはシート指定がしつこくても気になりません。

ちなみに例のように、「_」を使って複数行に改行して書くと、
そこそこ見やすくできますので活用してください。

シートの指定についての詳細はこちらの記事をご参考ください。
www.limecode.jp

なお、もう一つ裏技的な方法として、
対象シートをActiveSheetにしてしまうという方法もあります。

Worksheets("○○").Activate ' ← Activateでシートを選択してしまう

Range(Cells(r1, c1), Cells(r2, c2)) ' ← これで動くようになる

ただしこの方法は、アクティブシートが変わる処理を書いてしまう不具合や、
「マクロ実行中のユーザーのクリック」による不具合も発生します。

使い捨てのマクロをサッと書くときくらいに限定し、
規模が大きいマクロではなるべく使用しないようにしましょう。

Rangeのシート指定は必要？

さて今回の書き方のルールとしては、

• Cells2つにシートの指定を付ける
• RangeとCells2つすべてにシートの指定を付ける

このどちらかで書く必要がありました。

言い換えると「Cellsにシート指定があればRangeには不要」ということになります。

ではRangeには書く必要がないかというと、やはりシートの指定は先頭にあった方が、
どのシートの処理なのかすぐわかって読みやすいというのがあります。

シートの指定を短縮さえしていれば

Range(ws.Cells(r1, c1), ws.Cells(r2, c2))
ws.Range(ws.Cells(r1, c1), ws.Cells(r2, c2))

この程度の差でしかありませんので、
これだと下の方が読みやすいのではないでしょうか？

このあたりは好みが出る部分でもあると思いますので、
自分なりに読みやすい方法を研究してみてください。

おまけ：意外な解決策

今回のエラーには意外な解決策があり、

Worksheets("○○").Range(Cells(r1, c1).Address, Cells(r2, c2).Address)

と、Cells2つに「.Address」を付けてもちゃんと動きます。

これは単純に、

Worksheets("○○").Range("A1", "B2")

という処理を書いたのと同じ結果になるからですね。

• Addressを取得したセルはActiveSheetのもの
• 取得するセル範囲RangeはWorksheets("○○")のもの

と、相変わらず違うシートを指定してしまっていますが、
Cells(1, 1)のアドレスはどのシートでも当然A1ですので問題ないのです。

わざわざこの方法をとる意味は全くないためただの小ネタですが、
「プログラミングには無数のやり方があり正解はない」
ということは意識しておきましょう。

そういう柔軟な考え方ができるとコーディングが上達しますし、
いつか将来、この書き方に助けられる日が来るかもしれません。