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

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

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

そこで今回は、

といった基礎的なことから、

といった応用的な方法まで、徹底的に解説します！

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

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

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

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

そのため、具体的になぜエラーが起こっているかわかりづらいエラーなんですよね。

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

のように、急にエラーが発生してしまうことも多いため、とても厄介なエラーです。

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

ここまで説明を聞くと

と思った方もいるのではないでしょうか。確かに、具体的な原因を見つけづらいエラーではありますが、対処方法はあります！

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

それぞれ詳しく解説しますね。

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

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

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

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

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

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

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

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

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

高速化対応を取り入れる

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

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

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日

まとめ

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

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