【ExcelVBA】オートメーションエラーが起こる原因・対処方法とは?

こんにちは、フリーランスエンジニア兼ライターのワキザカ サンシロウです。

皆さんは、VBAで「オートメーション エラー」というエラーを見たことがありますか?「オートメーション エラー」のエラーは、メッセージを見ただけではエラーの意味がよく分からないため、対処方法がわからず困る方も多いのではないでしょうか。

そこで今回は、

  • 「オートメーション エラー」のエラーとは?
  • 「オートメーション エラー」のエラーがおこる原因
  • といった基礎的なことから、

  • 「オートメーション エラー」のエラーが起こった時の対処方法
  • エラー処理を使ってデバッグ画面に移動しない方法
  • その他よくあるエラーの対処方法
  • といった応用的な方法まで、徹底的に解説します!

    目次

    オートメーションエラーとは

    まず、オートメーションエラーについて簡単に解説します。オートメーションエラーとは、発生する原因がとても多いエラーの一つです。

    オートメーションエラーのメッセージサンプル:

    たとえば、以下のような状況でエラーが起こるときがあります。

  • 大量のループ処理を実行してメモリが足りなくなったケース
  • Excelのセル数式が重たいデータで処理を動かしてしまった場合
  • 削除したブックを指定して、ブック操作をしてしまった場合
  • 削除したシートを指定して、シート操作をしてしまった場合
  • シート関数の処理に矛盾があるようなケース
  • Excel以外のアプリをたくさんたちあげているときにVBAの処理を実行してしまった場合
  • そのため、具体的になぜエラーが起こっているかわかりづらいエラーなんですよね。

    重たい処理を動かしたときにおこりやすいですが、同じ処理をもう一度動かしても同じエラーが起こらないケースがあるのが、このエラーの厄介なところです。

  • 原因を調べるために再度実行
  • 処理がエラーにならなかったため一旦放置
  • しばらくたってからエラー再発
  • のように、急にエラーが発生してしまうことも多いため、とても厄介なエラーです。

    オートメーションエラーの対処方法

    ここまで説明を聞くと

    じゃあいったいどうやって対処すればいいんだ・・・
    と思った方もいるのではないでしょうか。確かに、具体的な原因を見つけづらいエラーではありますが、対処方法はあります!

    今回のような「具体的な原因がわからない、いつ起こるかわからない」エラーの場合、以下のような対応がおすすすめです!

  • 繰り返し処理以外にできないか考えてみる
  • 高速化対応を取り入れる
  • 無駄なメモリを使わない方法にする
  • それぞれ詳しく解説しますね。

    繰り返し処理以外にできないか考えてみる

    まずは、「繰り返し処理以外にできないか考えてみる」についてです。

    処理を作っていると繰り返し処理でデータを取得・登録するケースが多いと思います。たとえば、データ一覧から重複削除するようなケースです。

    次のような一覧データからデータを削除するときに、次のように繰り返し処理で重複削除処理を作ってしまうケースがあります。

  • 1行目・2行目をチェックして重複していたら削除
  • 2行目・3行目をチェックして重複していたら削除
  • 3行目・4行目をチェックして重複していたら削除

  • n-1行目・n行目をチェックして重複していたら削除
  • 1行目・2行目を重複チェックするイメージ:

    2行目・3行目を重複チェックするイメージ:

    ただ、重複削除処理は、RemoveDuplicatesメソッドを使って1行で重複削除処理を書くこともできます。無駄な繰り返し処理をしていると、その分メモリを消費して処理も重くなってしまうため、オートメーションエラーが起こりやすくなってしまいます。

    そのため、「繰り返し処理を他のメソッドで置き換えることができないか?」と考えて処理を作っていくのがおすすめです!

    ちなみに、RemoveDuplicatesメソッドをつかった重複削除処理の作り方については以下で詳しく解説しているので、気になる方は見てみてくださいね!

    高速化対応を取り入れる

    次は、「高速化対応を取り入れる」についてです。繰り返し処理以外にも、処理が重たくなる原因はいくつかあります。

    たとえば、CSVやテキストファイルの読み込み処理を作るようなケースです。ファイルを読み込む方法は大きく分けて2つあります。

  • 1. Open・input・Closeステートメントで読み込む
  • 2. QueryTablesメソッドで読み込む
  • 1の方法はファイルを開いて1行ずつデータを読み込む方法で、2はExcelの外部データのインポート機能を使ってデータ全体をごそっと読み込む方法です。

    2の方法の方が圧倒的に処理が速く、1で10秒かかる場合でも2であれば1秒以内に処理が終わります。

    このように、同じ処理でも遅い書き方・速い書き方があります。今回解説したQueryTablesメソッドで読み込む方法も含め、]高速化対応については以下で詳しく解説しているので、ぜひ取り入れてみてくださいね!

    無駄なメモリを使わないようにする

    次は、「無駄なメモリを使わないようにする」についてです。Excelの数式で多いのですが、列や行を選択するときに列全体・行全体を数式に入れていることがありませんか?

    列全体・行全体を入れていれば、どこにデータがあっても計算結果が表示されるため、とても便利に見えて使っている人がとても多いです。

    ただ、セルに値が書き込まれると列全体・行全体をもう一度見て再計算してしまうため、数式が入っているセルが増えるごとに処理が重たくなってしまいます!そのため、再計算に無駄にメモリを消費してしまい、オートメーションエラーにつながりやすいです。

    データの範囲は可能な限り最小限にして作りこむのがおすすめです!

    数式でどうしても表現できない場合は、「データが増えたときに数式の範囲を書き換える処理」をVBAで組み込むのもおすすめです。

    大事なのは、「意味のない範囲を無駄に設定しすぎないこと」なので、範囲を絞れるものは絞って作るとオートメーションエラーが起こりづらくなるのでおすすめです。

    エラー処理を入れる方法とは

    今まで解説したような方法を取り入れても、処理の複雑度やパソコンのスペックによってオートメーションエラーが起こってしまうケースはよくあります。

    ただ、エラーが発生したときにVBEの画面に移動してしまうと、VBAを知らない人がツールを使っていた場合はびっくりしてしまいますよね。

    自分で使うだけであればそこまで気にしなくてもいいかもしれませんが、ツールを使うことに対しての不信感にもつながってしまうので、エラー処理を入れておくのがおすすめです!

    オートメーションエラーが確認できる、「ブックを開く → 開いたブックを閉じる → 閉じたブックをアクティブ化」の処理にエラー処理を入れたサンプルコードを用意しました。

    サンプルコード:

    'メインの処理
    Sub Main()
      
      'Testの実行結果を「resultMessage」に格納
      Dim resultMessage As String
      resultMessage = Test
      
      'Testの実行結果がエラーの場合はメッセージを表示して処理終了
      If resultMessage <> "" Then
        MsgBox resultMessage, vbCritical
        Exit Sub
      End If
      
      '処理の続きを以下に書いて行く
      
    End Sub
     
     
    Function Test() As String
    On Error GoTo Test_Err
      Test = ""
     
      'Test1.xlsxを開く
      Workbooks.Open ThisWorkbook.Path & "Test1.xlsx"
      
      'Test1.xlsxを変数に格納
      Dim wb1 As Workbook
      Set wb1 = Workbooks("Test1.xlsx")
      
      'Test1.xlsxを閉じる
      wb1.Close
      
      '閉じたブックをアクティブに指定 (ここでエラー発生)
      wb1.Activate
      
      Exit Function
     
    Test_Err:
      'エラー時にエラー情報を返す
      Test = "【処理エラー】" & vbCrLf & _
             "エラー番号:" & Err.Number & vbCrLf & _
             "エラーメッセージ:" & Err.Description
     
    End Function

    実行結果:

    エラーが起こるときのメッセージの内容はほとんど同じですが、VBEに移動せずにメッセージを表示することができます。

    ちょっとしたことで使い勝手が大きく変わるので、エラー処理を入れる癖をつけるのがおすすめです。エラー処理については以下でも詳しく解説しているので、気になる方は見てみてくださいね!

    補足:その他よくあるエラーの対処方法

    オートメーションエラー以外にも、起こりやすいエラーはたくさんあります

    なかでも、「型が一致しません」「オーバーフロー」「オブジェクトが必要です」のエラーはよくおこるため、対処方法を合わせて覚えておくのがおすすめです!

    以下で詳しく解説しているので、気になる方は見てみてくださいね!


    【VBA入門】「オーバーフロー」エラーが発生する原因・対処方法とは
    更新日:2024年5月6日

    【ExcelVBA入門】「オブジェクトが必要です」のエラー原因・対処方法とは
    更新日:2024年5月6日

    まとめ

    今回は、「オートメーション エラー」のエラーが起きる原因・対処方法について解説しました。このエラーは発生原因がわかりづらいエラーなので、発生しにくい作りにしていくのが重要です。

    エラーが起きてしまったときは、この記事を見つつ対応してみてくださいね!

    この記事を書いた人

    【プロフィール】
    DX認定取得事業者に選定されている株式会社SAMURAIのマーケティング・コミュニケーション部が運営。「質の高いIT教育を、すべての人に」をミッションに、IT・プログラミングを学び始めた初学者の方に向け記事を執筆。
    累計指導者数4万5,000名以上のプログラミングスクール「侍エンジニア」、累計登録者数1万8,000人以上のオンライン学習サービス「侍テラコヤ」で扱う教材開発のノウハウ、2013年の創業から運営で得た知見に基づき、記事の執筆だけでなく編集・監修も担当しています。
    【専門分野】
    IT/Web開発/AI・ロボット開発/インフラ開発/ゲーム開発/AI/Webデザイン

    目次