即効テクニック |
配列数式をうっかりEnterキーで確定してしまうミスをしたことはありませんか? セルの数式が配列数式として入力されているかどうかを判定するマクロを考えてみましょう。 配列数式とは、セル内の数式で参照範囲を配列として扱うような数式です。 たとえば、セル範囲A1:A6に 田中 鈴木 山田 : のような名前が入力されていて、セル範囲B1:B6に数値が入力されていたとします。 A列が"田中"の数値だけを合計するとき、配列数式では次のようにします。 =SUM(IF(A1:A6="田中",B1:B6,0)) セルに式を入力したら、CtrlキーとShiftキーを押しながらEnterキーを押します。 これが、配列数式を入力するときの決まりです。 配列数式として入力されると、数式バーに表示される数式が {=SUM(IF(A1:A6="田中",B1:B6,0))} のように、{ }で囲まれます。 この{ }は、実際に入力されている文字ではなく、配列数式であることを示す印です。 まず、セルに数式が入力されているかどうかを判定しましょう。 これは簡単です。RangeオブジェクトのHasFormulaプロパティを使います。 次のコードは、選択したセル範囲内で、数式が入力されているセルのアドレスをイミディエイトウィンドウに出力します。Sub Sample1() Dim c As Range For Each c In Selection If c.HasFormula Then Debug.Print c.Address End If Next c End SubHasFormulaプロパティは、セルに数式が入力されているとTrueを返します。 数式かどうかは、データの先頭が「=」で始まっているかを判定しています。 配列数式であっても、普通の数式であっても、HasFormulaプロパティの結果には違いがありません。 数式が、配列数式として入力されているかどうかは、RangeオブジェクトのHasArrayプロパティを使います。Sub Sample2() Dim c As Range For Each c In Selection If c.HasArray Then Debug.Print c.Address End If Next c End Sub本来、配列数式として入力されていなければいけない数式が、正しく配列数式として入力されているかどうかは、次のように判定できます。 次のコードは、選択したセル範囲内で、配列数式として入力されていないセルを赤色で塗りつぶします。Sub Sample3() Dim c As Range For Each c In Selection If c.HasFormula And Not c.HasArray Then c.Interior.ColorIndex = 3 End If Next c End Subでは配列数式として入力されていない場合は、自動的に配列数式として入力し直すマクロを作成しましょう。 セルに入力されている数式は、Formulaプロパティで操作できます。 ただ数式を入力するだけでしたらRange("C1").Value = "=SUM(IF(A1:A6=""田中"",B1:B6,0))"のように、Valueプロパティに対して設定することも可能ですが、セルに入力されている数式を取得するときは、Formulaプロパティを使います。 しかし、Formulaプロパティに設定した数式は、配列数式になりません。 数式を、配列数式として入力するには、FormulaArrayプロパティを使います。 FormulaArrayプロパティは、ヘルプに 指定したセル範囲に配列数式が含まれていないときは Null 値を返します。 と書かれていますが、配列数式ではない一般の数式が入力されている場合にはNull値ではなく、その数式が返ります。 FormulaArrayプロパティに数式を設定すれば、その数式は配列数式として入力されるのですが、ひとつ問題があります。 このプロパティを使って、配列数式として入力するときは、 数式には R1C1 形式を使う必要があります。A1 形式は使えません。 だそうです。試しにA1形式でやってみましょう。Sub Sample4() ActiveCell.FormulaArray = "=SUM(IF(A1:A6=""田中"",B1:B6,0))" End Sub配列数式として入力できました。 ヘルプの間違いには、今さら驚きません。 もしかして、バージョンによって違うのかと確認してみましたが、 Excel 97/2000/2002/2003/2007/2010 で、A1形式でも配列数式として入力できました。 ユーザーが選択したセル範囲に対して、数式が入力されているセルで、その数式が配列数式として入力されていなかったとき、配列数式として入力し直すマクロは次のようになります。Sub Sample5() Dim c As Range For Each c In Selection If c.HasFormula And Not c.HasArray Then c.FormulaArray = c.Formula End If Next c End Sub