和風スパゲティのレシピ

日本語でコーディングするExcelVBA

For Each ○ In Range はセルごとのループとは限らない

知らずに落ちると抜け出せなくなる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"))

A1:C1に連番を出力

いけますね。



じゃあこのブロックをこうやって指定してみましょう。

Call 指定のセル範囲に連番を出力する(Range("A1:C100").Rows(1))

Rowsで取得したA1:C1に連番を出力
はいダメでした。


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×3の正方形を、3つのRowsで持ってそうなRangeに実行してみましょう。

Rowsで取得したA1:C3に連番を出力する

こうなります。

この結果を眺めると、この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 ' =1

です。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(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)の省略形

というのが、正しくはないですがそこそこ納得しやすい解釈だと思います。


余談ですが、

Rangeオブジェクトのプロパティ候補

Cellsのプロパティ候補


この「Cellsの時にプロパティの候補が表示されない」理由がこれだそうですよ。

まとめ

長くなりましたが、超ざっくりいうと

  • Rangeは、実は奥が深い
  • 心配なら.Valueとか省略するな
  • For Eachには、.Cellsを必ずつけとけ
  • Cells(番号)は「Rangeの何番目」じゃなくて「Offset」

です。

オブジェクトの扱いにまだ慣れていない方は、
何を言っているかわからない場所も多々あったと思います。

オブジェクトの理解が深まったとき、
急に霧が晴れたように理解できるかもしれませんので、
メジャーなWorksheetsあたりで訓練していきましょう。

またのご来訪を、お待ちしております。