3日がかりのその仕事、3分で終わらせる方法教えます!
パソコンスキルの心技体

For Each構文の「処理される順番」 – Excelマクロ・VBA

2012年1月19日
  • このエントリーをはてなブックマークに追加
  • follow us in feedly

エクセルマクロ・VBA達人養成塾 小川です。

キューバ旅行記、その86です。

エクセルマクロ達人養成塾塾長ブログ-途中のバス休憩所にて。

途中のバス休憩所にて。

エクセルマクロ達人養成塾塾長ブログ

エクセルマクロ達人養成塾塾長ブログ-キューバらしい自家用車

キューバらしい自家用車

エクセルマクロ達人養成塾塾長ブログ-甘いフラッペ。

甘いフラッペ。

エクセルマクロ達人養成塾塾長ブログ-ということで、休憩所を後に。

ということで、休憩所を後に。

明日は、いよいよ、ハバナに戻ります。

塾長のキューバ旅行記、最初から読みたい方はこちらから


For Each構文の「処理される順番」 – Excelマクロ・VBA

今日は、For Each構文の「処理される順番」というお話。

何かというと。

前回のブログで、こんな↓サンプルコードを紹介したあと

Sub SetNum()
    Debug.Print Now
    
    Range(“C1”).Value = 1
    Range(“C2”).Value = 2
    Range(“C1:C2”).AutoFill Destination:=Range(“C1:C65536”), Type:=xlFillDefault
    
    Debug.Print Now

    Dim c As Long
    For c = 1 To 65536
        Range(“D” & c).Value = c
    Next
    
    Debug.Print Now
    
    Dim i As Long
    i = 1
    Dim r As Range
    For Each r In Range(“E1:E65536”)
        r.Value = i
        i = i + 1
    Next
    
    Debug.Print Now
End Sub

こんな↓ことを書きました。

こういう作業をするには実はFor Eachは不適切なので、比較対象から除外。(その理由は、明日のブログで)

この件について、解説。

例えば、以下のようなデータがあったとする。


|B列 |C列 |D列 |E列 |F列 |G列 |H列 |
---------------------------------------------------
2 行目 |id |t1 |t2 |t3 |t4 |t5 |t6 |
---------------------------------------------------
3 行目 |1 |71 |53 |58 |29 |30 |77 |
---------------------------------------------------
4 行目 |2 |1 |76 |81 |71 |5 |41 |
---------------------------------------------------
5 行目 |3 |86 |79 |37 |96 |87 |6 |
---------------------------------------------------
6 行目 |4 |95 |36 |52 |77 |5 |59 |
---------------------------------------------------
7 行目 |5 |47 |30 |62 |65 |26 |28 |
---------------------------------------------------
8 行目 |6 |83 |82 |59 |99 |91 |23 |
---------------------------------------------------
9 行目 |7 |70 |98 |24 |53 |11 |100 |
---------------------------------------------------
10行目 |8 |68 |2 |58 |10 |10 |80 |
---------------------------------------------------
11行目 |9 |28 |5 |30 |38 |30 |95 |
---------------------------------------------------
12行目 |10 |98 |40 |28 |16 |16 |65 |
---------------------------------------------------
13行目 |11 |41 |41 |71 |33 |63 |21 |
---------------------------------------------------
14行目 |12 |19 |58 |8 |46 |91 |26 |
---------------------------------------------------
15行目 |13 |79 |38 |29 |92 |63 |63 |
---------------------------------------------------
16行目 |14 |43 |10 |56 |69 |91 |83 |
---------------------------------------------------
17行目 |15 |2 |54 |92 |43 |68 |50 |
---------------------------------------------------
18行目 |16 |51 |46 |35 |40 |27 |6 |
---------------------------------------------------
19行目 |17 |24 |98 |6 |39 |36 |49 |
---------------------------------------------------
20行目 |18 |16 |47 |26 |63 |54 |16 |
---------------------------------------------------

このとき、セル範囲「C3:H20」にあるセルについて、値が70を超えていたら、
セルの背景に色をつけ、フォントを太字にするとしよう

マクロで処理すると、以下のようになる。

Sub CheckValues()
Dim r As Range
For Each r In Range(“C3:H20”)
If r.Value >= 70 Then
r.Interior.ColorIndex = 17
r.Font.Bold = True
Debug.Print r.Address
End If
Next
End Sub

で、ステップインモードでこのマクロを実行していくと、以下の順番で処理されていくのが分かる。

出力結果1
セルC3
セルH3
セルD4
セルE4
セルF4
セルC5
セルD5
セルF5
セルG5
セルC6
セルF6
セルC8
セルD8
セルF8
セルG8
セルC9
セルD9
セルH9
セルH10
セルH11
セルC12
セルE13
セルG14
セルC15
セルF15
セルG16
セルH16
セルE17
セルD19

つまり、上の行から、かつ、左にあるセルから、順番に処理をしていっているように見受けられる。

あるいは。

表計算用のシートが10枚あったとする。

シート名は、左から順に、Sheet1, Sheet2, Sheet3, … Sheet10。

このとき、「各シートで順番に処理をする」というマクロを作るとなると、以下の要領。

Sub CheckAllSheets()
Dim w As Worksheet
For Each w In Worksheets
w.Tab.Color = vbRed
Debug.Print w.Name
Next
End Sub

このマクロを実行していくと、以下の順番で処理されていくのが分かる。

出力結果2
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6
Sheet7
Sheet8
Sheet9
Sheet10

ここで、シートの配置を替えてみよう。

左から順番に、Sheet10, Sheet9, Sheet8, … という位置関係にしてみる。

その状態で先ほど紹介したCheckAllSheetsを実行すると、以下のようになる。

出力結果3
Sheet10
Sheet9
Sheet8
Sheet7
Sheet6
Sheet5
Sheet4
Sheet3
Sheet2
Sheet1

で。

こういうサンプルをセミナーで見せると、ほぼ必ず、と言っていいくらいに出てくる質問があります。

「For Eachって、どういう順番でセルなり、シートなりを処理していくものなんですか?」

つまり、

「何度やっても、出力結果1の順番で処理されていくのか?(すなわち、いつも、「上の行から、かつ、左にあるセルから」という順番なのか?)」とか、

「シートは、いつも、左にあるものから順に処理されていくのか?」とか、

そういう質問なんですが。

えー。

結論から言いますと。

For Each構文では、どのオブジェクトから作業を開始するかという順番に特に決まりはなく、仕様としては、「手当たり次第」に作業が実行されていくことになっています。
なので、原理的には、同じサブプロシージャを何度も実行すると、実行する度ごとに処理されるオブジェクトの順番が異なる可能性があります。

つまりどういうことかというと。

例えば上に紹介したサブプロシージャ「CheckAllSheets」であれば、

今は、たまたま一番左にあるシートから順番に作業が行われましたが、次にもう一度実行したとき、またこの順番で作業されるとは限らないということです。

とはいえ。

それはあくまで、「仕様」でして。

僕自身の経験としては、Excel VBAでFor Each構文を実行して、「まったく同じ状況なのに、実行の度に順序が違った!」という経験をしたことはありません ヾ(´―`)ノ
上記の話は、Excel VBAだけを扱っている限りは、「ITリテラシーとして、いちおう知っておいてください」という程度です。

なんですが。

やはり、仕様的に保証されていないことをするのは、怖いですよね。

なので。

例えば、「以下のようなデータがあって、3行目のデータから、idを『1, 2, 3, … 』と順番に割り振っていきたい」


|B列 |C列 |D列 |E列 |F列 |G列 |H列 |
---------------------------------------------------
2 行目 |id |t1 |t2 |t3 |t4 |t5 |t6 |
---------------------------------------------------
3 行目 | |71 |53 |58 |29 |30 |77 |
---------------------------------------------------
4 行目 | |1 |76 |81 |71 |5 |41 |
---------------------------------------------------
5 行目 | |86 |79 |37 |96 |87 |6 |
---------------------------------------------------
6 行目 | |95 |36 |52 |77 |5 |59 |
---------------------------------------------------
7 行目 | |47 |30 |62 |65 |26 |28 |
---------------------------------------------------
8 行目 | |83 |82 |59 |99 |91 |23 |
---------------------------------------------------
9 行目 | |70 |98 |24 |53 |11 |100 |
---------------------------------------------------
10行目 | |68 |2 |58 |10 |10 |80 |
---------------------------------------------------
11行目 | |28 |5 |30 |38 |30 |95 |
---------------------------------------------------
12行目 | |98 |40 |28 |16 |16 |65 |
---------------------------------------------------
13行目 | |41 |41 |71 |33 |63 |21 |
---------------------------------------------------
14行目 | |19 |58 |8 |46 |91 |26 |
---------------------------------------------------
15行目 | |79 |38 |29 |92 |63 |63 |
---------------------------------------------------
16行目 | |43 |10 |56 |69 |91 |83 |
---------------------------------------------------
17行目 | |2 |54 |92 |43 |68 |50 |
---------------------------------------------------
18行目 | |51 |46 |35 |40 |27 |6 |
---------------------------------------------------
19行目 | |24 |98 |6 |39 |36 |49 |
---------------------------------------------------
20行目 | |16 |47 |26 |63 |54 |16 |
---------------------------------------------------

というようなときには、For Each構文を使うのは不適切、ということになります。

こういう課題を解決するのにFor Each構文を使っているソースをときどき見かけるのですが。

たとえ狙い通りの挙動をしたとしても。
ITリテラシーがあまり高くなさそうな印象を持たれる可能性があるので。

やめたほうが無難…と思います。

結論:
「For Each構文」では、実は、オブジェクトにアクセスの順番が保証されていません。
その点、VBからプログラミングの世界に入った方には、要注意です。

「手当たり次第」の処理では困るときには、For Each構文は使わず、
For Next構文か、Do Loop構文を使うようにしましょう。

最後に、おまけとして、上記の課題をFor Next構文、Do Loop構文で解決する場合のサンプルコードを紹介しておきます。

データの数があらかじめ分かっているとき:

Sub ForNext1()
Dim c As Long
For c = 3 To 20
Range(“B” & c).Value = c – 2
Next
End Sub

Sub DoLoop1()
Dim c As Long
c = 3
Do While c < 21
Range(“B” & c).Value = c – 2
c = c + 1
Loop
End Sub

データの数があらかじめ分かっていないとき:

Sub ForNext2()
Dim c As Long
For c = 3 To Range(“C” & ActiveSheet.Rows.Count).End(xlUp).Row
Range(“B” & c).Value = c – 2
Next
End Sub

Sub DoLoop2()
Dim c As Long
c = 3
Do Until IsEmpty(Range(“C” & c))
Range(“B” & c).Value = c – 2
c = c + 1
Loop
End Sub

ではでは (^^)/~


お知らせ:

人気のセミナー2つを、久しぶりに開催します。受講受付開始しました。ふるってご参加ください☆

「親指シフト達人養成塾」
「エクセルデータ分析7つの上級技」 (あと2名)

「エクセルデータ分析」は、ほとんどリピータの方だけで埋まってしまいまして、早くも、残席2つだけとなりました。

親指シフトも、あと5名くらいです。

●塾長のTwitterはこちらです↓。フォローお待ちしていますね。
 http://twitter.com/kanjizaibosatsu

●無料PDFレポート「誰もが知っているWindowsの、誰も知らない12の技」
 http://www.exvba.com/freereport/index.php

●法人研修のお問い合わせはこちら
 https://sv86.wadax.ne.jp/~exvba-com/closed/toiawase_houjin.php

●ジーザス小川の個人サイト「こねこねのさいと」へはこちらから
 http://www.exvba.com/

キーワード

コメント

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

最新の記事

人気記事

最新記事

カテゴリ

最新コメント

タグクラウド