ExcelでActiveCellの下に行を追加するとエラーとなる

Question

現在、VBAで以下の機能を実装しようとしています。

１．特定のセルに入力制限（リスト）を掛ける
２．リストから選択した値が特定の値の場合、アクティブセルの下に１行追加する

問題は【２】の追加時にエクセルが異常終了してしまう事です。

コードは以下の通りです。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then
        Select Case ActiveCell
            Case "変更": AddCell
        End Selects
    End If
    End Sub

Private Function AddCell()
    Rows(ActiveCell.Row + 1).Insert
End Function

この時、AddCellファンクションの+1を削除すれば正常にアクティブセルの上に行が追加されますが
+1を付与し、下行を追加しようとするとエラーになります。

本原因の解決方法、代替方法等をご教示頂けないでしょうか。

宜しくお願い致します。

Answer 1

いわゆるイベントの連鎖が発生しています。
行を追加した時点でワークシートの変更イベントが発生してしまうためです。
手っ取り早く対応するには、イベントを抑止するのがよいでしょう。
Application.EnableEventsで制御できます。

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then
        Select Case ActiveCell
            Case "変更": AddCell
        End Select
    End If
    Application.EnableEvents = True
End Sub

Comments

[ronin]

ご解説頂きありがとうございます。
上に追加と下に追加で挙動が変わるのは、追加時にアクティブセルが変わるか否かによるものみたいですね。
今回初めてChangeイベントを使用してみたのですが、理解するには時間が掛かりそうです。

[ttyp03]

そんなに難しい話ではなくて、シートの内容が変わったらイベントが発生するだけです。
そこだけに注意してコーディングすれば大丈夫なはずです。

[ronin]

イベント自体は変更が入った時点で毎回呼ばれるんですね。
納得できました。度々のご指摘本当にありがとうございます。

Answer 2

デバッグしてますか？
下に追加の場合、ActiveCellが変わらないまま再度WorkSheet_Changeが動いてしまいます。
※この時まだAddCellが終わっていないことにも注意して下さい

Comments

[ronin]

なるほど、下に追加したタイミングでWorkSheet_Changeが再度呼び出されるんですね。
なぜそうなるのかが良くわかりませんが、承知致しました。
ご回答ありがとうございます。

[S_kawa]

ttyp03さんの回答にもありますが補足すると
VBAコードだろうと、エクセルを実際に触ろうと、シート上の何かに変更を加えると起動するのがWorkSheet_Changeというイベントです。
なので
手で”変更”を入力>WorkSheet_Change>AddCell>WorkSheet_Change>AddCell…
と繰り返されていきます。
(つまり延々と行追加され続けるデバッグでステップ実行するとその様子が伺えます)
ちなみにプログラムとしてはこの時、関数を呼び出す度にコールスタックという呼び出し履歴が溜まっていきますが、この履歴用の領域は言語によって上限があって溢れるとスタックオーバーフローが発生します。
(多分質問のエラーはこれが発生している)

[ronin]

補足頂きありがとうございます。
上に追加（通常）するとアクティブセルが変わる為に正常に終了し、下に追加するとアクティブセルが変わらない為、イベントトリガーに掛かり再度呼び出される、という事なんですよね。
ご指摘頂いた後で（申し訳ありません）デバッグを試行してみましたが、確かに行を追加した後で再度Worksheet_Changeが呼びされてループをしておりました。

[S_kawa]

ほんのちょっとだけ違います。
追加したら上下は問わずWorkSheet_Changeは呼ばれます。
ただ上の時はご認識の通りアクティブセルが変わっているのでAddCellは呼ばれず終了しますね。
つまり
WorkSheet_Change(1) start > AddCell start > WorkSheet_Change(2) start _ end > AddCell end > WorkSheet_Change(1) end
って事です。
これもデバッグで動きを見てみると良いですよ。

[ronin]

すみません、仰る通りです。
度々補足頂きありがとうございます。
納得できました。