テーブル機能は範囲を指定することが容易なので、シートの最下行や指定の列を必死に探していたころより遥かにデータの挿入が楽になりました
なので、最近よく使用する機能なのですが、つい先日ハマってしまったエラーに関する内容を記事にしておきますので、同じような処理を検討している場合は注意してください
結果から言いますと、テーブル内にテーブルの列名等を使用した数式を一括出力するとエラーが発生します
これはテーブルの列名等の数式は見た目は全く同じ数式になるのですが、その内部にはテーブルの指定セルが含まれているようです
そのため、テーブルに一括出力する際にまだ行作成が行われる前にその参照をしてしまいセルの未存在の実行時エラーが発生します
この画像の動きを確認してください
この処理では選択範囲のセルデータを配列に代入させて、その配列データをテーブルに一括出力している動きです
配列データの3列目は、1列目と2列目の値の一致判定を行う数式が入力されています
画像を確認してもらえばわかるように、出力する数式文字列は全て全く同じです
しかし、上述のようにこの数式が入力されて再計算が行われる方がテーブルの行作成より先に行われています
そのため、出力時に「実行時エラー ‘1004’:アプリケーション定義またはオブジェクト定義のエラーです。」が発生します
画像の出力後のデータを確認してもらうと分かるように2行目の数式セルまでは入力が行われています
2行目の数式セルを入力しようとしてエラーになっていることが、この結果からもわかります
さらにこのエラーの発生後に、なぞの現象として1行目のデータが無いことになってしまっています
正常に出力されているはずの1行目が、なぜか存在しないことになって削除が実行できなくなっています
もちろんデータが無いことになっているので、この状態でテーブルのオブジェクトのListObject.DataBodyRangeプロパティでデータを削除しようとするとエラーが発生します
データが無いため、DataBodyRangeプロパティが存在しないためです
この場合は、このプロパティを使用せずに処理を組んでおくか、もしくは手動操作にて行の挿入を一旦行ってください
すると空白行が1行目に挿入されて表示されているデータは消滅します
とはいえこのエラー対策を行っておけば、ここは必要ありません
解決策として
この場合の解決策は非常に単純で、行を作成してから数式を出力するようにすればいいだけになります
この場合の方法としては2つあると思います
ListRowsオブジェクトのAddメソッドで必要行数の空白行を作成してから配列を出力する方法と、そもそも数式を配列に含めず後で入力する方法です
テーブルの既定の動作として、数式を1行目に入力すると列全体に反映されます
つまり1セルだけ数式を入力すれば良いだけなのです
なので、後者の方法の方が圧倒的に簡単です
前者の方法はAddメソッドがあまり早くないので、どうしても必要な場合に選択してください
今度は配列に代入させる選択範囲は2列目までの範囲としています
その範囲の配列データをテーブルに一括出力します
その後3列目のデータ行1行目のセルに数式を入力しています
ただこれだけで3列目は数式が列全体に反映されます
このエラーはかなり苦戦しました
なんせ、出力先のテーブルの範囲も正常にAddressプロパティで取得できるし、配列データもテーブル以外の範囲に出力させると問題なく出力されます
配列データもそれなりの列数があったので、細かく1列ごとに確認をしていない状態でとにかく出力先のセル指定が間違っているのだろうとばかり思っていました
何よりこの処理を作成する直前に、全く同じ処理の流れで別の配列データを出力させていてそちらは全く問題なく、コードの流れも同じだったのも混乱を招きました
もちろん直前の配列には数式は含まれていませんでした
原因が分かってしまえば、対策にしてもどうと言うことは無いのですが、Web検索でエラー内容の検索をしてもセルの指定間違いという内容が多かったので自分で気づいたのは翌日のことでした
数式の計算が行の作成より先というのは盲点でした
ちなみにApplication.Calculationで計算を手動にしてもエラーを回避できませんので悪しからず～