EXCEL2010エラーVBA

質問

下記を実行するとエラーになりEXCEL2010が終了してしまいます。
fDebug:0 offset00009391　がエラーメッセージです。
何が原因でしょうか。

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim rg As Range
Dim r As Variant
Dim c As Long
Dim hanni1 As Range
Dim atai As Range

Set ws = Worksheets("あああ")
Set rg = Worksheets("コード").Range("A1:B10")
r = ws.Cells(Rows.Count, 1).End(xlUp).Row
Set hanni1 = ws.Range(Cells(2, 2), Cells(r, 3))
Set atai = ws.Range(Cells(2, 3), Cells(r, 3))

atai = Application.VLookup(hanni1, rg, 2, False)

End Sub

回答No.3

queuerev2 · ベストアンサー

イベントの発生を停止する方法やイベントを判別する方法を解説いたします。

１．イベントの発生を停止する。
以下のようにすれば、ataiを書き換えてもイベントが発生しません。

Application.EnableEvents = False
atai = Application.VLookup(hanni1, rg, 2, False)
Application.EnableEvents = True

注意点としては、イベント発生を再開する前にエラー等でVBAの実行が停止した場合などApplication.EnableEvents = Falseのままになってしまうと、何らかの方法でApplication.EnableEvents = TrueにしないとExcelを終了して再び起動するまでExcelのすべてのイベントが発生しなくなります。

２．フラグでイベントを判別する
プロシージャが終了しても値が消えない変数を確保してイベント判別のフラグとして使用します。
下記の例ではモジュールレベル変数を使用し、初期値がFalseになることを利用しています。

Private AAA_Change As Boolean 'モジュール先頭の宣言部に書く

Private Sub Worksheet_Change(ByVal Target As Range)
・・・（変数宣言）・・・
If AAA_Change Then Exit Sub
・・・（コード続き）・・・
AAA_Change = True
atai = Application.VLookup(hanni1, rg, 2, False)
AAA_Change = False

注意点としては、何らかの原因でAAA_Change = Trueのままになってしまうとこのプロシージャ（のIf AAA_Change Then Exit Subより後の部分）が実行されなくなります。
復帰ですが、ENDの実行、VBAのリセット、コードの変更等で変数が初期化されますので、１．のイベント発生停止よりは復帰が容易です。

３．変更する場所（セル範囲）でイベントを判別する
Worksheet_Changeでは、変更のあった場所が変数Targetで渡されますので、その場所を見て処理を行うかどうかを決めるという方法があります。
この方法では、エラー等でataiの書換えが行われなくなることがありませんが、処理を行わない範囲（以下の例ではC列）を書き換えてもataiの書換えは行われないという点で１．や２．とは動作が異なります。
以下の例では簡単のためTargetの（最も左の）列がC列ではない場合に処理を行います。

Private Sub Worksheet_Change(ByVal Target As Range)
・・・（変数宣言）・・・
If Target.Column = 3 Then Exit Sub
・・・（コード続き）・・・
atai = Application.VLookup(hanni1, rg, 2, False)

注意点としては、シートのレイアウト変更を行った場合にセル範囲がずれてうまく動作しなくなることがあります。

お礼

ありがとうございます。
何とか設定できました。

回答No.2

queuerev2

No.1です。
前提を書き忘れました。
質問文中のプロシージャ Worksheet_Change はシート"あああ"にあるとしました。
もしそうでない場合、No.1の回答は成り立ちません。

回答No.1

queuerev2

Excel2007でやってみました。
エラーの種類は違いますがやはりエラーにはなります。

原因は、
atai = Application.VLookup(hanni1, rg, 2, False)
で範囲ataiを書き換えるとワークシートの内容が変更されたと判断されてその場でまたこのプロシージャが呼ばれ、そうするとまた上記のVLookupの行に行き、、、、、、
となり、このプロシージャの呼び出しが無限に続いてしまうということです。

対策ですが、ataiを書き換える前にイベントを一時的に止めてしまうか、手動によるシート内容変更と上記の行によるataiへの書き込みを判別して上記のVLookupの行を実行するかどうかを決めるかすればよいと思います。

お礼

回答をいただきありがとうございます。
イベントを一時的に止める方法を勉強してみます。

補足

>対策ですが、ataiを書き換える前にイベントを一時的に止めてしまうか、手動によるシート内容変更と上記の行によるataiへの書き込みを判別して上記のVLookupの行を実行するかどうかを決めるかすればよいと思います。

上記の点ですが、再計算等調べてみましたが具体的に設定できませんでした。
VBAの勉強を始めたばかりで力不足のためです。
もう少し具体的にアドバイスいただけるとたすかります。