Excel2013 マクロでｃｏｌｏｒｉｎｄｅｘを使用すると処理が重い

Question

Ｅｘｃｅｌ2003から2010までは、特に問題なく処理されるがＥｘｃel2013では極端に処理が遅い。

セルの色を消したり付けたりする数が多い。

ＰＣ：Ｅｘｃｅｌ2013　Ｗｉｎ７

使用マクロ：

条件によりセルの色をかえる。

' 入力されたセルは色を変える

Call NoColor(ColorRange, 6)

' 色を消す

Call NoColor(ColorRange, xlNone)

' セルに色付けをする

Function NoColor(ByVal ColorRange As Range, ByVal ColorNo As Variant)

Application.EnableEvents = False

CheckSheet.Unprotect Password:="ＡＡＡ-5140"

ColorRange.Interior.ColorIndex = ColorNo

CheckSheet.Protect Password:="ＡＡＡ-5140"

Application.EnableEvents = True

End Function

どうすれば、Ｅｘｃｅｌ2013でも処理を軽くできますか。

Answer 1

と言うことは原因は別みたいですね。

どこかにこだわる前に遅くなっている原因を特定してください。

また、原因が特定できたら、その現象が発生するサンプルコードを作って提示してください。

あと、最初のコードで気になるのは、Protectをいちいち外したり設定したりしている点です。

こういうのはコードの初めと最後でやれば良い話です。

またDoEventsもどういう理由で入れているのか分かりません。

（このコードでは必要ないと思います）

テストとしてこれらをコメントアウトして試してみることをお勧めします。

Answer 2

5000足らずですね。

他の処理もされているのかもしれませんが、2010の10秒ってのも印象としては時間がかかりすぎです。

とにかくScreenUpdatingを殺して見たらいかがですか？

また、新規ブックで私のテストプログラムを実行した場合どの程度かかりますか？

私の環境では2013でも10000セルの色づけと消去で0.5秒くらいです。（ScreenUpdatingは生きている状態）

 

確かに、同じ様に0.5秒くらいです。（ScreenUpdatingは指定なし）

Answer 3

5000足らずですね。

他の処理もされているのかもしれませんが、2010の10秒ってのも印象としては時間がかかりすぎです。

とにかくScreenUpdatingを殺して見たらいかがですか？

また、新規ブックで私のテストプログラムを実行した場合どの程度かかりますか？

私の環境では2013でも10000セルの色づけと消去で0.5秒くらいです。（ScreenUpdatingは生きている状態）

Answer 4

早速のご指摘ありがとうございます。

処理時間については、Ｅｘｃｅｌ2003から2010までは１０秒そこそこですが

Ｅｘｃｅｌ2013ですと２分弱に及びます。対象のセルは、およそ３０行＊１５０ほどです。

追記

エクセルの保存形式　：　Ｅｘｃel97-2003

Application.ScreenUpdating　　試しましたが効果なし

Ｅｘｃｅｌ2013バージョン　：（15.0.4420.1017）ｏｒ（15.0.4535.1507）ともに同じ結果

Answer 5

本論には関係ないですが、Function NoColor

と書かれていますが、Functionの形になってません。（値を設定していない）

Sub　プロシージャとして作成されるべきだと思います。

それで、↓のようなテストプログラムを作成して、Excel2010とExcel2013 で比較してみました。

対象が1000くらいだとどちらにしろ一瞬で、違いが分かりませんでしたが、10000にすると確かに多少2013の方が遅いのが目視で確認できました。（一応違いが判る程度です）

ただ、これですと極端に遅いと言う話にはならないと思ったのですが、一つ思い当たるのは私の環境ではExcel2013のアニメーションを殺しています。

このアニメーションが”遅い”と言う原因であるとすると、VBAの処理の最初で、

Application.ScreenUpdating=False

として、画面の更新を止め、最後で

Application.ScreenUpdating=True

で戻せば改善されるかもしれません。

参考まで。

なお、蛇足ですが極端に遅いとおっしゃっても、具体的にどのくらいなのか第三者にはわかりません。

例えば私のテストプログラムを2010と2013で比較して、「2010だと一瞬だが、2013だと5秒くらいかかる」

とか具体的に示された方がよろしいかと思います。

Sub NoColor(ByVal ColorRange As Range, ByVal ColorNo As Variant)

  ColorRange.Interior.ColorIndex = ColorNo

End Sub

Sub main()

With ActiveSheet

  For I = 1 To 10000

    NoColor .Cells(I, 1), 6

  Next

  For I = 1 To 10000

    NoColor .Cells(I, 1), xlNone

  Next

End With

End Sub