VBA '_default'メソッドは失敗しました 'range'オブジェクトの回避方法

Question

Excel　VBAのエラー回避方法

①A列に自動的に連番をつけています。
②B列にセルCとDを結合をしています。
③削除編集時、連番再振り替え、B列に編集内容の結合
を行っています

行数が増えてくるとエラーが起きてきます。
実行時エラー　-'-2147417848（80010108）
'_default'メソッドは失敗しました　'range'オブジェクト

他サイトで2014年時同様なエラーコードの説明では、一種の無限ループ状態
とありましたが理解できず。
解決方法はあるでしょうか？　ご教授宜しくお願いします。

Excel　VBA　sheet　コードです

**①**連番を振るVBA
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long
    i = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        If Cells(i, "D") <> "" Then
            Cells(i, "A") = i - 2
            Cells(i, "B") = Cells(i, "C") & Cells(i, "D").Value2
        End If
  End Sub
**②**'削除編集時に連番、結合の編集VBA
Private Sub Worksheet_Calculate()
 Dim i As Long
    i = Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Row
      Range(Cells(3, 1), Cells(i, 2)).Select
    Selection.ClearContents
End Sub
```EXCEL
![イメージ説明](02c8ae8d5b6cabb78a9279967c7c7917.jpeg)
![イメージ説明](515d669b74306f735505ac5d413c3dc4.png)

Answer 1

他サイトで2014年時同様なエラーコードの説明では、一種の無限ループ状態

Worksheet_Changeはシート上のどこかのセルの値が変更されたら実行されるイベントです。

この「どこかのセルの値が変更されたら」ですが、ユーザーの入力も、マクロによる変更も同等に扱われます。

質問のコードの以下の箇所では、セルにi - 2などの値を設定しています。

            Cells(i, "A") = i - 2
            Cells(i, "B") = Cells(i, "C") & Cells(i, "D").Value2

「値を設定する」、つまりセルの値が変更されたこととなり、再度Worksheet_Changeが呼び出されます。

1. ユーザーが値を変更する
2. Worksheet_Changeが動作
3. Worksheet_Change内でセルの値を変更
4. セルの値が変更されたのでWorksheet_Changeが動作

...という動作になる可能性があるため「一種の無限ループ状態」ということになります。

対策としてはApplicationオブジェクトのEnableEventsプロパティを一時的にFalseにすることです。
Falseにすることで、そのExcelで発生するすべてのイベントが無効化されます。
当然Worksheet_Changeも無効化されるため、無限ループが発生しません。

---

変更後イメージ

'https://teratail.com/questions/196641
'**①**連番を振るVBA
Private Sub Worksheet_Change(ByVal Target As Range)
    Const HeaderHeight = 2
    
    Dim r As Long
    r = Me.Cells(Me.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    
    If Me.Cells(r, "D").Value <> "" Then
        'セルを編集するため、一時的にイベント無効化。
        Me.Application.EnableEvents = False
        
        Me.Cells(r, "A").Value = r - HeaderHeight
        Me.Cells(r, "B").Value = Me.Cells(r, "C").Value & Me.Cells(r, "D").Value2
        
        Me.Application.EnableEvents = True
    End If
End Sub

'**②**'削除編集時に連番、結合の編集VBA
Private Sub Worksheet_Calculate()
    Const HeaderHeight = 2
    
    Dim r As Long
    r = Me.Cells(Me.Rows.Count, "A").End(xlUp).Offset(0, 0).Row
    With Me.Range(Me.Cells(HeaderHeight + 1, 1), Me.Cells(r, 2))
        .ClearContents
        
        .Select '必要であれば
    End With
End Sub

Comments

[masayone]

imihitoさん
ありがとうございます。
親切な説明もついて、且つ変更後のコードも作成頂き大変助かります。 Me.Application.EnableEvents = False　等もNetで見ることはありあますが、実際どの部分に書けばいいのか理解できていない初心者なのでありがたいです。
早速、実装して試してみます。今後とも宜しくお願いします。

[masayone]

imihitoさん
早速頂いたコードをコピペし実行してみました。
データがC列～X列まで、５８０行が入力されています。
表は「テーブル」にしています。
今まで、連番をオートフィルで表示、B列は＝C＆Dで表示していましたが
AB共に空白になり「範囲をDel」等で操作しましたが反応なし。
結果：A列B列が空白のままで表示されません
テスト環境のシートを作り（列数は減らして）一行づつ入力の際はAB共に表示されて行きます
行数が多くなるとVBAが反応しない状態です（メモリ不足等のメッセージは出ていませんン）
※先に作成のコード（無限ループが起こる前）でも同様な現象が起こりました。
見えないところで、メモリ不足等があるのでしょうか？
細かい説明が出来ていませんが、再度、ご教示お願いたします。
データ入力はユーザーフォームから登録しています。（各項目の合計等の計算」をさせています。
コードは他にも２・３書いてあります。各コードが実行しない場合でもメモリを消費することがあるのでしょうか？
また必要であれば、再質問の形で、全てのコード、フォームを貼付けした方が宜しいでしょうか？
稚拙な質問で申し訳ありませんが、宜しくお願い致します。

[imihito]

まずはVBAが反応しているかどうか、の確認として
Worksheet_Changeなど各種イベントにブレークポイントを設定するなどして、処理が呼び出されたどうか確認しても良いかもしれません。

また今日以降は私が対応できない可能性が高いため、解決が難しそうであれば新たに質問をした方が良いと思われます。

[masayone]

imihitoさん
分かりました、お教え頂いたように、ブレークポンとで反応を確認しました。
Me.Cells(Me.Rows.Count, "A").End(xlUp).Offset(1, 0).RowのOffset(0, 0)に変更することで解決しました。
最終行をデータのある1行下を参照するため、Cells(r, "D").Value <> "" Thenは空白となり、End Subまで飛んでしまいました。アドバイスありがとうございました。
ただ、削除編集時に連番、結合の編集VBAは.ClearContentsで条件により
A列とB列がクリアーされ空白になってしまうので、コメントアウトして無視することにしました。
A連番とB文字連結はApplication.EnableEvents = Falseでメモリ不足が解消されています。
また、質問の際にはご教授下さい。
ありがとうございました。