☰

セルに文字列を出力するときにエラーが発生しました

tag-icon • tag-icon microsoft-excel worksheet-function vba string
セルに文字列を出力するときにエラーが発生しました

私はExcelスプレッドシートの一部を自動化するためにVBAマクロを使用しています。次の行でランタイムエラー1004#アプリケーションまたはオブジェクト定義エラーが発生し続けます

Sheets("Material").Last.Offset(0, 1).Value = "=SUMIF(MainTable[0],AG12,MainTable[Cost])"

ウォッチを使用すると、式の左側が適切なセルを参照していることがわかります。エラーは、セルに書き込まれる右側の文字列内にあると想定しています。おそらくテーブル参照に問題があるのではないかと考えています。残りの数式は文字列としてすべて正常に書き込まれ、テーブル参照は含まれていません。

答え1

ランタイム エラーが発生したときに、特定のセルに出力がなかったため、エラーはコード内にあると想定しました。実際に問題が発生するのは、数式をセルに書き込むと、「この数式に問題が見つかりました...」というポップアップ ウィンドウが表示される場合です。#NAME?、#VALUE!、#N/A タイプのエラーとは異なります。私の場合、コードは MainTable という名前のテーブルに範囲を設定し、列ヘッダーの 1 つが Effective Cost でした。コードは、MainTable[Cost] を参照する文字列として数式を設定しましたが、これは MainTable[Effective Cost] であるべきでした。

VBA は実行時エラーを生成し、サブは停止し、問題の文字列はセルに出力されません。次のコードはサブを停止します。(余分な "(" により、ポップアップ ウィンドウに数式エラーが発生します)

Range("A1").Value = "=Sum((B2:B4)"

次のコードは、セル A1 に #NAME? エラーが発生しますが、中断することなく動作します。

Range("A1").Value = "=Sum(PotatoSalad)"

また、文字列の配列を使用して簡単なテストも行いました。配列内の文字列に数式を格納すると、数式は更新されるまで評価されないため、コードが中断される問題を回避できるようです。この質問では、配列を使用して数式をセルにダンプすると何が起こるかについてさらに詳しく説明しています。簡単に言うと、バリアント配列を使用すると、数式が評価され、サブが停止します。https://stackoverflow.com/questions/19238844/strange-behavior-when-assigning-a-vba-array-to-formulas-of-an-excel-range

次のコードは、セル A1 が強制的に再計算されるまで機能します。

Dim MyArray(0 to 0) as String MyArray(0) = "=Sum((B2:B4)" Range("A1").Value = MyArray()

同じ配列をバリアントとして渡すと、ランタイム エラーが発生します。

関連情報