For NextよりAutofillのほうがかなり高速！ – Excelマクロ・VBA

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

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

バラデロからハバナへの高速バスにて。

マタンザスの駅前で、乗客のピックアップがあって、小休止。

マタンザス駅付近はこんな感じ。

マタンザス地元の人たち。

地元の人が乗るバスはこんな感じ。
塾長のキューバ旅行記、最初から読みたい方はこちらから

For NextよりAutofillのほうがかなり高速！ – Excelマクロ・VBA

今日は、Autofillのお話。

「表のいちばん左の列にidを振る」とか、「日付を順番に入れていく」といった作業をすることを考える。

まずは、単純に、idを振る場合。それも、「1, 2, 3, … 」と、1からはじまる番号を順番に入れていく場合。

まず、ベタなやり方。For Next構文で書いたら、こんな感じ↓

Sub ForNextSample()

Dim c As Long

For c = 1 To 65536

Range("A" & c).Value = c

Next

End Sub

For Each構文なら、以下。

Sub ForEachSample()

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

ところで、エクセル画面上でオートフィル機能を使っても同様な値を入力できる。

具体的には、自動記録をしながらセルA1、セルA2に順番に1, 2と値を入れ、それからフィルハンドルをつかんでオートフィルを行う。

例えば、セルA1～A18まででオートフィルをしたなら、以下のマクロを得られる。

Sub Macro1()

'

' Macro1 Macro

' マクロ記録日 : 2012/1/18 ユーザー名 : 小川慶一

''

ActiveCell.FormulaR1C1 = "1"

Range("A2").Select

ActiveCell.FormulaR1C1 = "2"

Range("A1:A2").Select

Selection.AutoFill Destination:=Range("A1:A18"), Type:=xlFillDefault

Range("A1:A18").Select

End Sub

ここで、「”A1:A18″」となっている2カ所を、「”A1:A65536″」とすれば、「ForNextSample」と、比較できる。

ということで、以下のサンプルコードを作って、速さを比べてみた。

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

イミディエイトウィンドウに出力された値は、以下のとおり。

2012/01/18 10:16:43

2012/01/18 10:16:43

2012/01/18 10:16:53

2012/01/18 10:17:02

(手元のThinkPad X201s win7 pro, excel 2003 にて。マシンのベンチマークテストをしているわけではないので、マシンの細かいスペックについては省略)

オートフィルなら、1秒かからない。

For Next構文だと、10秒くらい。

For Each構文だと、9秒くらい。

ということで、オートフィルに軍配 ヾ(´ー｀)ﾉ

次に、日付を入力する場合についても比べてみる。以下、こういう作業をするには実はFor Eachは不適切なので、比較対象から除外。（その理由は、明日のブログで）

以下のサンプル。

Sub SetDate()

Debug.Print Now    Range("G1").Value = #1/19/2012#

Range("G2").Value = #1/20/2012#

Range("G1:G2").AutoFill Destination:=Range("G1:G65536"), Type:=xlFillDefault

Debug.Print Now

Dim c As Long

Dim d As Date

d = Date

For c = 1 To 65536

Range("H" & c).Value = DateAdd("d", c, d)

Next

Debug.Print Now

End Sub

イミディエイトウィンドウに出力された値は、以下のとおり。

2012/01/18 10:22:09

2012/01/18 10:22:09

2012/01/18 10:22:25

オートフィルなら、1秒かからない。

For Next構文だと、15秒ちかくかかる。
ということで、こちらでも、オートフィルに軍配 ヾ(´ー｀)ﾉ
結論。

活用できるときには、For Next構文よりも、Autofillを使いたい。

お知らせ:

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

「親指シフト達人養成塾」

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

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

ではでは (^^)/~