古い話で大変恐縮なのですが、昨年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の最大の弱点と言ってもいいワークシートへのデータ書き出しの遅さは、もはやほとんど気にする必要がなくなったわけです。