マクロの処理速度が遅くなってしまいました

質問

今までエクセル２０００を使用していたのですが、エクセル２００３にバージョンアップして、以下の処理速度を検証したところ、処理が遅くなってしまいました。内容はsheet2にあるデータを変数に格納して簡単な計算をした後にsheet1に入力するということを5回繰り返し、それぞれの処理にかかる時間をsheet3に表示するというものです。

Sub タイム計測()
　　Dim myStart As Single, myGoal As Single
　　Dim j As Integer

　　Application.ScreenUpdating = False
　　Sheets(1).Select
　　Cells.Clear
　　　　For j = 1 To 5
　　　　　　myStart = Timer
　　　　　　　　Call サンプル
　　　　　　myGoal = Timer - myStart
　　　　　　Sheets(3).Select
　　　　　　Cells(j, 1) = myGoal
　　　　　　Sheets(1).Select
　　　　Next
　　　　Sheets(3).Select
　　Application.ScreenUpdating = True
End Sub

Sub サンプル()
　　Dim i As Integer, j As Integer
　　Dim Data As Variant, KeKKa(1 To 2000, 1 To 199) As Variant
　　Data = Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(2000, 199))
　　For i = 1 To 2000
　　　　For j = 1 To 199
　　　　　　KeKKa(i, j) = Data(i, j) + Data(i, j)
　　　　Next
　　Next
　　Range(Cells(1, 1), Cells(2000, 199)) = KeKKa
End Sub

エクセル２０００のときは1回当り平均して概ね0.7秒位で処理していたのですが、エクセル２００３にすると1.4秒位かかってしまいます。処理速度が遅くなってしまうとバージョンアップした意味がないのですが、原因や改善策があればどなたか教えていただけないでしょうか？よろしくお願いします。

回答No.1

Gocho_ · ベストアンサー

実行環境がExcelのバージョン以外は
同一という条件のもとに検証しました。

余計なSelectを消したり、
＞KeKKa(i, j) = Data(i, j) + Data(i, j)
を
KeKKa(i, j) = Data(i, j) * 2
に変更して、データの参照回数を減らしたり、
などして、ゼロコンマ数秒の改善はできました。

そもそも、どの処理にどれだけの時間がかかっているのかを
調べてみると
一回あたり 0.7秒弱に対して
Sub サンプルの最後の
Range(Cells(1, 1), Cells(2000, 199)) = KeKKa
で0.5秒強を要していました。

つまり、大量のメモリ空間のデータをエクセルのデータに転記
するのに時間がかかっているようです。
ためしに、質問者さんの環境で、
Range(Cells(1, 1), Cells(2000, 199)) = KeKKa
をコメントアウトして実行してみてください。
それで、劇的に早くなるのであれば、
メモリ不足や、メモリ領域に連続した空き領域がないため、
時間がかかっている可能性があります。
2000より2003の方が起動した状態でのメモリの使用量は
多いはずですので（たぶん）

Sub タイム計測()
Dim myStart As Single
Dim j As Integer

Application.ScreenUpdating = False
For j = 1 To 5
myStart = Timer
Call サンプル
Sheet3.Cells(j, 1) = Timer - myStart
Next

Sheet3.Select
Application.ScreenUpdating = True
End Sub

Sub サンプル()
Dim i As Integer, j As Integer
Dim Data As Variant, KeKKa(1 To 2000, 1 To 199) As Integer

Data = Range(Sheet2.Cells(1, 1), Sheet2.Cells(2000, 199))
For i = 1 To 2000
For j = 1 To 199
KeKKa(i, j) = Data(i, j) * 2
Next
Next
Range(Sheet2.Cells(1, 1), Sheet2.Cells(2000, 199)) = KeKKa
End Sub

お礼

確かに
　　Range(Sheet2.Cells(1, 1), Sheet2.Cells(2000, 199)) = KeKKa
の部分をコメントアウトすると約1秒ほど短縮されました。
おっしゃる通りメモリ不足が原因と思われます。
　機能面を考慮するとエクセル2003を使いたいのですが、単純な計算だけならエクセル2000の方が効率がいいのかもしれませんね。微妙なところです。
　どうもありがとうございました！