数列の複製をするVBAマクロで 実行時エラー'-2147417848(80010108)':が発生 直前に入力規則のあるセルを直前に触った場合に発生します

Question

###前提・実現したいこと

エクセルVBAにて、数列の複製をするマクロを作成しましたが、下記の問題が解決できず困っています。

下記のマクロを動かすと、エラーが発生する場合があります。大容量のシートの場合に下記のエラーが発生します。
入力規則のあるセルを直前に触り、値を入力した後にマクロを起動した場合に発生するようです。

###発生している問題・エラーメッセージ

.Range(.Columns(第一列左列 + 一台列数 * 繰返し数 + 1), .Columns(第一列左列 + 一台列数 * 繰返し数 + 1)).Insert Shift:=xlToRight
のところで、

実行時エラー'-2147417848(80010108)':
オートメーションエラーです。
起動されたオブジェクトはクライアントから切断されました。

###該当のソースコード
```ここに言語を入力
    With ActiveWorkbook.Sheets(MySheet)
       .Unprotect
        
        第一列左列 = .Cells.Find(What:="第一列左列", After:=.Range(検索開始セル), LookIn:=xlComments, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False).Column       '選択された列番号を取得
        最終列右列 = .Cells.Find(What:="最終列右列", After:=.Range(検索開始セル), LookIn:=xlComments, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False).Column       '選択された列番号を取得
        一台列数 = 最終列右列 - 第一列左列 - 1
        
        繰返し数 = 1

Line1:
            
        .Range(.Columns(第一列左列 + 1), .Columns(最終列右列 - 1)).Copy
        
        .Range(.Columns(第一列左列 + 一台列数 * 繰返し数 + 1), .Columns(第一列左列 + 一台列数 * 繰返し数 + 1)).Insert Shift:=xlToRight
        
        繰返し数 = 繰返し数 + 1
        
        If 繰返し数 < 列複製数 Then GoTo Line1
        
        Application.CutCopyMode = False
            
        On Error Resume Next
            Set シート保護をかけません = .Cells.Find(What:="シート保護をかけません", After:=.Range(検索開始セル), LookIn:=xlComments, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False)      '選択された行番号を取得
        On Error GoTo 0
        If シート保護をかけません Is Nothing Then
            .Protect
        Else
            .Unprotect
        End If

    End With

###試したこと
入力規則を触った後でも、軽いシートの場合には、発生しません。

###補足情報(言語/FW/ツール等のバージョンなど)
Excelバージョンは2010です。

Comments

[coco_bauer]

「入力規則のあるセルにリストからデータを選択した後に」というのは、入力規制のあるセルに値を代入しようとしたという事ですか？　エラーが発生する状況が、よく理解できないのですが(主として日本語が）

[101]

早速ありがとうございます。エラーが発生するのは、マクロ起動中です。「入力規則のあるセルにデータを投入した後にマクロを起動するとエラーが発生します」その他の場合には問題が起こらないようです。解りにくくてすみませんが、ご教示いただければ幸いです。

Answer 1

バシッとした回答ではありませんが…

オートメーションエラーは経験上、過負荷っぽい時（？）、大量の情報を処理しようとする時（？）、無いオブジェクトにアクセスしようとしたり（？）といった、普段あまり起きないであろう「もろもろ」の状況でよく起きている気がします。

まずは、エラーが起きるであろうブックを保存し、どのシートのどの部分でエラーが起きるか、再現性を検証するのに必要なデータ(材料）をそろえてください。

続いて、VBAエディタでコードの左端をつつくと、ブレークポイント（赤い丸）を付けられたと思います。VBAを走らせたときに、このブレークポイントで一時停止できますので、以下の行より上の部分で怪しいところを何か所かマークを付けて走らせてください。特に怪しい部分については、一行毎に動かしてください。

.Range(.Columns(第一列左列 + 一台列数 * 繰返し数 + 1), .Columns(第一列左列 + 一台列数 * 繰返し数 + 1)).Insert Shift:=xlToRight

この時に、この行で何が起きてオートメーションエラーが出てきたか、実は少し前の行で異常な動作をしていた、などを元にもう一度teratailで相談してみてください。

むしろ、ここまでできればきっと自力で解決できると思います。
どうしてもオートメーションエラーを解決できないときには、全く別の関数や方法を使って代替手段を用意するしかないと思います。

Good luck!

Comments

[101]

slash様
早速のご回答ありがとうございます。
おっしゃる通り、エラーが起こるシートは決まっているようなんです。
前段階で異常が起こっているとの発想はありませんでしたので、
もう一度、一行づつ確認してみます。新たな発見がありましたら、また、質問させてください。
今後もよろしくお願いいたします。

[101]

slash様

おはようございます。
ご指導いただいた様に、数種類のシートで確認し、エラーが起きる前に異常がないかも確認しました。
あるシートで、異常なく終了した場合には、それ以降「入力規則のあるセル」を触らないかぎり問題が起こりません。
そこで、問題の行の前に、下記のようなダミーのコピー貼り付けを挿入してみましたところ、問題解決いたしました。根本原因にたどりつけていない感じはありますが、処理速度にも影響がないようですので、これにて対処しようと思います。この度は、貴重なご指導大変ありがとうございました。助かりました。

On Error Resume Next 'エラー処理
.Range(.Columns(Columns.Count), .Columns(Columns.Count)).Copy
.Range(.Columns(Columns.Count), .Columns(Columns.Count)).Insert Shift:=xlToRight
On Error GoTo 0 'エラー処理解除