セルに数式を入力/取得する(Formula/FormulaR1C1プロパティ)｜Excel VBA

ツイート

セルに数式を入力、または入力されている数式を取得するプロパティです。
対象がセル範囲の時には取得される結果は2次元の配列になります。

構文  Object.Formula       A1形式で数式の設定/取得
      Object.FormulaR1C1   R1C1形式で数式の設定/取得

数式を設定するときは「=」から記述し、数式全体を「"」で囲みます。
「=」を忘れると文字列とみなされてしまうので、注意してください。

■A1形式とR1C1形式

セルを参照する形式には、A1形式とR1C1形式があります。
A1形式は「A1」や「C5」のように、列をアルファベット、行を行番号で表す形式です。
Excelの既定ではA1形式になっているのでおなじみの形式でしょう。
絶対参照にする場合は列、行、または両方に「$」をつけます。
たとえば、セルA1の場合「$A1（列のみ絶対参照）」「A$1（行のみ絶対参照）」「$A$1（行・列ともに絶対参照）」のように記述します。
Formulaプロパティで数式を取得・設定するときは、A1形式を使用します。

一方、R1C1形式は行（Row）をRと行番号、列（Column）をCと列番号で表す形式です。
たとえば、「R1C2」はセルB1、「R3C4」はセルD3を絶対参照で表します。
相対参照にする場合は、基準とするセルから見た相対的な位置を R[数値]C[数値] のように指定します。

基準セルから見て右と下は正の数、左と上は負の数を指定します。
Offsetプロパティで相対的な位置を指定するのと同じような考え方ですね。
FormulaR1C1プロパティで数式を取得・設定するとき、R1C1形式を使用します。

次のサンプル1では、(1)〜(4)はいずれもセルC1にセルA1とB1を足す数式を入力します。

●サンプル1●

Sub Sample1()
     Range("C1").Formula = "=A1+B1"             '----(1)A1形式で相対参照
'    Range("C1").Formula = "=$A$1+$B$1"         '----(2)A1形式絶対参照
'    Range("C1").FormulaR1C1 = "=R1C1+R1C2"     '----(3)R1C1形式で絶対参照
'    Range("C1").FormulaR1C1 = "=RC[-2]+RC[-1]" '----(4)R1C1形式で相対参照

    MsgBox "A1形式：" & Range("C1").Formula & vbCrLf & _
           "R1C1形式：" & Range("C1").FormulaR1C1
End Sub

セルに入力された数式は、(1)と(4)が相対参照、(2)と(3)が絶対参照で同じものになります。
メッセージボックスには、入力された数式を両方の形式で表示します。
(1)〜(4)のコードを1つずつ有効にして、設定された数式の違いを確認してください。

■FormulaR1C1プロパティが有効なケース

サンプル1の数式では、可読性を考えるとFormulaR1C1プロパティを使用する意味はあまりありません。(1)と(4)では(1)の方がだんぜん見やすいですね。

FormulaR1C1プロパティは、プログラムの中で取得した行番号と列番号によって、数式で参照するセル範囲を特定できるような場合に便利です。特に、数式を相対参照で設定できるため、複数のセル範囲に数式を一度に設定するときに効果的です。


では、データの右端に左端までの値を合計する数式を入力する場合、それぞれどのような記述になるでしょうか。なお、データはセルA1から複数行・複数列に入力されており、範囲は可変とします。

一例に過ぎませんが、Formulaプロパティを使用した場合はサンプル2のようになります。

●サンプル2●

Sub Sample2()
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim i As Long

    '----最終行
    myLastRow = Range("A1").End(xlDown).Row
    '----最終列
    myLastCol = Range("A1").End(xlToRight).Column
    
    For i = 1 To myLastRow
       Cells(i, myLastCol + 1).Formula = _
            "=SUM(A" & i & ":" & _
             Cells(i, myLastCol).Address(False, False) & ")"
    Next i
End Sub

せっかく取得した最終行が、数式の中の参照範囲の記述に活かされていません。
では、FormulaR1C1プロパティを使用する例を見てみましょう。

●サンプル3●

Sub Sample3()
    Dim myLastRow As Long
    Dim myLastCol As Long
    Dim i As Long

    '----最終行
    myLastRow = Range("A1").End(xlDown).Row
    '----最終列
    myLastCol = Range("A1").End(xlToRight).Column
    
    Cells(1, myLastCol + 1).Resize(myLastRow).FormulaR1C1 = _
             "=SUM(RC[" & -myLastCol & "]:RC[-1])"
End Sub

サンプル2がループの中でセルごとに数式を編集し、1つずつセルに設定しているのに対し、 サンプル3は対象のセル範囲に同じ数式を一度に設定することができます。

●注意●

セルに入力されているのが数式以外の場合には、Valueプロパティと同じ結果を返します。