Office TANAKA - Excel VBA Tips[ワークシート関数を入力する]

セルにワークシート関数を入力する操作をマクロ記録してみましょう。

たとえば、下図のようなワークシートで、セルA4にSUM関数を入力してみます。

セルA4にアクティブセルを置いてマクロ記録を開始し、Alt+Shift+「=」キーでSUM関数を入力後、Ctrl+Enterで確定しました。セルA4には「=SUM(A1:A3)」という関数が入力され、この操作は次のように記録されました(コメント部分を除く)。

Sub Macro1()
    Selection.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
End Sub

記録されたFormulaR1C1プロパティは、数式をR1C1形式で設定・取得するプロパティです。設定された数式は「=SUM(R[-3]C:R[-1]C)」と、確かにR1C1形式になっています。しかし、実際には、セルA4に入力された関数は「=SUM(A1:A3)」です。

このように、関数を入力する操作をマクロ記録すると、Excelの設定が「A1形式」であってもR1C1形式で記録されます。このことから、マクロで関数を代入するときは、R1C1形式でなければならない…と誤解している人がけっこういるようです。そんなことはないんですよ。たとえば上記のSUM関数は、次のようなマクロで代入できます。

Sub Sample1()
    Selection.Formula = "=SUM(A1:A3)"
End Sub

あなたが、R1C1形式を好むユーザーで、そのマクロを使うユーザーもA1形式よりR1C1形式が肌に合っているのでしたら、何も問題はありません。決してR1C1形式を否定する気持ちはありません。しかし、現在主流であるA1形式で普段からExcelを使っているのでしたら、この部分だけをR1C1形式にするのはどうでしょう。わかりにくくないですか？

ちなみに、上図のようなケースでセルA4にSUM関数を代入するとき、計算の範囲であるSUM関数の引数を自動取得するには、たとえば次のようにします。

Sub Sample2()
    With Range("A1")
        .End(xlDown).Offset(1, 0).Formula = _
            "=SUM(" & .CurrentRegion.Address(0, 0) & ")"
    End With
End Sub

まぁ、いろんな手があります。要するに、発想しだいだということですね。