プチIT化

【Excelマクロ】実行時エラー’1004’:数式が不完全です。の対処法

こんにちは、わたあめです。エクセルのマクロ(VBA)を実行していたら、こんなエラーが。

実行時エラー'1004'、見たことがある気がしませんか?私はよく出会いますが、必ず同じ原因ではなく、毎度原因は違ったりなんですよね。原因色々な1004エラー、わたあめの今回の経験談が少しでも解決のお役に立てればと思いまとめました。

それでは、いってみましょう!

事象:数式が不完全です。メッセージが出る

Excelマクロ実行時に、以下のエラーが発生しました。

数式が不完全です。対応する角かっこ(])があることを確認ください。

これまで、マクロは順調に問題なく動いていていました。エラー発生前にマクロを編集・更新したりは全くしていないのです。

わたあめ
わたあめ
今日もうまく動くと思っていたマクロが止まって、動揺!なんだろう??

原因:参照している値が存在しなかった

対処方法は次章にまとめていますが、まずは原因から。ズバリ:代入しようとしている値が存在しなかった。具体的には、ピボットテーブルの値を参照し、セルに代入しようとしていましたが、その値が存在しなかったということです。

ちょっと抽象的なので、具体的に例を使って説明します。例えば、以下のようなピボットテーブルがあったとします。

そして、以下が3月の個数の値をA1に入れるVBAなのですが、このエラーが出ます。

Worksheets("Sheet1").Range("A1") = Worksheets("Sheet2").PivotTables("ピボットテーブル").GetData("3月 個数")

1月と2月のデータは値が存在するので、エラーは出ないのですが、3月のデータは存在しないので、エラーとなります。数式が不完全=数式に誤りがある、というエラーのようです。

実行時エラー'1004'について

1004エラーは、VBAマクロの実行に失敗したというエラーです。(って、そのまんまじゃないか!(笑))同じエラー番号でも、内容は多岐にわたるようなのです。「実行時エラー'1004'」の後に具体的な内容が記載されているので、そのメッセージを手掛かりに原因にあたりをつけていくと良いです。ちなみに、1004エラーのメッセージは他にもこんなものが。

  • データが選択されていません。
  • アプリケーションまたはオブジェクト定義のエラーです。

今回は、「数式が不完全です。」というエラーだったので、そのメッセージを手掛かりに、調べていきました。実際の調べ方(対処)は次章にまとめていきます。

対処:デバッグして該当行を読み解く

エラーメッセージが出てきたときに、メッセージ下にボタン[デバッグ(D)]があります。

そのボタンをクリックすると、問題のある行が黄色になります。黄色の行がエラーが発生した場所という訳です。こんな感じ。(シート名やセルやらが適当なのはごめんなさい。)

その該当行を読み解いていきました。

  • ピボットテーブルで存在しないデータフィールドを参照していないか?
  • アクティブシートでオブジェクトを指定していて、想定のシートではないシートで、データを参照していないか?

など、Excelの式としてエラーが起きそうな原因が含まれていないか?を確認していきました。

わたあめ
わたあめ
これはマクロの内容や環境によって異なるので、人(マクロ?)それぞれかと思います。

今回のわたあめのエラーケースの詳細

より具体的にわたあめのマクロを例にとって、今回のエラーケースを解説します。おさらいになりますが、今回私のエラー原因は、ピボットテーブルを参照するとき(PivotTableのGetDataメソッド)、存在しないデータフィールドを参照していたことです。

マクロでの具体的な処理は、月ごとの売上をまとめたピボットテーブルがあり、1月~12月までを順に、ピボットテーブルに値が存在するかFind関数で確認していました。データが存在すれば、そのデータを参照し貼り付け、存在しなければ何もしないという処理にしていました。

売上データは、先のデータは存在しないことがあり、例えば現在11月の時点では来年の1~3月の売上はまだ存在しないといった状況です。そんな状況で、"1月"をFindしたときに、1月のデータは無いはずだけど、"11月"に"1月"という文字列も含まれているので、1月のデータもある前提で処理してしまった…"ない"のに"ある"ということになり、処理に失敗(参照する値が無かった)という。

Find関数の判定が想定外だった

データが無いはずなのに、一致してしまっているのは、Find関数の引数で完全一致を指定していなかったからでした。(指定しないとデフォルトが完全一致ではなかったため、11月の中に"1月"という文字列が含まれていると判定されてしまったわけです。)

完全一致は完全に文字列が一緒だった場合一致、部分一致は文字列が含まれていれば一致するという判定方法です。

Find関数の引数設定

Find関数は引数で検索方法を指定できるので、指定しておいた方が、想定外な事態が起こりにくいです。(自戒)

Find("キーワード", LookAt:=定数)

定数のところには、以下を指定できます。

  • xlWhole:完全一致
  • xlPart:部分一致

今回は、完全一致(xlWhole)を指定することで、一致判定を間違えることなく無事に解決しました。パチパチパチ。

さいごに

ここまで読んでくださってありがとうございます。

エラーについて検索を書けると一般的な原因などは書いてあるものの、実際の例に基づいた解説がなかったので、今回は詳しく書いてみました。くどいかな~脱線してるな~と思いつつ書き上げました。

ということで、ここまで読んでくださった方が、お付き合いいただきありがとうございます!!それでは、また。

flier(フライヤー)