えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

トップ > Excel > VBAの実行時エラー「無効な前方参照か、コンパイルされていない種類への参照です。」の原因と研究記録

VBAの実行時エラー「無効な前方参照か、コンパイルされていない種類への参照です。」の原因と研究記録

Excel VBA

2023年12月から2024年1月にかけて、Excel VBAで実行時エラー「オートメーションエラーです。無効な前方参照か、コンパイルされていない種類への参照です。」が多発しているようです。

更に関係あるか不明ですがVBAプロジェクトが完全に読めなくなるという事象も起きているようです。※末尾関連ツイート参照

以前Twitterに書いた内容ですが、最近閲覧数が急激に増えているためブログにもまとめておきます。

--------------------
実行時エラー '-2147319767 (80028029)':

オートメーション エラーです。
無効な前方参照か、コンパイルされていない種類への参照です。 
--------------------

追記情報

2024/2/13のアップデート バージョン 2401 ビルド 17231.20236 に関係していそうな文言が見つかったため、Excelを最新版に更新することで解消される可能性があります。

Excel ファイルを保存するときにマクロが破損する問題を修正しました。

解決できた方法

今のところ、2つの方法で解決を確認しています。

  1. プロジェクトの完全な再コンパイルを行う
  2. シートオブジェクトをWorksheet型の変数に格納してからメンバを参照する

2022/1/20に目撃したときは「方法1」で解決しましたが、2023/12/5に発生した際は「方法2」を実施しないと解決できませんでした。

また、「方法1」は対症療法であるため再発する可能性があります。これから対応する場合は「方法2」から試すことを推奨します。

追記：レガシーな「ファイルを開く」（Ctrl+F12）から「開いて修復する」で開くことによって解消できるという情報をいただきました。後に仮説部分に書いていますがシートの破損が最も疑わしいため、これがもっとも根本的な解決策になるかと思います。実際に解決できるかどうかは現在検証中です。

シートオブジェクトをWorksheet型の変数に格納してからメンバを参照する

発生しているソースコードを拝見すると、いずれも Sheets や Worksheets を使用している行で実行時エラーが起きているようです。

このようなケースにおいては、Worksheet型の変数に格納してから、その変数にドットをつけてメンバを記述することで解決できます。

例えば次のようなソースコードの場合

Sheets("一覧表").Select

こうしたら動きます。

Dim ws As Worksheet
set ws = Sheets("一覧表")
ws.Select

また、次のようなコードの場合

dim Sheet1Name As String
Sheet1Name = Worksheets(1).Name

このようにしたら動きます。

Dim ws As Worksheet
Dim Sheet1Name As String
Set ws = Worksheets(1)
Sheet1Name = ws.Name

更に次のようなコードの場合

Sub SheetWrite( objSheet As Object )
   objSheet.Select
End Sub

このようにします。

Sub SheetWrite( objSheet As Object )
   Dim ws As Worksheet
    set ws = objSheet
   ws.Select
End Sub

なお、「毎回変数を作るのが面倒だ」「エレガントに書きたい」という方は、キャスト関数を作る方法もあります。

Sub Test()
    Dim Sheet1Name As String
    
    Sheet1Name = CastWorksheet(Worksheets(1)).Name
    CastWorksheet(Sheets(1)).Select
End Sub

Function CastWorksheet(objSheet As Object) As Worksheet
    Set CastWorksheet = objSheet
End Function

ハマることが多い罠の一つですが、Sheets()やWorksheets()の戻り値はSheet型やWorksheet型ではありません。Object型です。

TypeNameで調べるとWorksheetが返ってきますが、ウォッチウィンドウで確認すると Object/Sheet1 となっており

オブジェクトブラウザウィンドウで Worksheetsのデフォルトプロパティである Item を確認すると As Object と記載されています。

余談ですが、SheetsやWorksheetsのあとにドットを入力しても、インテリセンスによる入力候補が表示されない原因も戻り値がObject型だからです。

なぜドットの左をWorksheet型変数にすると解決できるのか

これを説明するには、オブジェクト指向型プログラミング言語の知識が必要となるため難しい説明になり、しかも仮説でしかないためここではふわっとした感覚的な説明にとどめます。

問題を解決したいだけの人は理解する必要はありません。

Object型にはWorksheet型の他にもRange型やDictionary型やユーザー定義クラス型など、あらゆるオブジェクトが代入できます。

このようなデータ型を抽象型と言いますが、抽象型の変数にドットを付けてメンバへアクセスしたとき、Object型変数には該当するメンバがいないためデータ型の階層を潜り込んでメンバがいないか探して実行します。

潜った階層のデータ型にもメンバがいない場合は、更に潜ったデータ型にメンバがいないか探して実行します。

それぞれの階層の同名のメンバには、同じ目的のためのプログラムですが書いてあることに若干の違いがあります。

これが今回の「Worksheet型変数に代入してからアクセスすると動作する」で解決策できる理由と考えられます。

プロジェクトの完全な再コンパイルを行う

ここで言う「完全な再コンパイル」とは、「全てのモジュールをエクスポートして削除した後インポートする」という手順のことを指しています。※他の操作方法もあります

モジュールのエクスポートは、モジュール名を右クリックすることでもできますが、フォルダにドラッグ＆ドロップした方が簡単です。

インポートも同様にフォルダ上のbasファイルをプロジェクトエクスプローラにドラッグ＆ドロップです。しかもインポートは複数のファイルを一括で取り込むことができます。

同名のモジュールは上書きできないため、インポートする前に削除が必要です。エクスポートと削除は1件づつ行う必要があります。

なお、運が良ければモジュールを1つだけの実施で解消できます。（モジュール内のソースコード次第）

VBAプロジェクトが破損して読み取れなくなるという別の不具合も発生しているようなので、この機会に全てのモジュールをエクスポートしておくほうが良いかもしれません。この記事の操作でプロジェクトが破損しても筆者は責任がとれません。

なぜ「プロジェクトの完全な再コンパイル」で解決できるのか

「ソースコードに間違いがないのに何故かエラーが出る。何度実行しても再発する」という現象は、多くの場合は「中間コード」というVBAソースコードのコンパイル時に生成される情報が影響しています。

中間コードはワークシートやソースコードと合わせてブックに保存されている情報で、たとえExcelを再起動したとしても再利用されます。

コンパイル＆実行時は中間コードの様々なメタ情報を生成していると考えられますが、ここではそれら全てをまとめて中間コードと呼びます。

通常、中間コードは細かく分けて記憶されており、ソースコードの一部が改変されたとき、その影響範囲の一部だけが再構築されます。

（目安はプロジェクト単位、モジュール単位、プロシージャ単位くらいの規模）

今回のエラーでは原因がエラーの発生行にあるとしか考えにくいため、その周辺だけにいくら変更を加えても、該当するプロシージャの中間コードしか更新されません。

もし、何らかの不具合により中間コード全体で根本的な矛盾が発生しているとしたら、いくらプロシージャだけ作り直しても意味がありません。

そこで、モジュールの入れ替えによりプロジェクト全体の中間コードの消去を促し、改めて実行したときにソースコード全体の再コンパイルさせるという手順を踏んでいます。

この他にも中間コードを削除する方法はいくつかありますが、もっとも簡単で確実なのが、先で説明したモジュールを読み書きする操作になります。

例えば、全ソースコードのコメントアウト＆コンパイル＆コメントインは簡単かつ強力ですが、たまに効果がないときがあります。

エラー発生の傾向について

Excelのbit数が関係ある？

Excel (Office) には32bit版と64bit版がありますが、64bit版の環境で発生する傾向があるように感じています。

（※Windows OSのビット数とExcelのビット数が一致するとは限りません。64bit OSには32bit版Excelがインストールされていることもあります）

あるいは、異なるbit数の環境を行き来することによって、「片方のbit数の環境では動くが、反対のbit数の環境では動かない」という状況に陥っているのではないかと考えています。

このあたりは情報を持っていないので、コメント等で教えていただけると助かります。

ソースコードを何も変更していないのに突然発生する？

今まで使えていたのに、ある日から突然動かなくなったという声もあるようです。

いわゆる「何もしていないのに壊れた」という状態です。

Excelの日々のアップデートの影響も関係あるかもしれません。

特定のワークシートだけで発生する？

問題が発生しているブックでも、全てのシートでこの問題が発生するわけではないようです。

つまりシートオブジェクトに何らかの問題が起きているのかもしれません。

同時期に発生した次の現象においては、64bitだけでVBAプロジェクト全体が破損しているということから、本件と関係あるか分かりませんがシートオブジェクトのVBA領域に何らかの不具合が紛れ込んでいる。原因は同じではないかと疑っています。

追記：こちらの破損は「開いて修復する」で解決できることを確認しました。

関連ツイート

別件の関連ツイート

https://x.com/VBA07529852/status/1743541195551485961?s=20