EXCELマクロの処理速度

質問

いつもお世話になっております。

EXCELのマクロで、シートAのデータから条件に合致する値を検索してシートBにデータを自動で入力させています。
シートA、Bは同じブック内のシートです。

シートAはVisible = FALSEで隠したままで、シートBを隠した状態と表示した状態でマクロの処理速度に違いがある（具体的な時間は計測していませんが体感できるほどの差がある）のですがこのような処理速度の違いは起こるものなのでしょうか。

私が作成したマクロではシートBが表示されている方が処理速度が速いです。

もし、シートBを表示した方が速くなることが理論的に説明できるのなら、表示させて処理速度を少しでも速くしたいと思っています。

よろしくお願いします。

回答No.3

hana-hana3 · ベストアンサー

＞今回のコードだけでこういう現象が起こるというなら問題があります。

おそらく処理内容の問題かと思います。
実際のデータが無いので何とも言えませんが、提示されたコードには無用な部分も見られる感じですから、もう少し整理が可能かと思います。

下記のコードでは、シートの表示・非表示では最大で0.02秒の差しかありませんでした。
test1とtest2の実行時間の差は約１秒。

---------------------------------------------------------------
シートＡのＡ列の１００００行までに適当な数字が入っています。
シートＢのＡ列には、シートＡのＡ列の値（隅奇）に応じて、０か１を入れます。

Sub test1()

Dim tmp As Integer, i As Integer
Dim work As Variant

work = Timer
For i = 1 To 10000
tmp = Worksheets("シートA").Cells(i, 1).Value
If tmp Mod 2 = 1 Then
Worksheets("シートB").Cells(i, 1).Value = 1
Else
Worksheets("シートB").Cells(i, 1).Value = 0
End If
Next
Debug.Print Timer - work

End Sub

Sub test2()

Dim tmp As Integer, i As Integer
Dim work As Variant

work = Timer
For i = 1 To 10000
tmp = Worksheets("シートA").Range("A" & i).Value
If tmp Mod 2 = 1 Then
Worksheets("シートB").Range("A" & i).Value = 1
Else
Worksheets("シートB").Range("A" & i).Value = 0
End If
Next
Debug.Print Timer - work

End Sub

＞シートの表示、非表示による処理時間の差が納得がいかないです。

テストしたコードだけでは明確な理由を見つけ出す事は出来なかったので何とも言えませんが、余分なシートや表示を排除してシート間計算（集計？）だけにして試してみる必用があるかと思います。

＞処理後のデータを別シートでグラフにしているため、生データを見る必要がないからです。

「ScreenUpdating」では描画を抑止するので、グラフの書き換えも止まるためかも知れませんね。
マクロ実行中でもイベントが発生して居ます。
セルの値が書き変わるとエクセルは自動的に再計算を行いますから、グラフも再描画される事になります。

Application.EnableEvents = False
（実行終了時は必ず True に戻す事）
で、イベントをキャンセルするとさらに高速化するかも知れません。

お礼

回答、ありがとうございます。
お礼が遅くなり申し訳ありませんでした。

提示頂いたコードを参考に変更を加えたところ、シートの表示、非表示にかかわらず、処理の高速化に成功しました。
for nextのところを変数iのカウントアップから変数RowNumLongNowのカウントダウンに変更しました。
お恥ずかしい話なのですが、for nextでカウントダウンができるということを知らなくて、以前のようにわざわざ２変数を用いてカウントダウンを実現していました。

旧コードでのシート表示、非表示による処理時間の差の件はたぶん、イベント発生によるものだと思われます。
hana-hana3様が仰るようにApplication.EnableEvents = Falseを設定すると処理速度の差がなくなりました。
シートA、B以外のシートが多すぎるので、どのシートが悪さをしているかを見極めるのは難しいので、おまじない的にApplication.EnableEvents = Falseを設定するようにします。

ようやくすっきりしました。
ありがとうございました。

回答No.2

hana-hana3

実際のコードの提示が無いので何とも言えませんが・・・。

最速はシートに関数を記入して行う方法です。
その次は、ＶＢＡでワークシート関数を使う方法になります。

VBA高速化テクニック(目次)
http://officetanaka.net/excel/vba/speed/index.htm

補足

回答ありがとうございます。
「VBA高速化テクニック」非常に参考になりました。

さて、実際のコードは以下の通りです。

Sub DataA_to_DataB()

myDataSheetNameA = "シートA"
myDataSheetNameB = "シートB"
RowNumANow = 1126
RowNumB = 1126

For i = 1 To 1090
RowNumAPre = RowNumANow - 1
CellNameDataANow = "AO" & RowNumANow
CellNameDataAPre = "AO" & RowNumAPre

DataAnow = Worksheets(myDataSheetNameA).Range(CellNameDataANow).Value
DataApre = Worksheets(myDataSheetNameA).Range(CellNameDataAPre).Value

CellNameDateANow = "B" & RowNumANow
CellNameDateB = "B" & RowNumB
CellNameTime = "G" & RowNumB

myDateANow = Worksheets(myDataSheetNameA).Range(CellNameDateANow).Value
myDateB = Worksheets(myDataSheetNameB).Range(CellNameDateB).Value
myTime = Worksheets(myDataSheetNameB).Range(CellNameTime).Value
myTime = Hour(myTime)

Do Until myDateB <= myDateANow And myTime < 8
CellNameBtoA = "CC" & RowNumB
If DataAnow <= DataApre Then
Worksheets(myDataSheetNameB).Range(CellNameBtoA).Value = 0
Else
Worksheets(myDataSheetNameB).Range(CellNameBtoA).Value = 1
End If
RowNumB = RowNumB - 1
CellNameDateB = "B" & RowNumB
CellNameTime = "G" & RowNumB
myDateB = Worksheets(myDataSheetNameB).Range(CellNameDateB).Value
myTime = Worksheets(myDataSheetNameB).Range(CellNameTime).Value
myTime = Hour(myTime)
If RowNumB < 36 Then
Exit For
End If
Loop
RowNumANow = RowNumANow - 1
Next

End Sub

普段、シートA、BはプロパティでVisible = FALSEにしています。処理後のデータを別シートでグラフにしているため、生データを見る必要がないからです。

このコードをシートA、Bを表示した場合（Visible = TRUE）と、非表示の場合で処理時間を計測したところ、表示した場合の方が約１０分の１の時間で処理が終わりました。
非表示の場合でも、Application.ScreenUpdating = FALSEをつけると同じく１０分の１の時間で処理が終了します。

処理時間だけを問題にするならApplication.ScreenUpdating = FALSEをつけて問題解決となるのですが、シートの表示、非表示による処理時間の差が納得がいかないです。

さらなる高速化を目指すなら、Application.ScreenUpdating = FALSEに、さらにシートを表示させればいいと言うことになりますが、今回のコードだけでこういう現象が起こるというなら問題があります。

よろしくお願いします。

回答No.1

extrabold

逆なら経験がありますが、表示させたほうが速いというのはあまり聞きません。
画面更新系は遅い部類の処理になるので、基本的には非表示で実行させるのが高速化と言われているものです。
ただ、非表示にするのを、プロパティのVisibleをFalseにする、という方法は取ったことがありません。
マクロ実行中は、ScreenUpdating=Falseを設定し、実行終了時にTrueに戻す方法が普通だと思われます。
回答としてはずれてしまいますが、データの取得・書き出しも複数方法があり、速度も違いますので余裕があれば調べられてはいかがでしょうか。

補足

回答ありがとうございます。

ScreenUpdating=Falseによって処理時間が短縮されることを確認できました。
しかし、Visible=FALSE、TRUEによる処理時間の差に関しては疑問が解決しておりません。

ANo.2のhana-hana3様への補足に実際のコードを示させてもらいました。

何かお気づきの点がありましたら、よろしくお願いします。