こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excelで立ち上げ後しばらくすると動きが鈍くなる〔VBA〕

VBAを使った動作がEXCEL2003を立ち上げて5分か10分くらいすると動きが異常に遅くなります。これってリソースの関係なのでしょうか?
具体的にといいますと、例えばLoop-NextにIF-End IFを組み込んだような構文で遅くなる現象がよくおきている感じがします。具体的な繰り返しも50回位というところです。この例ですと、「Activecellが空欄ならばその行を非表示にする」という作業を「1セル下にずれながら50回繰り返す」というものです。

この作業もExcelを立ち上げた当初には0.5秒くらいでしてくれるのに、しばらくすると同じ作業を5秒から10秒くらいかかってしまいます。

いままでの経験で言うと、セルに値を指示するような記述では遅くなるという現象はなく、Activecellが動いたり、シート間を行き来したり、行や列を非表示にするような記述をすると動きが重たくなる現象が起きているような気がしています。

ちなみにVBAを使わない通常の作業には全く遅いと感じることはありません。

投稿日時 - 2006-09-01 23:35:12

QNo.2377741

困ってます

質問者が選んだベストアンサー

こんにちは。

>ループ内の作業が終わってもマクロが終了していないということがあるのでしょうか?
いいえ、そのマクロには、問題はないのですね。ただ、そのアクティブセルには、荷物が多いはずです。

ひとつのセルを選択するたびに「計算しなさい(Calculate)」という指令が、Book全体に出ています。単純なワークシートでは問題ないのに、実際のマクロでは、ひとつ動くたびに、かなり大変な仕事をしています。(そのブックには、配列数式や揮発性関数が多く使われているような気がします)

どこが問題かというと、
>ActiveCell.Offset(1).Select
この部分で、新たに、オブジェクトを取り直しているからです。Select - Selection がいけないという人がいますが、それは、ループの中でオブジェクトを取り直すという問題があるからです。繰り返さなければよいのですね。

次に、
>ActiveCell.Row = 50
ここでも、新たに、オブジェクトを取っています。
一度、取ればよいのです。

同じループでも、こちらのほうがよいかもしれません。

Sub 空欄を非表示2()
 ' 50行になるまで隠すを繰り返す
Dim i As Long
 Application.ScreenUpdating = False '画面の切り替わりを止める
 For i = 1 To 50
  If Cells(i, 1).Value = "" Then
   Cells(i, 1).EntireRow.Hidden = True
  End If
 Next i
 Application.ScreenUpdating = False
End Sub

このマクロは、該当するシートモジュールに書くか、標準モジュールに書かないと、うまくいきません。本来は、ActiveSheet をつけたほうがよいかもしれません。

Do ~ Loop というのは、離脱条件が、未確定の場合に使います。出てきて決めるっていう感じです。今回は、最終が50と決まっていますから、For ~ Next のほうがよいです。

それと、
Application.ScreenUpdating
をあるなしを試してみてください。かなりスピードが違うはずです。

それと、VBAの「最適化ルール」というのがあります。以下をご参考にしてみてください。これさえ分かれば、もう怖いものなしって思います。ただ、その文章をそのまま、すぐに実現できるものではありません。今の雑誌や書籍のサンプルコードにも、お手本はありませんから、専門掲示板で、レベルの高い人のコードで、なぜ、こう書くのだろうって疑問を持って、他人のコードを読んで、実際に動かしてみるしか、覚えられないような気がします。(これは、VBAのみに適用するものです。VB ではありません。)

VBA コードを最適化する
-
オブジェクトを最適化する
-
(**画面が広がるので、参考URLに書きました)

 「ループ内で配列の 1 つの要素を参照する必要がある場合は、配列内で参照するのではなく、要素の値を保管する一時変数を作成します。配列から値を取得する方法は、同じデータ型の変数を読み取る方法よりも時間がかかります」

その中の「ループ」の要項を再現すると、私なら、以下のようにします。

Sub 空欄を非表示Opt()
 Dim i As Long
 ' 50行になるまで隠すを繰り返す
 ' Application.ScreenUpdating = False
 With Range("A1:A50") '一時変数の代わり
  i = 1
  Do Until i >= 50
   If .Cells(i, 1).Value = "" Then
    .Cells(i, 1).EntireRow.Hidden = True
   End If
   i = i + 1
  Loop
 End With
' Application.ScreenUpdating = False
End Sub

参考URL:http://www.microsoft.com/japan/developer/library/modcore/deconoptimizingvbacode.htm,http://www.microsoft.com/japan/devel

投稿日時 - 2006-09-02 10:20:42

お礼

大変ご丁寧な指導をありがとうございます。

>(そのブックには、配列数式や揮発性関数が多く使われているような気がします)

ご指摘のとおりです。シート数も30以上にも及び、動きが少し重たいですが、自作ソフトになっています。
数えたわけではないですが、おおよそ5000くらいのセルに複雑な数式や関数を埋め込んでいます。

まず、Application.ScreenUpdating = False
というのを覚えます。確かにこれだけでも違いますね。でも、オブジェクトの取り直しが多すぎるから処理が重たいというのはなんとなく自分でもわかっていましたから、最終的にはコードのレベルを徐々に上げていけるように努力します!

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

このことは、よく知りませんでした。勉強になります。それにしてもWendy02さんはすごいですね。

投稿日時 - 2006-09-02 17:13:09

ANo.2

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

補足

オブジェクトを最適化する
のマイクロソフトのリンク先が、途中で切れてしまいましたので、以下においておきます。

参考URL:http://www.microsoft.com/japan/developer/library/VBCon98/vbconoptimizingobjects.htm

投稿日時 - 2006-09-02 10:22:50

ANo.1

こんばんは。

>「Activecellが空欄ならばその行を非表示にする」という作業を「1セル下にずれながら50回繰り返す」

こんな内容にしてみたらどうですか?
A列に対して、空白(ブランク)なら、その行を非表示にする

'範囲が最終セル行まで
Sub HiddenRows()
On Error Resume Next
'A列
With ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks)
  .EntireRow.Hidden = True
End With
On Error GoTo 0
End Sub


'範囲が決まっている場合(A1:A50)まで
Sub HiddenRows2()
 On Error Resume Next
 With ActiveSheet
  .Range("65536").Value = "a" 'ダミー
  With .Range("A1:A50").SpecialCells(xlCellTypeBlanks)
   .EntireRow.Hidden = True
  End With
  .Range("65536").Clear
 End With
 On Error GoTo 0
End Sub



>Loop Next
コードなしで、想像するのは、こちらとしては無理があるけれど、たぶん、ループ内の作業が終わっても、そのマクロ自体が終了していないのではないでしょうか?ループの離脱条件は、何にしているのでしょうか?

投稿日時 - 2006-09-02 00:19:08

補足

ありがとうございます。ご教示頂いたプロシージャ、少し試してみますね。
問題のあった内容を書いておきます。この記述で離脱条件に問題があるでしょうか?

Sub 空欄を非表示()
' 50行になるまで隠すを繰り返す
Range("A1").Select
Do Until ActiveCell.Row = 50
If ActiveCell.Value = "" Then
Selection.EntireRow.Hidden = True
End If
ActiveCell.Offset(1).Select
Loop
End Sub

ループ内の作業が終わってもマクロが終了していないということがあるのでしょうか?他にもたくさんのプロシージャを書いていますので、チェックしたいと思います。
他にもFor-Next型も使っていますが、「For i = 1 To 100」としているので大丈夫だとは思うのですが、VBAはまだまだ知らないことがたくさんありますので・・・(見た目の仕組みとしてはキチンと実行されています)

投稿日時 - 2006-09-02 01:33:47

お礼

ご提案頂いたコードですが、私の記述よりはるかに高度ですね!(私がこのようなコードをスラスラと書けるようになるには時間が必要ですが・・・)
実際にセルを動かさずに実行できるのでさすがに実行速度が速いです。色々試してみたのですが、今のところご提案頂いた記述による実行で異常に速度が遅くなるという現象は起きていません。前回の御質問といい大変参考になります。
ところで、例の遅くなる現象ですが、セルを実際に動かしたとしても、正常なら0.5秒で終わるものが5秒以上かかってしまう原因は気になりますね!もう少し他のプロシージャの実行が関係していないか調査してみます。

投稿日時 - 2006-09-02 01:55:37

あなたにオススメの質問