スキルアップ・問題解決なら、モーグにおまかせ！

配列数式をうっかり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 Sub　　　

HasFormulaプロパティは、セルに数式が入力されていると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