エクセルマクロ・VBA達人養成塾 小川です。
キューバ旅行記、その76です。
キューバ屈指の外人向けリゾート地、バラデロにて、自転車でフラフラ移動中です。
バラデロの柔道場
柔道場の中にて
チェゲバラと嘉納治五郎の肖像写真
そして、また海 ヾ(´ー｀)ﾉ
地元の小さな郷土資料館2階から。
今日は、前回のブログ「複数セルでの処理を高速化したい-その2 Unionメソッドを使用する Excelマクロ・VBA」の内容に若干関連したネタを。
複数セルに対して連続処理をするとき。
For Next構文を使うか、For Each構文を使うか、はたまた、Do Loop構文を使うか、ということで選択を迫られることがあります。
…迫られるっていうか、好きに決めていいんですが。
例えば、以下の3つの構文は、どれも、2行目から11行目までのセルに対して、値を記入していきます。
Dim c As Long
For c = 2 To 11
Range(“A” & c).Value = c – 1
Next
End SubSub ForEach()
Dim r As Range
Dim c As Long
c = 1
For Each r In Range(“B2:B11”)
r.Value = c
c = c + 1
Next
End Sub
Sub DoLoop()
Dim c As Long
c = 2
Do Until c > 11
Range(“C” & c).Value = c – 1
c = c + 1
Loop
End Sub
そのとき、では、どの構文が一番高速か、というお話。
まずは、実験。
上記の例では処理するセルの数が10個しかなくて分かりにくいので、100万個にして試してみます。
以下で、サブプロシージャ「main」を実行してみます。
Dim dF As Date
Dim dT As Date
dF = Now
Dim c As Long
For c = 2 To 1000001
Range(“A” & c).Value = c – 1
Next
dT = Now
Debug.Print DateDiff(“s”, dF, dT)
End SubSub ForEach1000000()
Dim dF As Date
Dim dT As Date
dF = Now
Dim r As Range
Dim c As Long
c = 1
For Each r In Range(“B2:B1000001”)
r.Value = c
c = c + 1
Next
dT = Now
Debug.Print DateDiff(“s”, dF, dT)
End Sub
Sub DoLoop1000000()
Dim dF As Date
Dim dT As Date
dF = Now
Dim c As Long
c = 2
Do Until c > 1000001
Range(“C” & c).Value = c – 1
c = c + 1
Loop
dT = Now
Debug.Print DateDiff(“s”, dF, dT)
End Sub
Sub main()
ForNext1000000
ForEach1000000
DoLoop1000000
End Sub
さて、これで、どうなるかとというと…。
手元のマシンで、Excel 2007上で試したところ、イミィディエイトウィンドウには、以下のように出力されました。
For Next構文: 75
For Each構文: 61
Do Loop構文: 75
ということのようです。
For Each構文が一番速いみたいですね。
解説すると。
For Each構文は、ループに入るタイミングで、あらかじめ作業対象のセルの参照の集合を、メモリに読み込みます。
そして、その読み込まれた集合に対して処理をしていきます。
一方、For Next構文、Do Loop構文では、「Range(“C” & c)」といった構文に来る都度、改めて、そこで言われているセルがどこなのか？ということを調べにいきます。
その分のオーバーヘッド（IT用語: 間接的・付加的に必要となる処理とそれにより発生する負荷の大きさ）のせいで、時間がかかってしまうんですね。
てことで、基本は、For Each構文のほうがおすすめです。
あと、For Each構文のほうが、抽象度が高いので、よく分かっていない人に見せたとき、高度なプログラムを書いているように見られがちでもあります。
そういう意味でも、作り込むなら、For Each構文のほうがオススメです（笑
あ、でも、その場限りのちょっとしたデータ分析に使うだけの、使い捨てマクロを書くくらいでしたら、For Next構文のほうがたぶん簡単かも。
まあ、何らかの参考になれば、と思います。
昨日のブログ記事「複数セルでの処理を高速化したい-その2 Unionメソッドを使用する Excelマクロ・VBA」でご紹介したUnionメソッドを使った手法も、それが高速化につながる理由のひとつは、あらかじめ作業対象のセルの参照の集合を、メモリに読み込んでいるからです。
複数セルでの処理を高速化したい-その2 Unionメソッドを使用する Excelマクロ・VBA
前々回のブログ: 「複数セルに値を入れる処理を高速化したい」もあわせてお読みください。
そうそう。あと、お知らせが2つ。
人気のセミナー「エクセルデータ分析7つの上級技」 と、
知る人ぞ知るマイナーだけど高度なタイピングスキル「親指シフト」をマスターできる「親指シフト達人養成塾」を、
久しぶりに開催します。
今日から受講受付開始します。ふるってご参加ください☆
ではでは (^^)/~
