エクセルVBAに死角なし？ [システムトレード]
古い話で大変恐縮なのですが、昨年6月12日のコラムで、VBAを用いてワークシートにデータを書き出していくと、非常に時間が掛かるといった話をしました。
これは、一般的にVBAの弱点の一つとされているものなのですが、実は非常に高速にワークシートにデータを書き出す方法が存在します。
それは、VBAにおいて求めた2次元配列を、ワークシートの特定範囲に対応させる、という方法です。
VBAをきちんと勉強された方なら常識なのかもしれませんが、我流でVBAを勉強してきた私は、最近までこのような方法を知りませんでした。
通常、ワークシートの各セルにVBAで演算した結果等を書き出す場合は、ForNextステートメントで2重にループ処理を行うことが多いかと思います。
実際に行なったことのある方ならお分かりだと思いますが、この方法でシートにデータを書き出すと、異常に時間が掛かってしまいます。
例えば以下のようなマクロを実行してみます。
Sub test01()
Dim i As Integer, j As Integer, k As Integer
For k = 1 To 20
Range(Cells(1, 1), Cells(1000, 100)).ClearContents
For i = 1 To 1000
For j = 1 To 100
Cells(i, j) = i + j
Next j
Next i
Next k
End Sub
これは、ワークシートの1行1列から1000行100列まで、行+列の計算結果を記入する動作を20回繰り返すというものです。これを実行しますと、Core2Quad＠2.66GHｚ、RAM3GB、Windows VISTA、Excel2003の環境で、103.6秒ほど掛かりました。
このような簡単なデータの入力でさえ、これほどの時間が掛かるのですから、複雑なデータを入力する場合は、いったいどれくらいの時間が掛かるのか、想像すらできません。
では、これを冒頭に掲げた方法で実行してみましょう。そのマクロは次のようになります。
Sub test02()
Dim i As Integer, j As Integer, k As Integer
Dim cellData(999, 99) As Integer
For k = 1 To 20
Range(Cells(1, 1), Cells(1000, 100)).ClearContents
For i = 1 To 1000
For j = 1 To 100
cellData(i - 1, j - 1) = i + j
Next j
Next i
Range(Cells(1, 1), Cells(1000, 100)).Value = cellData()
Next k
End Sub
これも、test01と同様の計算結果をワークシートに記入するマクロですが、これを実行すると処理時間はわずか3.6秒ほどにしかなりません。
何と、test01の3.5%ほどの時間で終了いたします。30倍近い速度アップです。
これは、行と列に対するForNextステートメントの中で、計算結果を逐次セルに書き出していくのではなく、それを一旦cellData配列に書き出して、最後に一気にワークシートの全対象範囲に書き出しています。
配列の(0,0)を起点として、それをCells(1,1)に対応させているわけです。ここで注意すべき点は、配列は必ず2次元配列の必要があるということです。
あとは、その配列をワークシートの対象範囲に重ね合わせるわけです。
なお、今回の例では全てのデータが整数型で扱えましたので、それを宣言していますが、一般的に種々のデータ型が混在する場合は、Variant型で宣言する必要があります。
あと、配列に取り込めるのはデータのみですので、書式等は別に設定する必要があります。
それらにさえ気をつければ、これは非常に有用な方法となることでしょう。VBAの最大の弱点と言ってもいいワークシートへのデータ書き出しの遅さは、もはやほとんど気にする必要がなくなったわけです。
これは、一般的にVBAの弱点の一つとされているものなのですが、実は非常に高速にワークシートにデータを書き出す方法が存在します。
それは、VBAにおいて求めた2次元配列を、ワークシートの特定範囲に対応させる、という方法です。
VBAをきちんと勉強された方なら常識なのかもしれませんが、我流でVBAを勉強してきた私は、最近までこのような方法を知りませんでした。
通常、ワークシートの各セルにVBAで演算した結果等を書き出す場合は、ForNextステートメントで2重にループ処理を行うことが多いかと思います。
実際に行なったことのある方ならお分かりだと思いますが、この方法でシートにデータを書き出すと、異常に時間が掛かってしまいます。
例えば以下のようなマクロを実行してみます。
Sub test01()
Dim i As Integer, j As Integer, k As Integer
For k = 1 To 20
Range(Cells(1, 1), Cells(1000, 100)).ClearContents
For i = 1 To 1000
For j = 1 To 100
Cells(i, j) = i + j
Next j
Next i
Next k
End Sub
これは、ワークシートの1行1列から1000行100列まで、行+列の計算結果を記入する動作を20回繰り返すというものです。これを実行しますと、Core2Quad＠2.66GHｚ、RAM3GB、Windows VISTA、Excel2003の環境で、103.6秒ほど掛かりました。
このような簡単なデータの入力でさえ、これほどの時間が掛かるのですから、複雑なデータを入力する場合は、いったいどれくらいの時間が掛かるのか、想像すらできません。
では、これを冒頭に掲げた方法で実行してみましょう。そのマクロは次のようになります。
Sub test02()
Dim i As Integer, j As Integer, k As Integer
Dim cellData(999, 99) As Integer
For k = 1 To 20
Range(Cells(1, 1), Cells(1000, 100)).ClearContents
For i = 1 To 1000
For j = 1 To 100
cellData(i - 1, j - 1) = i + j
Next j
Next i
Range(Cells(1, 1), Cells(1000, 100)).Value = cellData()
Next k
End Sub
これも、test01と同様の計算結果をワークシートに記入するマクロですが、これを実行すると処理時間はわずか3.6秒ほどにしかなりません。
何と、test01の3.5%ほどの時間で終了いたします。30倍近い速度アップです。
これは、行と列に対するForNextステートメントの中で、計算結果を逐次セルに書き出していくのではなく、それを一旦cellData配列に書き出して、最後に一気にワークシートの全対象範囲に書き出しています。
配列の(0,0)を起点として、それをCells(1,1)に対応させているわけです。ここで注意すべき点は、配列は必ず2次元配列の必要があるということです。
あとは、その配列をワークシートの対象範囲に重ね合わせるわけです。
なお、今回の例では全てのデータが整数型で扱えましたので、それを宣言していますが、一般的に種々のデータ型が混在する場合は、Variant型で宣言する必要があります。
あと、配列に取り込めるのはデータのみですので、書式等は別に設定する必要があります。
それらにさえ気をつければ、これは非常に有用な方法となることでしょう。VBAの最大の弱点と言ってもいいワークシートへのデータ書き出しの遅さは、もはやほとんど気にする必要がなくなったわけです。
VBA勉強中の者です。
このネタ、今までのコードをすべて書き直したくなるくらい衝撃的な内容ですよね。
by brAin (2009-05-30 23:56)
brAinさん、こんばんは。
nice!をいただきまして、ありがとうございます。
エクセルには、一般の書籍などでほとんど紹介されていない有用な手法がまだまだたくさんあります。
そのような手法を発掘していくのもまた、楽しいことだと思います。
by Ｋフロー (2009-05-31 18:31)
初めまして。
zebratchと申します。
>Range(Cells(1, 1), Cells(1000, 100)).Value = cellData()
VBAはたまに使いますが、こんな構文が使えるんですね。
でも前者の方法で時間がかかるのは、常に画面更新をしているからかもしれませんね。
画面更新を制御して高速化できるかどうか、機会があったら試してみようと思います。
【ご参考】
（画面更新を制御する(ScreenUpdatingプロパティ)）
http://www.moug.net/tech/exvba/0010005.htm
by zebratch (2009-05-31 20:54)
zebratchさん、はじめまして。
nice!をいただきまして、ありがとうございます。
また、参考情報をご紹介いただき、ありがとうございます。
ScreenUpdatingをFalseに設定して画面の更新を停止することはVBAの高速化技術の一つですが、その方法ではせいぜい数%程度の高速化しか図れないと思います。
画面の更新を停止しても、その裏ではVBAはワークシートにデータを書き出しています。ただ、その過程が目隠しされて、画面では見えないだけです。
実は、画面に表示された結果を更新することが遅いのではなく、その前段階の、シートに結果を書き出すこと自体が遅いのです。
そのため、200万回の書き出しが必要な前者の方法と、20回の書き出しで済む後者の方法に、決定的な差が生じるわけです。
ちなみに、エクセルではセルやシートの複写には後者の方法を用いているようですね。VBAにおいて、それを「複写」という命令を直接的に用いずに表現した結果が、後者の方法ということになるかと思います。
もしも、通常のワークシートで、広範囲の複写の際にセルを1個1個コピーしていたら、大変なことになってしまいます。VBAでも、まったく同じことが言えるわけです。
by Ｋフロー (2009-06-01 09:31)