知らずに落ちると抜け出せなくなるVBAの落とし穴です。
- For EachでRangeオブジェクトをループしたとき、全セルが処理されない
- Rangeオブジェクト.Countがセルの数を返さない
あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。
マクロのサンプル：セルに連番を出力する
Rangeオブジェクトの中にあるすべてのセルを、
1つずつループで処理したいとします。
とりあえず、簡単に「連番を出力」してみましょう。
Sub 選択セルに連番を出力する() Dim i As Long: i = 1 Dim セル As Range For Each セル In Selection セル.Value = i i = i + 1 Next End Sub
実行例
このように、「For Each ○ In Rangeオブジェクト」で、「Rangeオブジェクトの中のすべてのセルを処理」することができます。
実行例のように、Ctrlキーで複数選択したセルもOK。
Rangeオブジェクトの指定を引数にして汎用化
さて、サンプルマクロは「選択セル＝Selection」に対して実行していました。
これを汎用化して、「指定のRangeオブジェクト」に実行できるようにしましょう。
Sub 指定のセル範囲に連番を出力する(指定セル範囲 As Range) Dim i As Long: i = 1 Dim セル As Range For Each セル In 指定セル範囲 セル.Value = i i = i + 1 Next End Sub
なんてことはないですね。Selectionを、関数の引数「指定セル範囲」に書き換えただけです。
Call 指定のセル範囲に連番を出力する(Selection)
とすれば、さっきのコードと同じ処理をしてくれます。
発生するバグ
さて、↑のコードは、実は既にタイトルの罠にかかっています。
これが分かる方は、かなり上級者なんじゃないかと思います。
A1:C1の、横3セルのブロックに実行してみましょう。
Call 指定のセル範囲に連番を出力する(Range("A1:C1"))
いけますね。
じゃあこのブロックをこうやって指定してみましょう。
Call 指定のセル範囲に連番を出力する(Range("A1:C100").Rows(1))
はいダメでした。
Range("A1:C100").Rows(1) は、3×100の長方形の、第1行部分なので、
間違いなくRangeオブジェクトで、.Addressは"A1:C1"のはずです。
関数中にブレークポイントを設置してFor Each前に停止し、
関数に渡された引数「指定セル範囲」を確認しても、そうなっています。
◇ ウォッチウィンドウで確認
◇ イミディエイトウィンドウで確認
確かにA1:C1です。でもダメなんですね。
解決策
解説がすごく複雑なので、先に解決策を。
For Each セル In 指定セル範囲.Cells
と、「.Cells」をつけてあげるだけでOKです。
なぜこのバグが発生するのか
先に断っておきますと、これはExcelのバグではありません。
Rangeのちゃんとした仕様なので、バグとは、このFor Eachを書いた人のバグです。
この理由を説明するのが難しくてですね。
正確な説明ではないと前置きして、超ざっくり説明します。
こうなってしまう原因は、「Range」が、「プロパティ」であり、「オブジェクト」であり、「コレクション」でもあることに起因しています。
しかも、このうちのどれかになるというわけではなく、Rangeオブジェクトのコレクションが、Rangeオブジェクトであったりするのです。
わかりやすいように、Rangeオブジェクト.Countの実行例で説明します。
Range("A1:C1").Count ' =3 Range("A1:C100").Rows(1).Count ' =1 Range("A1:C100").Rows(1).Cells.Count ' =3
「Countプロパティ」は、正確には「コレクションの」数を返します。
2行目の「.Count」は、「1」を返していますね。
「行が1つ」とイメージすると、なんとなくつかめるかもしれません。
つまりRowsプロパティは、
「セルのコレクションであるRangeオブジェクトを、
行ごとに区切って持つ、いわばコレクションのコレクション」
であり、
- 範囲全体は行のコレクション
- 行はセルのコレクション
ということなのです。
For Eachは直下のコレクションを走査しますので、行ごとにEachされて、
全部「1」になったというわけですね。
この「範囲全体」「行」「セル」が、
すべてRangeオブジェクトなので混乱するんですよ(笑)
Rowsは、Rangeオブジェクト(セル)のコレクションからなるRangeオブジェクト(行)のコレクションを、Rangeオブジェクトで返すプロパティなのです。
まだ？？？状態かもしれないので、試しに、
Call 指定のセル範囲に連番を出力する(Range("A1:C100").Rows("1:3"))
と、３×３の正方形を、3つのRowsで持ってそうなRangeに実行してみましょう。
こうなります。
この結果を眺めると、このFor Each が
「行ごとに区切られたRangeオブジェクトのコレクション」
を走査しているのが、かすかにイメージできるでしょうか？
ちなみに、勘が鋭い人は、
エリアごとに区切っても同じ問題が起きるのか？と疑ったかもしれません。
しかし、
Range("A1:C1,A2:C2,A3:C3").Count ' =9
こんな風に、,で区切って「3行」っぽくしても、Countはセルの数になります。
Range("A1:C1,A2:C2,A3:C3").Areas.Count " =3
で見るとわかるように、Areasは3つあるけど、
コレクションとしては、9個のセルからなるコレクションなんですね。
逆にRowsプロパティで取得したRangeオブジェクトは、
Range("A1:C100").Rows("1:3").Areas.Count ' =１
です。3つのセルからなるコレクションが3つあるコレクションですが、
Areasは1つなんです。ややこしい(笑)
まあこの辺にしておきますね。
今までの話は全部忘れて、
- Rangeは、実は奥が深い
- 心配なら.Valueとか省略するな
- For Eachには、.Cellsを必ずつけとけ
という理解で、最初はいいと思います。
ちなみに冒頭の「For Each セル In Selection」は、OKです。
Selectionは、行全体、列全体を選択していても、
必ず単一のRangeオブジェクトを返しますので、
Selection.Cellsにする必要はありません。
とはいっても、これも仕様を理解しての話ですので、
Cellsを使ておいた方が無難でしょう。
今度はCellsプロパティに仕掛けられた罠
CellsをつければOK。
なんですが、万能ではないどころか、逆にあぶない罠があります。
Sub 指定のセル範囲に連番を出力する2(指定セル範囲 As Range) Dim i As Long For i = 1 To 指定セル範囲.Cells.Count 指定セル範囲.Cells(i).Value = i Next End Sub
For Each は使わずに、1からセルの数までForでループします。
Cells(i)で、i番目のセルを処理しているように見えます。
ですが、こうなります。
CellsはCellsで、
「引数を指定したときと、省略したときで、まったく別物のプロパティ」
という罠があります。
※より厳密には、Cellsは引数をもちません。Cells(1,1)は、Cells.[_Default](1,1)を省略したという扱いになります。
Cellsはセルが集まったコレクション「Rangeオブジェクト」を返しますが、
Cells(番号を指定)は、そのコレクションからとってきたRangeオブジェクトを返すわけではないのです。
Range("A1").Cells.Count ' = 1 Range("A1").Cells(2).Address ' = "A2"
Count数をオーバーして書けちゃいますからね。
Cells(番号を指定)は、Cells.1個目のセル.Offset(番号を指定-1)の省略形
というのが、正しくはないですがそこそこ納得しやすい解釈だと思います。
余談ですが、
この「Cellsの時にプロパティの候補が表示されない」理由がこれだそうですよ。
まとめ
長くなりましたが、超ざっくりいうと
- Rangeは、実は奥が深い
- 心配なら.Valueとか省略するな
- For Eachには、.Cellsを必ずつけとけ
- Cells(番号)は「Rangeの何番目」じゃなくて「Offset」
です。
オブジェクトの扱いにまだ慣れていない方は、
何を言っているかわからない場所も多々あったと思います。
オブジェクトの理解が深まったとき、
急に霧が晴れたように理解できるかもしれませんので、
メジャーなWorksheetsあたりで訓練していきましょう。
またのご来訪を、お待ちしております。
