〔Excel:VBA〕マクロの実行が異常に遅くなる

質問

下記のプロシージャを実行すると実行速度が異常に遅くなることがあります。色々原因を調べたところ、一度印刷を実行したシートで再度下記のプロシージャを実行するとこの現象が再現できることがわかりました。（一度ブックを閉じて、再度立ち上げなおせば実行速度も元のスピードに回復しますが、また印刷をかけると遅くなります）
再現性があるのでどなたか原因を教えていただけないでしょうか？
-------------------------------------------------
Sub 非表示_0()

Application.ScreenUpdating = False
Dim i As Integer

For i = 1 To 100
　　If Cells(i, 1).Value = 0 Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next i

End Sub
-------------------------------------------------

回答No.7

Wendy02

こんにちは。Wendy02です。

#6のKenKen_SPさんの回答の補足に対するものですが、

>xlbの拡張子はツールバーかなにかの情報をもったものだったと思うのですが、これって消しても大丈夫でしょうか？

これを消すと、メニューがデフォルトに戻ります。通常、3点セットで、Personal.xls, *.xlb, *.pip を削除すると、完全なデフォルトに戻ります。なお、*.pip は、メニューの操作の記憶ですが、これを削除すると、修復とか言ってきたと思います。Personal.xls は、言うまでもなく、個人用マクロブックですから、削除したら、その中身は失ってしまいます。

さて、私の方のExcelですが、

#2のテストで、何をしなくても、何度やってみても、
3　秒を越えますね。
その理由は、システムから情報を取得する、いくつかの複雑なユーザー定義関数を通ってきているからです。

私の知っている範疇で、変則的な方法ですが、実用度はあると思います。
いろいろ実験したわけではないのですが、昔、ある有名な方が作ったもののコードのイメージが、こんな風だったかなと思って作ってみました。（時間はかなり短縮されます。)
本来は、もう少し手直しする必要があるのですが、今は、ここでとどめました。

'Declare Function timeGetTime Lib "winmm.dll" () As Long '既に標準モジュールにある場合は不要

Sub HiddenRowsPrc()
　Dim rng As Range
　Dim ar As Range

　'-----------------------------------
　Dim lngTime As Long 'KenKen_SP さん '#2時間測定用
　lngTime = timeGetTime()
　'-----------------------------------
　'領域設定
　Set rng = Range("A1:A100")
　Application.Calculation = xlManual
　Application.ScreenUpdating = False

　If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.Range.AutoFilter
　rng.AutoFilter Field:=1, Criteria1:=""
　rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Select
　rng.AutoFilter

　For Each ar In Selection
　　ar.EntireRow.Hidden = True
　Next ar

　Application.Goto rng.Cells(1), False
　Set rng = Nothing
　Application.Calculation = xlAutomatic
　Application.ScreenUpdating = True
　'------------------------------------------
　lngTime = timeGetTime() - lngTime '時間測定用
　MsgBox Format$(lngTime / 1000, "0.000 sec")

　'次のテスト用に再表示
　Call Restore
'------------------------------------------
End Sub

お礼

こんばんは。xlbの件ありがとうございます。やっぱりそうでしたよね。
Wendy02さんのコードで早速実験してみました。
印刷前→平均0.057sec
印刷後→平均4.705sec
でした。
もうこの辺になると私には全てのコードの意味が理解できていないのでよくわかりませんが、若干処理速度が遅くなったような気がします。いずれにしても印刷の前後の差は歴然でした（泣・・・）
でも色々勉強になります。奥が深いっ！！！

この議題も長くなってきたのでもうそろそろ一旦締め切りますね。皆さんありがとうございました。
すぐには解決できそうにもない内容なのですが、色々経験を重ねるといつかわかってくると信じて頑張ります！

回答No.6

KenKen_SP

余りにも回答になってないですね～(;´・ω・`)スミマセン。

消極的ですけど、次のコードならどうなりますか？

時間計測を入れてますので、#2 のコードの末尾にでも付け足して下さい。

やや複雑なコードですが、Hidden の実行を１回だけにしています。ごく
短い処理なので、通常は shunshun-dash さんが書いたようなコードの方が
シンプルですし、より良いのですが、テストということで。

Sub TestCode()

　　Dim rngTarget As Range
　　Dim rngHidden As Range
　　Dim rngCell　 As Range
　　Dim lngTime　 As Long

　　lngTime = timeGetTime()

　　Set rngTarget = ActiveSheet.Range("A1:A100") _
　　　　　　　　　　.SpecialCells(xlCellTypeConstants, xlNumbers)
　　If rngTarget Is Nothing Then
　　　　Exit Sub
　　End If
　　For Each rngCell In rngTarget
　　　　If rngCell.Value Then
　　　　　　If rngHidden Is Nothing Then
　　　　　　　　Set rngHidden = rngCell
　　　　　　Else
　　　　　　　　Set rngHidden = Union(rngHidden, rngCell)
　　　　　　End If
　　　　End If
　　Next rngCell
　　If Not rngHidden Is Nothing Then
　　　　rngHidden.EntireRow.Hidden = True
　　End If
　　Set rngTarget = Nothing
　　Set rngHidden = Nothing

　　lngTime = timeGetTime() - lngTime
　　MsgBox Format$(lngTime / 1000, "0.000 sec")

　　'次のテスト用に再表示
　　Call Restore

End Sub

補足

ありがとうございます。
ご指摘のコードで再度実験してみました。今度はワークシート関数をたくさん載せた状態で。
（結果）
印刷前→0.0405sec/10回平均
印刷後→3.3735sce/10回平均
各データにはばらつきはほとんどありませんでした。

私の場合、自宅PCでも職場PCでも再現できたので皆さんも再現できると思ったのですが、不思議です・・・
念のためドライバを再インストールもしてみましたが、改善は見られませんでした。PDFへの吐き出し処理をおこなっても同じ現象が起きるので、やはりプリンタドライバへの吐き出しを行ったこと自体に原因があるということになりますでしょうか？

「Hidden」を使う時だけこの現象が起きるのですが、今後もこれと付き合っていかなければならないかと思うと「Hidden」は使いたくなくなりますね！でも非表示は絶対必要なのですが。
xlbの拡張子はツールバーかなにかの情報をもったものだったと思うのですが、これって消しても大丈夫でしょうか？（大丈夫だから教えてくださったのだと思いますが）

もう、こうなったら徹底的にということで、今職場のPC全てで試しています。今のところ全てのPCで再現性があります。（残念なことに・・・

そういえば、あともう一つの再現性があります。
というのも、印刷を実行したシート（アクティブシート）から実行する「Hidden」マクロだけが遅くなるのです。この現象が起きても、他のシートから実行するには正常速度でパパっと実行してくれます。そのシートでまた印刷をするとそのシートも死んでしまいますが・・・
ここから何か推測はできないでしょうか？

回答No.5

KenKen_SP

> セルにはデータがまったくない状態で...

それだとテストの意味が薄れるので、できるだけ実際のデータをセルに配置した
上でテストした方が良いですよ。

差異は３秒ですか..微妙ですね。テスト回数を多くとれば誤差の範囲に収拾
されてしまいそうな数字ですから、コメントし難いのですが、

　　「何らかの異常があるかもしれない」

という、何とも煮え切らないことぐらいしか言えません。0.3sec が 30.0sec
ぐらいに変わってしまうのであれば、原因も探りやすいのですが....

これでは、回答になってないですね。すみません。ご参考までに、私が推測し
ていた原因を述べておきます。

　▼原因1） 実測してみればその程の差はない
　　失礼ながら可能性として考慮すべきだと思いましたので、実際に
　　計測をお願いしました。しかし、 #2 補足欄を拝見する限り、
　　差は確かにあるようです。

　　>印刷をかける前の実行速度で体感0.5秒、印刷実行後で体感速度
　　>10秒位です。

　▼原因2） プリンタードライバーの異常
　　コードに問題があるとは考え難いのです。したがって Excel 以外
　　に原因があると考えました。過去にプリンタードライバーの異常が
　　原因となって、印刷時・印刷プレビュー時に Excel がフリーズ、
　　あるいは可笑しな挙動を示した事例がありました。

　　推測でしかありませんが、Hidden を実行するたびに PageSetup の
　　印刷情報が書き換わる？ので、その際に問い合わせるドライバーに
　　異常がある、例えばメモリリークなどが発生しているのだとすれば、
　　実行回数に比例して処理時間が長くなる...と考えたのです。しかし、

　　>職場のPCスペックはもっとハイパフォーマンスにもかかわらず、同様
　　>の現象がおきますので、...

　　の#2 の補足を拝見する限り、異なる環境で再現するわけですから、
　　こちらも可能性は薄そうです。

　　参考までに、その時の対処法を記載しておきます。

　　　　1）プリンタードライバーの再インストール
　　　　2）*.xlb ファイルの削除

　　大抵のケースでは以上の手順で直りました。

長くなるので、一旦切ります。

回答No.4

Wendy02

shunshun-dash　様
こんにちは。Wendy02です。

KenKen_SPさんとの#2の続きのお話も少し聞きたいところですが、

今、気になったので、google で、"excel slow" で検索してみました。

ここで、今までの書いていた内容について、ほとんど、出ていますね。

http://www.mvps.org/dmcritchie/excel/slowresp.htm
Slow Response, Memory Problems, and Speeding up Excel
遅い反応、メモリの問題、Excelを速くする

ここに、こんな一文があります。
　MS believes that some printer drivers are at the root of some resource issues:

（MSでは、いくつかのリソースの問題の根底に、いくつかのプリンタ・ドライバにあると考えているようです。)

http://support.microsoft.com/kb/165985
Q165985 -- Office:MS-Officeを使用しているときの 「メモリ不足」というメッセージの情報

It is also possible that the latest version of the printer driver will correct the problem.
（最新のバージョンのプリンタ・ドライバが、この問題を修正する可能性があります。)
特に、HP ドライバが問題になっている、としています。

So you might try changing print drivers.
（そんなわけで、プリンタ・ドライバを替えてもよいかもしれません。)

私は、考えてもみませんでした。もし、それが事実なら、試してみる価値はありますね。

まだ、いろんなメモリ漏れについて書かれています。全部は読んでいませんが、なかなかタメになります。

お礼

返事遅くなってすみません。
なんだか私の域を完全に超えているようですが、なんとなくExcelとプリンタドライバとのやり取りの中に原因があるような気がしますね。
ただ、プリンタドライバをかえても見たのですが、残念ながら改善は見られませんでした。PDFへ吐き出し処理でも同じことでしたので、やはりプリンタドライバへの出力時に何かが起きているのでしょうね！

回答No.3

Wendy02

こんにちは。Wendy02です。

私の場合は、1万行以上の並べ替えを頻繁に行っています。朝のスタート時などには、私のExcel 2003でも、並べ替えが目で追えるレベルで遅くなることがあります。

私は、ハード系がぜんぜん分からないのですが、キャッシュが、何かに別のものに占有されてるとき（つまり、何かを使った直後)は、異様に遅いようですね。自分のコードをもう一度見直してみないと分からないのですが、通常は問題ないので、間違いはないと思います。

その直し方というのは良く分からないのですが、Excel側で、何度か使用していると戻ります。これは、Excel2000では経験がありませんでした。ただし、私は、Excelではほとんど印刷はしませんので、印刷系でのトラブルは分かりません。

その直し方というのは、#1 で書いたように、やはり何かのツールが必要なのか、とは思いますが。

>印刷を実行するとリソースが極端に不足する
印刷自体ではないないと思います。印刷のバッファとの問題とか？

Excelのメモリそれぞれに割り振りがありますから、そこで、入れ出しになってメモリの割り振りが減ってしまっているとか、勝手な想像ですが、なかなかOffice だけでは解決できそうにもありませんね。

お礼

こんにちは。

>並べ替えが目で追えるレベルで遅くなることがあります。

その「目で追えるレベル」というのは私の体感しているレベルと同じだと思います。
試しに並べ替えのコードでも実験してみたのですが、残念ながら再現はできませんでしたが、原因は同じところにあるような気がします。
リソースとかキャッシュのことは全くわかりませんが、一度ブックを閉じて再度立ち上げなおすと改善されているので、リソースが開放された結果改善されるのだと勝手に想像しています。

仕事上、VBAのスキルが上達すると他の人が半日かけてする作業も30分程度でできるので非常に便利ですが、その反面、段々と難しいシステム系の問題に遭遇してくるようになってきて、こちらのほうで時間を食われています・・・

回答No.2

KenKen_SP

こんばんは、shunshun-dash さん、Wendy02 さん。

KenKen_SP です。

確かに Hidden プロパティーの処理速度は決して早くはありません。しかし、
オリジナルのコードは非常にシンプルで、A1:A100 とループ回数も少なくいの
ですから、それ程時間がかかる処理とは思えません。

プログラムの実行速度について早い・遅いという点で言えば、体感速度の感じ
方には個人差があって、それが影響している可能性も否定できません。

「異常に遅い..」と言うのは、具体的にはどれぐらいの時間がかかっているの
でしょうか？ これを検討するには、少なくとも

　・PC スペック　（CPU速度・メモリ搭載量・OS と Excel のバージョン）
　・処理するデータサンプル　（数件で構いません）
　・実行するコード　（これは下記コードでお願いします
　・ベンチマークテスト結果　（10回程度）

などの材料が提示される必要があります。

私がご提示のオリジナルコードを試したら、A1:A100 とサンプル数が少ないせい
もあって、10回平均で 0.125 sec でした。計測に使っている API は 15 ミリ秒
程度の誤差がありますので、信頼できるのは 1/100 秒までです。従って、 0.12
秒程度ということになります。なお、テスト環境は

　・P4-2.4GHz 512M　/　WindowsXP + Excel2002

です。もちろん、実際のものとは異なるデータ・環境でのテストですから、参考値
にもなりませんが...

実際に計って見て下さい。簡易ですが、ベンチマークは次の手順で取れます。

1.　Windows の起動直後で、常駐ソフトは可能な限り終了させる
2.　起動するのは Excel のみ
3.　セルに実際のデータと同様のサンプルデータを配置する
4.　下記のコードを標準モジュールにコピペする
5.　10回程度繰り返し、ベンチマークを取って下さい。

Option Explicit

' 処理時間計測用の API
Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub 非表示_0()

　　Dim i　　　 As Integer
　　Dim lngTime As Long

　　lngTime = timeGetTime()

　　Application.ScreenUpdating = False
　　For i = 1 To 100
　　　　If Cells(i, 1).Value = 0 Then
　　　　　　Cells(i, 1).EntireRow.Hidden = True
　　　　End If
　　Next i

　　lngTime = timeGetTime() - lngTime
　　MsgBox Format$(lngTime / 1000, "0.000 sec")

　　'次のテスト用に再表示
　　Call Restore

End Sub

Sub Restore()

　　'次のテスト用に再表示します
　　Cells.EntireRow.Hidden = False

End Sub

お礼

Wendy02さん、KenKen_SPさん、こんにちは。
ベンチマークをとってみました。
Excelを新規に立ち上げて、セルにはデータがまったくない状態で、KenKen_SPさんご指摘のコードをコピペして測定しています。
（今は職場のPCで測定しています）
◎Windows XP
◎Celeron D 2.80GHz
◎256MB+512MB（拡張）
◎Excel2003
（ベンチマーク結果）
「印刷前」→0.03sec/10回平均
「印刷後」→3.805sec/10回平均

印刷実行後は100倍以上のようです。
実際に問題となっていますExcelブックにはかなり多くのExcel関数が存在しているため作業に支障をきたすくらいに遅くなってしまうのだと思います。
皆さんは、私の問題状況を再現できていますでしょうか？それとも私だけなのでしょうか？？？

補足

こんばんは。夜遅くにありがとうございます。
PCのスペック等簡単に記載しておきますのでアドバイスいただけるとありがたいです。
◎Windows XP
◎Celeron 2.70GHz
◎256MB+512MB(メモリ拡張)
◎Excel2003

本当に簡単に書きました。「実行速度」の体感については確かに個人差がありますが、私が最初に記載したプロシージャについて、印刷をかける前の実行速度で体感0.5秒、印刷実行後で体感速度10秒位です。
上記のPCスペックは自宅のものですが、職場のPCスペックはもっとハイパフォーマンスにもかかわらず、同様の現象がおきますので、その辺のところは関係ないのかなと思っております。
いずれにしても、他の方のPCでも再現性があるのかどうかというところからでも教えていただけると助かります。とにかく、私の突き止めた原因は「印刷前」と「印刷後」でマクロの事項速度が少なくとも10倍以上になるということで、微妙な実行速度の変化ではありません。

夜遅いので、ベンチマークは明日行ってみます。よろしくお願い致します。

回答No.1

Wendy02

こんばんは。Wendy02です。

以前書いたように、
#ひとつのセルを選択するたびに「計算しなさい(Calculate)」という指令が、Book全体に出ています。

ということに対処するなら、以下のようになります。まだ、他にも方法はありますが、これはループで行う方法です。

Sub 非表示_0()
　Dim i As Integer
　Application.Calculation = xlManual
　Application.ScreenUpdating = False
　For i = 1 To 100
　　If Cells(i, 1).Value = 0 Then
　　　Cells(i, 1).EntireRow.Hidden = True
　　End If
　Next i
　Application.ScreenUpdating = True
　Application.Calculation = xlAutomatic
End Sub

ScreenUpdating は、必ず、False/True ではさんでください。

また、物理的な問題がある場合、例えば、フォントやアンチウィルスソフトなどの問題は、VBAでは対処できません。一旦、リソースをフリーにするなどしてみるのも手かもしれません。

補足

こんばんは。
以前のご指摘のとおり、ブックへの計算を手動に切り替えるなど色々やってみましたが、この「表示/非表示」に関するメソッドだけが、異常に遅くなる現象が起きているように思います（現在私が記述している他のマクロについては一切このような重たくなる現象は起きないのです・・・）。
ちなみに、先ほどのご指摘のとおりのコードで実行しても、やはり改善は見られませんでした。セルに計算式が一切入っていない軽いファイルで再現しておりますので再計算の実行によるものではないと思うのです。
前回私もリソースの問題かなとは思ったのですが、印刷を実行するとリソースが極端に不足するというも変なはなしだなぁと思いました。

Wendy02さんの環境ではこのような現象は再現されませんか？私は、自宅のPCでも職場のPCでも再現性がありますので、結構困っています。