VBA オートメーションエラー'-2147417848(80010108)' ボタン2つを含むシートの再作成

Question

前提・実現したいこと

Excel 2013のVBAにおいてアクティブシートを削除・再作成し、ボタン2つを表示させるボタンを作成しようとしています。

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

実行時エラー '-2147417848(80010108)':

オートメーション エラーです。
起動されたオブジェクトはクライアントから接続されました。


※デバッグボタンを押すと、「ActiveSheet.Delete」で停止している。

該当のソースコード

Sub reMake()
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    Sheets.Add
    Set b1 = ActiveSheet.Buttons.Add(1, 0, 100, 10)
    Set b2 = ActiveSheet.Buttons.Add(1, 10, 100, 10)
    b1.OnAction = "reMake"
End Sub

試したこと

• 上のコードをVBEで実行して作成されたb1を2回クリックすると、オートメーションエラーが発生します。
• 上のコードをVBEで実行してボタン作成後、ブック再起動して、b1を3回クリックすると、オートメーションエラーが発生します。
• VBEからF5で実行する場合、発生しません。ループで1000回ほど実行しても発生しません。
• ActiveSheet.Deleteしない場合、発生しません。

-- アクティブなシート上の全てのオブジェクト削除は、必要な動作です。ここではシート削除→作成で実装しています。

• b2を作成しない場合、発生しません（但し必要な動作です）
• Application.DisplayAlerts = Falseは再現に必須でありませんが、メッセージが煩わしいため載せています。

Answer 1

ブック内にシートが最低でも１つはないといけません。
ActiveSheet.Delete　を行うことでシートが1つもなくなるためにエラーになっているのだと思います。
テストとして、シートを１つ追加してマクロを実行してみてください。
きちんと動くと思います。

対応としては、
・アクティブシートを変数に保持しておいて、先にシートの追加を行ってから削除する
・アクティブシートを削除する前にシートAを追加してマクロ終了時にシートAを削除する

これで行けると思いますのでご確認ください。

▼ 【例】シート作ってからアクティブシートを削除する仕様に変更したもの

Sub reMake()
    Application.DisplayAlerts = False
    Dim ws_Del As Worksheet
    Set ws_Del = ActiveSheet
    Sheets.Add
    Set b1 = ActiveSheet.Buttons.Add(1, 0, 100, 10)
    Set b2 = ActiveSheet.Buttons.Add(1, 10, 100, 10)
    b1.OnAction = "reMake"
    ws_Del.Delete
End Sub

Comments

[tokita.]

ご回答ありがとうございます｡
例に挙げて頂いたコードで動作が確認できました｡

追加情報です｡
シート再作成する部分と､ボタン作成する部分を別の関数にしたいと考えました｡
シート再作成は､使いまわしたいためです｡
そのため次のようにしたところ､オートメーションエラーが再発しました｡
Sub reMake()
Application.DisplayAlerts = False
Dim ws_Del As Worksheet
Set ws_Del = ActiveSheet
Sheets.Add
ws_Del.Delete
Set b1 = ActiveSheet.Buttons.Add(1, 0, 100, 10)
Set b2 = ActiveSheet.Buttons.Add(1, 10, 100, 10)
b1.OnAction = "reMake"
End Sub

次のようにしたら､発生しませんでした｡
Sub reMake()
Application.DisplayAlerts = False
Dim ws_Del As Worksheet
Set ws_Del = ActiveSheet
Sheets.Add
[a1] = 1
ws_Del.Delete
Set b1 = ActiveSheet.Buttons.Add(1, 0, 100, 10)
Set b2 = ActiveSheet.Buttons.Add(1, 10, 100, 10)
b1.OnAction = "reMake"
End Sub

完全に空のシートはシート数を数えるときに何かしら問題を起こすのかもしれません｡

ありがとうございました｡

[Youbun]

＞そのため次のようにしたところ､オートメーションエラーが再発しました｡
この行の下に書かれた関数のみを新規作成したブックに貼り付けて動かしてみてください。
特に問題なく動くと思います。
なので、エラーの原因は今回の質問のあったコードとは別のコードで発生していると思います。
原因不明のバグにつながるので暇なときにでも原因追及しておくと後で困らないと思いますよ。

[tokita.]

コメントまでご確認いただきありがとうございます。

「次のようにしたところ､オートメーションエラーが再発しました｡」のコードについて
・ブック新規作成
・VBEを開く
・標準モジュールを追加、該当コードをコピペ
・F5で1回実行
・できたボタンを2回クリック
という手順で、エラー再現している次第です。
仰る通り、(知的好奇心としても）原因追究はしたいのですが
これ以上の追求方法が思いつかない所です。

ボタンから3回実行したとき発生して、
VBEから実行したとき発生しないことから
ボタンから関数を呼ぶときとVBEから実行するときに何か処理の違いがある…？
等々、短いコードなのにかなり深い問題に接しているように考えています。

[Youbun]

私の環境では同じ操作をしてボタンを何回クリックしてもエラーが出ないので
原因も探れませんでした・・・
話は変わりますが、書いていたソースで注意しないといけないところがあったのでご報告致します。
Sub reMake()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ws_Del As Worksheet
Set ws_Del = ActiveSheet
Sheets.Add
ws_Del.Delete
Set b1 = ActiveSheet.Buttons.Add(1, 0, 100, 10)
Set b2 = ActiveSheet.Buttons.Add(1, 10, 100, 10)
b1.OnAction = "reMake"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

こんな感じで、「Application.DisplayAlerts 」などの表示フラグを止めた場合、
関数の最後で必ず元の値に戻すようにしないと
マクロ実行後も警告が出ないようになりますので注意してください。

ついでに、「Application.ScreenUpdating」こいつをfalseにすると
画面の更新が止まるのでマクロの途中で画面が更新されなくなるので余計な遅延がなくなって便利です。

[tokita.]

追加情報までいただきありがとうございます。
環境によって違って残念です。

取り敢えず目の前の問題としては解決したので、まだ何か爆弾を抱えているかもしれないことをコメントに残しつつ、使っていこうと思います。