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

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

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

そこで今回は、

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

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

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

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

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

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

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

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

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

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

    ここまで説明を聞くと

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

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

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

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

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

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

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

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

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

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

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

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

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

    【ExcelVBA入門】重複データを削除する方法を徹底解説!
    更新日 : 2019年5月31日

    高速化対応を取り入れる

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

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

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

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

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

    【ExcelVBA入門】処理の実行時間を高速化する方法を徹底解説!
    更新日 : 2019年8月28日

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

    次は、「無駄なメモリを使わないようにする」についてです。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入門】エラーを処理する方法(On Error、Resume、GoTo)
    更新日 : 2019年4月21日

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

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

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

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


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

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

    まとめ

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

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

    LINEで送る
    Pocket

    「プログラミング、右も左もわからない…」という方にオススメ

    当プログラミングスクール「侍エンジニア塾」では、これまで6000人以上のエンジニアを輩出してきました。

    その経験を通してプログラミング学習に成功する人は、「目的目標が明確でそれに合わせた学習プランがあること」「常に相談できる人がそばにいること」「自己解決能力が身につくこと」この3つが根付いている傾向を発見しました。

    侍エンジニア塾は上記3つの成功ポイントを満たすようなサービス設計に磨きをかけております。

    cta_under_bnr

    「自分のスタイルや目的に合わせて学習を進めたいな」とお考えの方は、ぜひチェックしてみてください。

    書いた人

    Sanshiro Wakizaka

    Sanshiro Wakizaka

    新卒入社したメーカー系のIT企業で、システムエンジニアとして約5年勤務。
    Webアプリ、業務アプリ開発において、要件定義、設計、インフラ、製造、テスト、運用保守など、様々な経験あり。
    また、侍ブログ編集部として、これまでに200記事以上の記事を執筆。
    現在はフリーランスエンジニア兼テックライターとして活動中。