質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

2573閲覧

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

ronin

総合スコア89

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2019/04/08 03:06

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

1.特定のセルに入力制限(リスト)を掛ける
2.リストから選択した値が特定の値の場合、アクティブセルの下に1行追加する

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

イメージ説明

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

VBA

1Private Sub Worksheet_Change(ByVal Target As Range) 2 If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then 3 Select Case ActiveCell 4 Case "変更": AddCell 5 End Selects 6 End If 7 End Sub 8 9Private Function AddCell() 10 Rows(ActiveCell.Row + 1).Insert 11End Function

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

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

宜しくお願い致します。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

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

VBA

1Private Sub Worksheet_Change(ByVal Target As Range) 2 Application.EnableEvents = False 3 If Not Intersect(Target, Range("C1:C1000")) Is Nothing Then 4 Select Case ActiveCell 5 Case "変更": AddCell 6 End Select 7 End If 8 Application.EnableEvents = True 9End Sub 10

投稿2019/04/08 06:39

ttyp03

総合スコア16996

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ronin

2019/04/08 07:20

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

2019/04/08 07:36

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

2019/04/08 09:08

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

0

ベストアンサー

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

投稿2019/04/08 05:09

S_kawa

総合スコア156

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ronin

2019/04/08 06:21

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

2019/04/08 07:10 編集

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

2019/04/08 07:21

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

2019/04/08 07:33 編集

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

2019/04/08 08:01

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問