Excel VBA Do～Loop時のドラッグ操作

質問

はじめまして。

ExcelでGUIなガントチャート作成ツールっぽいものを作ろうと思っています。
そして、現在以下の実装で検討中です。

・実際のタスクを管理する「タスク」シートと、タスクを表として表示する「カレンダー」シートを別に管理。
・「タスク」シートにはタスクID・タスク名・開始日・終了日の情報を保持
・「カレンダー」シートは、
　1～3行目にカレンダー、1～5列目がタスクID・タスクの分類・タスク名などの情報を表示し、
　「タスク」シートに記載されているタスクを「カレンダー」上にプロット。
・ユーザは「カレンダー」シートのタスク(セルで持っています)をマウスで操作することで、
　タスクの追加・削除やスケジュール変更を行う
※Excelはユーザのマウスによる操作をセルの座標から該当タスクを特定し、
　 「タスク」シートの該当タスクの内容をセルの操作に応じて更新。
　 その後、更新後のタスクを元にカレンダーを再描画する。

---
で、「カレンダー」シートには、下記の要領のプログラムをかいています。

'常駐監視処理の操作用フラグ
dim Flg as Boolean

'ワークシートがアクティブになったときの処理
Private Sub Worksheet_Activate()
Flg = True
Call 監視
End Sub

'ワークシートが非アクティブになったときの処理
Private Sub Worksheet_Deactivate()
Flg = False
End Sub

Sub 監視()
　debug.print "■監視開始"
Do While Flg
　　　'ここに監視用の処理を実装予定
　Loop
debug.print "■監視終了"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'セル更新時の処理
　　debug.print "■更新"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'選択範囲変更時の処理
　　debug.print "■選択範囲変更"
End sub

---

このプログラムを起動した状態で、該当シートでセルのドラッグ&ドロップの
操作を行った際、Excel.EXEアプリケーションエラーが発生します。
「デバッグ」ボタンを押すと以下の添付画像のダイアログが表示されます。

※Worksheet_Change/Worksheet_SelectionChangeのイベントでも
　 トラップすることができません。

このエラーを回避する方法がありましたら教えていただけると助かります。

回答No.2

TooManyBugs · ベストアンサー

Worksheet_Activate()で監視をCallしているのでループしているので処理が終了する前に次のイベントが多数発生してメモリを食い尽くしています。
イベントの処理が完結するまで(ユーザーの処理＋Windwsの処理)同タスクからのイベントはDoEventsでは取れずキューイングしたままになるはずです。

Worksheet_Activate()では監視をCallせずFlgの変更のみとして、監視そのものはタイマなどを使用して別に行うべきです。

お礼

ありがとうございました！以下のような実装でうまく行きそうです。

'以下、ワークシート内に記述
Private Sub Worksheet_Activate()
Flg = True
Call 監視2
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print "■更新"
End Sub

Private Sub Worksheet_Deactivate()
Flg = False
Debug.Print "===監視終了"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Debug.Print "□選択範囲変更"
End Sub

'以下、標準モジュールに記述

Public Flg As Boolean

Sub 監視タイマーセット()
If Flg Then
Debug.Print "★待機中"
Application.OnTime Now + TimeValue("0:0:1"), "監視2"
End If
End Sub

Sub 監視2()
On Error GoTo Err_Trap:
'ここに監視処理を記載
Debug.Print "===監視処理中==="
Call 監視タイマーセット

Err_Trap:
End Sub

回答No.1

webnas

Sub 監視()
Debug.Print "■監視開始"
Do While Flg
DoEvents　　 'ここに監視用の処理を実装予定
Loop
Debug.Print "■監視終了"
End Sub

ループ分の中でメモリがいっぱいになっちゃってますので
ループ分内にＯＳに処理を返す、DoEvents　を入れてみたらどうでしょうか？

補足

さっそくありがとうございます。
ご指摘の通り、DoEvents、確かに入れていました。

※監視処理のベースは下記のURLのものを使う想定です。
http://www.keep-on.com/excelyou/1999lng4/199909/99090194.txt