エクセルVBAの実行処理速度がマウスカーソルを置く位置とfor-nextカウンターの初期値に依存する

Question

現在、日常的に使用しているexcelで処理時間が問題となり調査している過程で表題に挙げた問題に出会いました。

現象は以下の手順で容易に再現できるが原因がわかりません。よろしくお願いします。

簡単にその事象が確認出来るスクリプトを下記しました。

このスクリプト実行すると各サブ・プロシージャ(Proc_A,Proc_B)と全体の実行時間が表示される。

下の表は、このスクリプトを使って各々１０回の試行にて測定した処理時間の平均とばらつきです。

実際にはPC環境を（DELL、Win７、Office２０１０－３２Bit）、（Lenovo、Win７、Office２０１０－３２Bit）、（Dynabook、Win８、Office２０１０－６４Bit）

と変えて実施しました。処理時間は環境による違いはありますが、現象は変わりません。　下の表はDELL環境による測定結果です。

１．

Proc_A,Proc_Bのfor-nextカウンターJを（J=0 to 6）とし、マウスカーソル

　　をシート内に置いた状態にし、ショートカット・キーでスクリプトを実行。

--------------- J=0, シート内 --+-- J=0, シート外 --

                         平均    分散       平均    分散

Proc_A(T1-T0)   0.002   0.000    0.002   0.000

Proc_A(T2-T1)   0.001   0.000    0.000   0.000

Proc_A(End-T0) 0.067   0.000    0.070   0.000

２．

Proc_A,Proc_Bのfor-nextカウンターJを（J=6 to 6）とし、マウスカーソル

　　をシートの外に移した状態にし、ショートカット・キーでスクリプトを実行。

--------------- J=6, シート内 --+-- J=6, シート外 --

                         平均    分散       平均    分散

Proc_A(T1-T0)   0.000   0.000    0.000   0.000

Proc_A(T2-T1)   0.000   0.000    0.000   0.000

Proc_A(End-T0) 0.565   0.000    0.076   0.000

<------------ 今回のテストに使ったスクリプト ----->

Option Explicit

    Dim C As Variant

    Dim R As Variant

    Dim T0 As Double

    Dim T1 As Double

    Dim T2 As Double

Private Sub Proc_A()

    Dim i As Integer

    Dim j As Integer

    ReDim C(6, 300)

    For i = 0 To 299

        For j = 0 To 6

            C(j, i) = "Test"

        Next j

    Next i

End Sub

Private Sub Proc_B()

    Dim i As Integer

    Dim j As Integer

    ReDim R(6, 300)

    For i = 0 To 200

        For j = 0 To 6

            R(j, i) = C(j, i)

        Next j

    Next i

End Sub

Private Sub Proc_C()

    Dim i As Integer

    Dim j As Integer

    For i = 0 To 200

        For j = 0 To 6

            Cells(2 + i, 4 + j).Value = C(j, i)

        Next j

    Next i

End Sub

Sub Test()

    Application.ScreenUpdating = False

    Worksheets("sheet1").Activate

    T0 = CDbl(Timer)

    Range("B2:B5").ClearContents

    Range("D1:J300").ClearContents

    Proc_A                                  '配列Cに文字列"TEST"を代入。

    T1 = CDbl(Timer)

    Proc_B                                  '配列Cの各要素を配列Rに代入する。

    T2 = CDbl(Timer)

    Proc_C                                  '配列Rの各要素をセルに書き出す。

    Application.ScreenUpdating = True

    Range("B2").Value = T1 - T0             'Proc_Aの処理時間。

    Range("B3").Value = T2 - T1             'Proc_Bの処理時間。

    Range("B5").Value = CDbl(Timer) - T0    '全体の処理時間。

    Beep

End Sub

Answer 1

＞0でのクリアやられましたか？

variantに戻して再度実行してみました。

確かに０で初期化するとマウスの内・外に関係なく約５１ｍSで終了する事が確認できた。

その意味で、この「０クリア」は一つの解決法と言えると思います。

うむ、それでもどこかスッキリしない。　とりあえず、

今回の問題を以下に整理してみた。（私の環境での測定結果：再掲）

1. J=０でスクリプトを実行すると、マウスの内・外に関係なくほぼ瞬時（５０～７０ｍS）で終了。
2. J=６でスクリプトを実行すると、マウスの内・外に関係有り。外：瞬時（５０～７０ｍS）、内：５６０ｍS前後。（補足：　J ＝１～６まで順に増すと約８０～１００ｍSづつ増加する）
3. スクリプトの実行条件：　エクセルシートのウインドウが最前面である事。（補足）

今回、頂いたコメントから解った事。

1. 対処方法としてvariant配列をNull以外で初期化すれば回避出来る。

で、仮に初期化の無い事、或は、値の入ってない配列要素をセルに書き出す事、が問題だとすると、

依然、残される疑問として

1. 初期化してない（ReDimのみ）の場合であっても、マウスが外の時はJ の値に関係なく瞬時に終了。　　（問題整理の１．、２．）

と言う事実に対する説明が困難になる。

実際のアプリケーションで値の入ってない配列要素をセルに書き出すケースは殆ど無いと思います。

今回のスクリプトは質問投稿にあたり現象を簡単に再現出来る様にと作った特別なケースですが、

現象は決して特別とは思っていません。

Answer 2

ヴァリアント変数は使っていません。

ご指摘をいただいてから文字列型に変更しました。でも結果は同じです。

＞マウスの件は忘れてください。

思い込みだと思いますよ。

思い込みではありません。

実際にスクリプトを実行されましたか？

現実にタイマーが１０倍も違うのをどの様に説明されますか？

コードだけを見ただけではとても考えられない事だから質問しているのです。

Answer 3

y sakuda さん、いつもアドバイスありがとうございます。

Lazy Fox さん、こんにちは。

マイクロソフト コミュニティをご利用いただきありがとうございます。

紹介されているコメントが参考としていただけたようでよかったです。

同様の質問でこちらのスレッドを見られた方にも確認として参考になるよう [ 回答としてマーク ] をさせていただきました。

また質問や不明点がありましたらマイクロソフト コミュニティをぜひご利用ください。

Answer 4

プロシージャのつくり方の基本が未だ呑み込めていません。

紛れを残さない記述やグローバル変数の扱いなどとっても貴重なご意見

ありがとうございました。

＞マウスカーソルを置く位置は関係ないでしょう。

（まさかと思いましたが、結果に違いはありません）

variant型配列を整数型のLongにかえて同様なテスト行ってみました。

確かにこのケースではマウスカーソルの位置には関係しなようです。

しかし、文字列型の配列にして同様なテストをすると質問時と同じ結果になります。

つまり、J =6 to 6 としてマウスカーソルをシート内の任意のセルに置いておくと約５６０mSかかります。

一方、同じ条件でマウスカーソルをシートの外に出すと約５４mSで終わります。

しかも、周りにあるどのPCでテストしても時間の差はありますが、J = 6 to 6 （初期値が０以外でも可）の

時に限り、マウスカーソルがシートの内か外によって大きく差が出ます。

J =0 to 6の時はマウスカーソルの位置は関係ありませんでした。

よろしくお願いします。

Answer 5

マウスカーソルを置く位置は関係ないでしょう。

（まさかと思いましたが、結果に違いはありません）

問題はカウンタの扱いのようです。

問題はProc_Cではどちらのケースもj=0 to 6 でやっておられるので、a,bをj=6 to 6にした場合、

値をセットしていない状態のバリアント配列から大量にシートへの転記が発生しています。

正直こういうことになるとは知りませんでしたので、ここから先は推測です。

１．バリアント型は値が実際に設定されたところで、初めて型が決まる性質があります。

２．シートに転記する際、セル側はセルにセットするデータの方を認識する必要がある

３．値がセットされたバリアント変数については型は既にきまっているため、瞬時に転記されるが、

　値がセットされていない場合はその度に転記されるデータの型を決定しようとするため時間がかかる

こんなところだと思います。

参考まで。

で、話は全く関係ありませんが、提示されたコードで気になるところを２~３述べさせていただきます。

１．シートの指定がされていない

　　 Range("B2").Value = T1 - T0            

　　とするとActiveSheetが対象になりますが、複数のシートを扱っている場合バグの原因になることが非常　　　に多いと考えます。

　　With ActiveSheet

　　　　．Range("B5").Value = CDbl(Timer) - T0   

　　End with

　　のように常にオブジェクトを完全に修飾して紛れをなくすべきです。

２．グローバル変数の使用

　各プロシージャで共通に使用される変数をすべてグローバル変数として定義されています。

　確かにグローバル変数は便利な場合もあるのですが、全処理が終了しても値が初期化されないという厄介な問題があります。

　ですから1回目の処理とそれ以降の処理では初期状態が異なってしまい結果が違ってくる場合があります。

　また、トラブルが発生した際、その変数がどこで変更されているのか突き止めるのが非常に困難になる場合もあります。

　サブプロシージャへ渡す値や変数はパラメータ化することをお勧めします。

　↓の例のように配列も渡せます。

Sub aaa(pV())

  MsgBox UBound(pV)

  ReDim pV(100)

  MsgBox UBound(pV)

End Sub

Sub TestMain()

Dim wAry()

ReDim wAry(20)

aaa wAry

MsgBox UBound(wAry)

End Sub