Excel（エクセル）活用コミュの【質問】マクロの実行が遅い

# 下記トピックの#244 から派生させたトピックです。
http://mixi.jp/view_bbs.pl?id=66880273&comm_id=2106

ちょっとしたデータ集計のマクロをexcelで自作しました。集計にかかる時間が、

・Core2 Duo + メモリ4GB + WinVista + Excel 2007 → 14秒
・Core i5 + メモリ8GB + Win8.1 + Excel 2013 → 306秒

ってどういうことなんでしょう？なんとかVista並のスピードが出せるようにならないものでしょうか？

ちなみに、以下の対策は執りましたが効果はありませんでした。
・Microsoft IMEを削除してGoogle日本語入力をインストールした (←Win7+Excel2010ではこれが有効だった)
・Excel2013のオプションで「ハードウェアグラフィックアクセラレータ」を無効にした
・以下のレジストリを追加した
　key: HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Graphics
　name: DisableAnimations
　type: DWORD
　value: 1

コメント(17)

最初
全て
最新の40件

[1] mixiユーザー 11月26日 12:08

「ソースを見ないと」との事だったので、キモの部分を抜粋したものを公開します。

#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Option Explicit

Public tmFrom As Date
Public tmTill As Date

Sub Macro1()
Application.ScreenUpdating = False
Dim ii As Integer
tmFrom = Time
For ii = 1 To 100
Windows("Book2.xls").Activate
Range("A" & ii).Select
Selection.Copy
Windows("Book1.xlsb").Activate
Range("A" & ii).Select
ActiveSheet.Paste
Next ii
tmTill = Time
MsgBox ("整形完了 (" & DateDiff("s", tmFrom, tmTill) & "秒)")
End Sub

Book1.xlsb と Book2.xlsがあって、Book2のA1からA100までを
Book1のA1～A100に1セルずつコピペさせてます。
# Book1のファイル形式は.xls, .xlsm, .xlsbと試してみましたが
# 特に実行時間に有意差は見られませんでした。

実行結果は、私の環境では以下の通りでした。
・Core2 Duo + メモリ4GB + WinVista + Excel 2007 → 0秒 (1秒未満)
・Core i5 + メモリ8GB + Win8.1 + Excel 2013 → 22秒

[2] mixiユーザー 11月26日 16:07

windowをactivateしたりrangeをselectしたりせずに、いきなり代入するのではだめですか？

コピー元のシートに、目に見えないオブジェクトがあって、それらと共にコピペされたりしませんか。

いずれのブックも、ブックの共有などはしてませんか。

このコードを見る限りですが、tmfromとtmtillはpublic変数である必要はなさそうです。

screenupdatingをfalseにせずに描画しながら実行したら、どこがボトルネックかわかるような気もします。

冒頭の関数宣言？も、コード中では使われてないようなので、これをなくしたらどうなるか、確認の余地はあるような気も。

私はいま確認する環境が手元にない状態ですが、とりあえず思いあたったのは以上です。

[3] mixiユーザー 11月26日 17:02

>>[1]

見るからに時間の掛かりそうなコードですね。

ほぼ enoshinさんと同じ意見ですが、
対象範囲を配列に取り込んで貼り付ければ劇的に早くなりますよ。

[6] mixiユーザー 11月26日 20:43

>>[2]
コメントありがとうございます。

> windowをactivateしたりrangeをselectしたりせずに、いきなり代入するのではだめですか？
そこにかかるコストがExcel2007と2013で数十倍の差がある、ということですか？

> コピー元のシートに、目に見えないオブジェクトがあって、それらと共にコピペされたりしませんか。
それはありません。

> いずれのブックも、ブックの共有などはしてませんか。
それもありません。

> このコードを見る限りですが、tmfromとtmtillはpublic変数である必要はなさそうです。
そうですね、そこはオリジナルのコードからのコピペだったので誤解を招くソースになってしまいました。

> screenupdatingをfalseにせずに描画しながら実行したら、どこがボトルネックかわかるような気もします。
処理のどこか特定の個所で遅くなっているという感じではありませんでした。
全体的に遅いように見えます。

> 冒頭の関数宣言？も、コード中では使われてないようなので、これをなくしたらどうなるか、確認の余地はあるような気も。
あぁ、すみません。これもオリジナルのコードでは使っていたので。

> 私はいま確認する環境が手元にない状態ですが、とりあえず思いあたったのは以上です。
ありがとうございます。

[7] mixiユーザー 11月26日 20:47

>>[3]

> 問題の定義は同じコードで、OSやエクセル、VBAのバージョンなどの環境によって
> 遅延が起きているということですよね。
> スペックの高いほうで遅延が起きている。
まさにその通りです。

> 上から4行目PtrSafe抜けてないですか？
それがExcel2007(32bit)でも2013(64bit)でも使えるようにするおまじないなので。
http://msdn.microsoft.com/en-us/library/office/gg278832.aspx

> ちなみにwin7-pro/64bit/core2quad/8G/Excel2013-64bitの環境で上記コードは1秒でした。
お？となると問題はExcelのバージョンではなくOSってことなのでしょうか。

[8] mixiユーザー 11月26日 21:26

問題は同じコードで、OSやエクセル、VBAのバージョンなどの環境によって遅延が起きているということですよね。

抜粋したコードだけだと何秒ですか？

win7-pro/64bit/core2quad/8G/Excel2013-64bitの環境で上記コードは5秒でした。
が、Book1.xlsb と Book2.xlsを画面を最小化して実行したら1秒という結果になりました。

ハードウェアグラフィックアクセラレータの有無では差は生じませんでした。

念のために途中に何箇所か処理測定を設けて確認してみてはどうでしょうか。

[10] mixiユーザー 11月26日 22:59

>>[8]

あれ？さっきの引用がおかしかったようです。済みません。

> 抜粋したコードだけだと何秒ですか？
抜粋したコードだけで、[1]に書いた「0秒と22秒」です。

[11] mixiユーザー 11月26日 23:01

>>[3]
> 対象範囲を配列に取り込んで貼り付ければ劇的に早くなりますよ。
対象範囲が連続していればその手も使えるでしょうが、かなりバラバラに散らばっているところから
かきあつめてこないといけないんです。でも入力データのフォーマットは変えられません。

[12] mixiユーザー 11月27日 00:17

>>[10]

22秒でしたか、すいません見落としてました。
他にWin8使ってる方に確認できるといいですね。

実行時にタスクマネージャーのプロセスの数値を念のために消去法という意味で、確認しておきたいです。

それと、下記の文面を目にしましたが、何か関係ありそうですか？

お使いのウイルス対策ソフトウェアに Excel との統合機能が含まれている場合、パフォーマンスの問題が発生することがあります。

[13] mixiユーザー 11月27日 10:51

>>[11]

> 対象範囲が連続していればその手も使えるでしょうが、かなりバラバラに散らばっているところから
かきあつめてこないといけないんです。

データをかきあつめてくるのに、BookのActivate や RangeのSelect は必要ないですよね？
対象範囲がバラバラでも配列に取り込めるのは解りますよね？

[14] mixiユーザー 11月29日 19:41

マイクロコミュニティに、Win8 VBA処理が遅いで質問してみましたら、
以下の内容を確認しては、というメッセージを頂きましたのでご連絡します。

■ Office をセーフモードで起動した状態ではどうか
※ セーフモードで開くとマクロが一時的に無効になるので、有効にしてからマクロの動作を確認してみてください。
※ アドインの動作に原因がないかを確かめる方法です。

参考: Office 2013 アプリケーションを起動できない理由
http://office.microsoft.com/ja-jp/support/HA104011864.aspx

■ Windows をクリーンブートで起動した場合はどうか
※ OS 内のアプリケーションの動作を停止させて確認する方法です。

参考: Windows 8、Windows 7、または Windows Vista でクリーンブートを実行して問題のトラブルシューティングを行う方法
http://support.microsoft.com/kb/929135/ja

処理の遅延は、今回の記述だけでしょうか？
それとも、あらたに違うマクロを作成しても、どのマクロでも遅いということでしょうか？
記述によって、1つのファイル内だけのものなら、遅延にはならないとかありますでしょうか？
例えば、今回の22秒かかったマクロをコピー先をコピー元と同じBOOK内での処理時間は？

マクロを実行させる手前で、タスクマネージャーのプロセスのメモリを降順にして、画像をコピー。
マクロを実行させ、プロセスの画像をコピーして比較し、念のため負荷がかかったものを調べてみてはどうでしょう。

[15] mixiユーザー 11月29日 21:26

返信遅れて済みません。あれから出張にでてしまい、
問題の起きているPCに触る時間がなかなか取れず返信できずにいます。

>>[12] 優馬さん

>実行時にタスクマネージャーのプロセスの数値を念のために消去法という意味で、確認しておきたいです。
「プロセスの数値」とは、CPU使用率という解釈でよろしいでしょうか。
PCを起動して、タスクマネージャーとExcel以外のアプリケーションが起動していない状態で、
　Vista + Excel2007 → 開始から終了まで50%前後をウロウロ。
　Win8.1+ Excel2013 → ほぼゼロ。時々80%近くまで跳ね上がる。
といったところです。

> お使いのウイルス対策ソフトウェアに Excel との統合機能が含まれている場合、
> パフォーマンスの問題が発生することがあります。
Win8.1のPCは購入したばかりで、まだウィルス対策ソフトを入れてない状態なので
これは違うかな、と。

>>[13] ミスキタさん
要するに、ミスキタさんが仰りたいのは「編集作業の殆どをメモリ上で行うように
ソースを書き換えれば、スペックの高い環境で動作が遅いなんて事は気にならない
くらい速くなるから、そんなところで悩むだけムダ」ってことでしょうか。

>>[14] 優馬さん
> ■ Office をセーフモードで起動した状態ではどうか

> ■ Windows をクリーンブートで起動した場合はどうか
先の実験は余計なソフトウェアが立ち上がっていない状態で行いました。
問題の起きているPCに触る時間がなかなか取れず、これは実験できていません。
確認出来次第、改めて返信します。

[16] mixiユーザー 11月30日 21:04

＞Vista + Excel2007 → 開始から終了まで50%前後をウロウロ。
＞Win8.1+ Excel2013 → ほぼゼロ。時々80%近くまで跳ね上がる。

私のところは
Win7+Excel2013 → 15～20%で最後に27%になりました。
Win8.1のスペックで、たった、あれだけの記述で、時々80％というのは、
何か変ですね。

念のため、Excelのファイル、オプション、詳細設定、マルチスレッド計算を行う。
にチェックが入っていることを確認した上で。

もう一度、タスクマネージャーを開き、（Win8ではどういう画面かわかりませんが）
プロセスのタブを選択し、プロセスの一覧をみながら、マクロを実行させ「EXCEL.EXE」以外で数値が大きく変動しているものがあるかないか確認してみてください。
もしくは、「EXCEL.EXE」の数値が異様に膨れ上がったかどうか。

次に、エクセルのCPUの優先度を変えて確認します。
プロセスのタブのタスクの中から「EXCEL.EXE」を、マウスの右クリックし、
「優先度の設定」を選び、通常は「通常」になっているので、「通常以上」にして、
マクロを実行させて処理速度が変わるかどうか確認してみてください。
「リアルタイム」の選択はしない方がいいです。

優先度の理解と注意事項　（ネットが書かれていたものを引用）

基本的に優先度はプログラム側で決めるものであって、
変更するのは危険を伴います。
下手すると、まともにキー操作やマウス操作すらできない状態に陥ってしまいます。
CPU資源の独占によって起こるものなのでフリーズとは違いますが、
現象としては似ているかも知れません（
最悪、電源ボタンを長押しするしかなくなる、とか）。

ちなみに、タスクマネージャでは6種類の優先度が選択できますが、
Windowsは32種類の優先度を持っています。
プロセスの優先度とスレッドの相対的な優先度によって、
実際の処理順序が決まります。リアルタイムは非常に特殊な優先度で、
「高」までは32段階のうち15までしか上がる事はありませんが、
逆に「リアルタイム」は15以下に下がる事がありません。32段階のうち、
上半分が「リアルタイム」で占められている事になります。
なので、一つでも「リアルタイム」の優先度プロセスが存在すれば、
そのプロセスが処理を渡してくれない限り、CPU独占状態になってしまいます。

[17] mixiユーザー 12月02日 10:16

>>[15]

> 要するに、ミスキタさんが仰りたいのは「編集作業の殆どをメモリ上で行うように
> ソースを書き換えれば、スペックの高い環境で動作が遅いなんて事は気にならない
> くらい速くなるから、そんなところで悩むだけムダ」ってことでしょうか。

[1]のコードもメモリ上で実行されていると思うのでそういうことを言いたいのではありません。
マクロの自動記録で作成された（ような）コードで遅いとなげくよりも、ちゃんと書けば劇的に早くなるということです。

実際[1]と等価のコードを activate select copy paste なしで書くと150倍ぐらい早くなりましたよ。

ログインすると、残り3件のコメントが見れるよ