ExcelVBAのDoEventsによるエラー？

質問

Excel2010(Windows7 64bit)のVBAであるプログラムを作っていたのですが、以下の様な原因不明のエラーが生じてしまいます。

1.　自分では同じ操作をしているつもりなのですが、エラーが出る場合と出ない場合があり、エラーの原因を特定できません。

2. Errオブジェクトを調べたところ、エラー番号が「-2147417848」です。

3. エラー発生後にExcelがフリーズして数分待っても終了しません。仕方がないのでWindowsを再起動しています。

4.　エラーが発生する様になる前は、プログラムは問題なく動いてしました。動かなくなった日に行った変更は、時間がかかるループ内にDoEventsを入れたことと、モーダレスダイアログを表示するようにしたことです。ただ、その他にも細かい変更をしましたし、エラーが発生するタイミングが解らないので、これが原因かはわかりません。

以上です。もし、エラーの原因や解決方法等、何かご存じの方がいらっしゃいましたらご教授くださいますようにお願いいたします。

回答No.4

nofx35 · ベストアンサー

おはようございます。

モーダレスフォームが応答なしになるの対策ですが

処理の進歩を示すフォームなら、DoEventsでOSに処理を一旦渡さなくても
UserForm1.RePaintで出来ます。

もし
Label1.Caption = s & "% 処理中・・・"
DoEvnets

みたくなっているのでしたら

Label1.Caption = s & "% 処理中・・・"
UserForm1.RePaint

に置き換えてみるとどうでしょう？

このRePaintも頻繁に呼び出すと画面がちらつきますので呼び出す回数は工夫が必要
です。（例えば、10%毎に呼び出すとか）

VBAは処理速度が遅いので、WinApiのSleepを適度にはさんでやるのもいいのかも
しれません。

複数の環境で使われているみたいですから、条件付コンパイルを使ってSleepを宣言します。

#If VBA7 And Win64 Then
　'64ビット版
　　Public PtrSafe Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
　'32ビット版
　　Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

この様に標準モジュールに宣言をして

Do
　　Sleep 100
　　処理
Loop

とすると応答なしにならなくなります。CPUも100%使い切る事もなくなります。
このSleepは指定時間だけ処理を止めるものですから、コツは短い時間（ミリ秒）
を何度も挟むことです。長い時間はさむと処理もExcelの操作も一切出来ません
ので注意が必要です。

根本的な解決に繋がるか自信がありませんが、何かの参考になりましたら。

お礼

ご回答ありがとうございます。その後、色々試してみました。あるシートのセルにある程度まとまったデータを書き込むループがあったので、その中にDoEventsを入れていたのですが、そのループに入る前にScreenUpdatingをFalseにしていました。これをTrueにしたらエラーが出なくなりました。これが直接の原因だったのかはよく判らないのですが、とりあえずこれで進めてみます。色々お教えいただきましてありがとうございました。

回答No.3

nofx35

No2です。

このようなパターンでないですか？
DoEventsを入れてある無限ループ中にSheet1を手動で削除してみて下さい。
wsはNothingではありませんが、wsの実体がなくなってしまうと
オートメーションエラーになります。

新規ワークbookで試して下さい。

Sub Sample()
　　Dim ws As WorkSheet
　　Set ws = WorkSheets(1) '一番左のワークシートをセット

　　Do
　　　　DoEvents

　　　　If Not ws Is Nothing Then 'wsがNothingでないなら
　　　　　　Debug.Print ws.Name 'シートを削除すると、ここでオートメーションエラー
　　　　End If
　　Loop

End Sub

例はワークシートですが、UserFormへの参照や他諸々のオブジェクト変数の実体が
なくなる様な操作またはコーディングをしていませんか？

DoEventsは諸刃の剣と書きましたが、DoEventsをループ毎に読み込むのは逆に
処理速度を下げてしまいます。

Sub Sample2()
　　Dim i As Long
　　Dim s As Single

　　s = Timer
　　For i = 0 To 300000
　　　　DoEvents
　　Next
　　MsgBox Timer - s
End Sub

Sub Sample3()
　　Dim i As Long
　　Dim s As Single

　　s = Timer
　　For i = 0 To 300000
　　　　If (i mod 10000) = 0 Then 'iが10000で割り切れる時だけDoEventsを実行
　　　　　　DoEvents
　　End If
　　Next
　　MsgBox Timer - s
End Sub

ループ中にキャンセルを受け入れるのにはDoEventsは必要ですが、使用する回数は
極力少なくするのがいいと思います。（少なくするとキャンセルを受け入れるまで少しもた
つきますが。そこは加減で調整です。）

お礼

ご回答ありがとうございます。その後、いろいろ試してある程度原因となる箇所が解ってきました。あるシートに比較的大きな範囲(A～SS×1～86)のセルに値を入力するループがあり、その中にDoEventsを入れていたのですが、そのループをある条件で呼び出すとエラーが起きるようです。但し、複数のパソコンで実行したところ、発生するパソコンと発生しないパソコンがありました(excelのバージョンは同じ)。また、同じパソコンでも、サブルーチンを呼び出す順番を変えるとエラーが発生しなくなりました。更に、問題となるループ内のDoEventsを消せばエラーは発生しない様です。結局、DoEventsが原因だと考えて良いのだと思うのですが、DoEventsが直接の原因なのか、そもそも存在していたエラーがDoEventsによって顕在化したのかよくわかりませんでした。また、ループ内ではセルに出力する値を計算するコードもありますので、大量のセルに出力すること自体が問題かどうかもよくわかりませんでした。

DoEventsを入れる理由ですが、キャンセルを受け付けるだけならそれほど頻繁にDoEventsを呼ばなくても良いのですが、モーダレスダイアログに「...(応答無し)」と表示されてしまうと第3者がフリーズしたと勘違いしてしまうと思っています。ですので、DoEventsを入れなくても「...(応答無し)」と表示されないようにする方法があれば、ループ内のDoEventsをそれに置き換えれば良いと思うのですが...。

回答No.2

nofx35

こんばんは。

DoEventsは諸刃の剣ですからね。
一概にDoEventsが犯人だと決め付けられませんが怪しいですね。

ループ中にDoEventsを入れ込むと、ループ処理中に割り込みが可能になりますから
何か別の操作をしてしまったりすると、誤動作の原因にはなりますね。
DoEventsを入れた理由は何故でしょう？モーダレスフォームとは、もしかして
処理中を示す自作のプログレスバー的なものですか？

コードの提示がないのでこれ以上はわかりませんが
問題の原因を切り分けて調べて見ましょう。

DoEventsを削除するとエラーはおきなくなるか？
モーダレスフォームの表示をやめると？
この両方をやめると？

など個別に調べてみましょう。

ループ中に別の操作をしているなら"DoEvents"が8割怪しいですよ。

お礼

ご回答いただきましてありがとうございます。DoEventsを入れた理由ですが、仰るように、比較的時間がかかるループがいくつかあるので、キャンセルボタンを配置したプログレスバーのようなものを表示して、ループの前後でキャンセルが押されたから調べ、押された場合はプログラムの実行を中止するようにしました。また、そのままだとループ内でダイアログボックスのタイトルに「...(応答なし)」と表示されてしまうので、ループ内にDoEventsを入れました。
エラーの発生原因がわからず、エラーの発生率もそれほど高くないのでなかなか原因を特定できないでいます。DoEventsをコメントアウトしたりして試しているのですが、何となく、DoEventsを頻繁に呼ぶとエラーが発生しているように感じるのですが...。

回答No.1

anpauro11

http://schneisen.cocolog-nifty.com/blog/2012/03/excel-6736.html
上記のサイトに紹介されているケースとは違いますか？

エラーの発生箇所が分からないのであれば、絞り込むよう手を打つしか無いと思います。
具体的には「ある処理を行ったら現象が発生する」というのを徐々に絞り込んでいくということです。

残念ながらご質問の文面からではエラー原因を回答することは難しいと思います。
どうしても回答が必要な場合は、ソースコードの開示をお願いします。

お礼

ご回答いただきましてありがとうございました。ご紹介いただきましたサイトに示されているエラー番号が私の場合と同じですので、コレが原因かもされません。調べてみます。ソースコードですが、かなりの量を書いているコードのどこが原因なのか特定できていないため、このサイトに載せることができませんでした。もう少し調べてみます。