Excel

【ExcelVBA】数式設定FormulaとFormulaR1C1の違いと使い方

更新日：2023/12/01

ExcelVBAでセルに書式をセットするときは、FormulaまたはFormulaR1C1を使用します。
場合によってはFormula2またはFormula2R1C1を使用することもあります。
ここでは、これらのプロパティの違いと使い方についてお伝えします。

 

FormulaとFormulaR1C1の関係

FormulaとFormulaR1C1は、Rangeオブジェクトに含まれるセルに数式を設定または取得するプロパティです。
2つのプロパティは互いに連動していて、一方に入力すると他方に反映されます。

次のコードはFormulaプロパティに数式をセット後にFormulaプロパティとFormulaR1C1プロパティの値を表示して、同じ値であることを確認しています。

Sub formulaSample1()
    Dim rg As Range
    Set rg = Worksheets("Sheet1").Range("A1")
    
    ' Formula に数式をセット
    rg.Formula = "=UPPER(""hello!"")"
    
    Debug.Print "Formula: " & rg.Formula 
    Debug.Print "FormulaR1C1: " &rg.FormulaR1C1
End Sub

文字列中にダブルコーテーションを入力するときは、二つ重ねます。

結果は次のようになります。

Formula: =UPPER("hello!")
FormulaR1C1: =UPPER("hello!")

Formulaプロパティにセットした値が、FormulaR1C1プロパティにもセットされていることがわかります。

 

FormulaとFormulaR1C1の違い

FormulaプロパティとFormulaR1C1プロパティは数式にセルのアドレスを含むとき、アドレスの表現方法が異なります。

プロパティ形式形式の例
FormulaA1形式D3 … 相対参照
$D$3 … 絶対参照
FormulaR1C1R1C1形式R[2]C[3] … 相対参照
R3C4 … 絶対参照

A1形式は列方向(横)をアルファベットで、行方向(縦)を数値で表した形式です。
Excelでセルを選択したときに表示されるので、馴染みが深い形式です。


A1形式


R1C1形式の R は Row(行)を、C は Columun(列)を表します。
R1C1形式の絶対参照は、左上からの位置を数値で指定します。
相対参照は Rangeオブジェクトの位置を0として、相対位置（前方ならマイナス、後方ならプラスの値）を[ ]で囲みます。
相対位置が0のときは、省略できます。

FormulaプロパティにA1形式を含んだ数式をセットすると、R1C1形式に変換した数式がFormulaR1C1プロパティにセットされます。

Sub formulaSample2()
    Dim rg As Range
    
    Set rg = Worksheets("Sheet1").Range("A1")
    
    rg.Formula = "=D1 + E1"
    
    Debug.Print "Formula: " & rg.Formula
    Debug.Print "FormulaR1C1: " & rg.FormulaR1C1
End Sub

結果は次のようになります。

Formula: =D1 + E1
FormulaR1C1: =RC[3] + RC[4]

A1形式のD1がR1C1形式のRC[3]に、同様にE1がRC[4]に変換されています。

注：FormulaプロパティまたはFormulaR1C1プロパティに、対応していない形式のアドレスをセットすると、アドレスとして処理してくれません。

次のコードは、A1形式の数式をFormulaR1C1プロパティにセットしています。

Sub formulaSample3()
    Dim rg As Range
    
    Set rg = Worksheets("Sheet1").Range("A1")
    
    rg.FormulaR1C1 = "=D1 + E1"
    
    Debug.Print "Formula: " & rg.Formula
    Debug.Print "FormulaR1C1: " & rg.FormulaR1C1
End Sub

結果は次のようになります。

Formula: ='D1' + 'E1'
FormulaR1C1: =D1 + E1

シート上には #NAME? と表示されました。

D1をアドレスとして解釈できなかったため、D1という名前の関数等を探したけれど見つからなかったためエラーが表示されたのです。

 

他のRangeオブジェクトへの代入

アドレスが相対参照で数式を他のRangeオブジェクトへセットする場合、FormulaプロパティとFormulaR1C1プロパティのどちらを渡すかで参照先が変わります。

目的プロパティ
参照先セルを変更しないFormulaプロパティ
参照先セルを相対的に変更FormulaR1C1プロパティ

参照先セルを変更しない

次のコードは、Formulaプロパティを他のRangeオブジェクトへ代入しています。

Sub formulaSample4()
    Dim rg1 As Range, rg2 As Range
    Set rg1 = Worksheets("Sheet1").Range("A1")
    Set rg2 = Worksheets("Sheet1").Range("A2")
    
    rg1.Formula = "=C1"
    Debug.Print "(1)Formula: " & rg1.Formula
    Debug.Print "(1)FormulaR1C1: " & rg1.FormulaR1C1

    rg2.Formula = rg1.Formula
    Debug.Print "(2)Formula: " & rg2.Formula
    Debug.Print "(2)FormulaR1C1: " & rg2.FormulaR1C1

結果は次のようになります。

(1)Formula: =C1
(1)FormulaR1C1: =RC[2]
(2)Formula: =C1
(2)FormulaR1C1: =R[-1]C[2]

代入元と代入先は、共にC1セルを参照しています。
Formulaプロパティの値を元に、FormulaR1C1プロパティが調整された形ですね。

参照先セルを相対的に変更

次はFormulaR1C1プロパティを他のRangeオブジェクトへ代入しています。

Sub formulaSample5()
    Dim rg1 As Range, rg2 As Range
    Set rg1 = Worksheets("Sheet1").Range("A1")
    Set rg2 = Worksheets("Sheet1").Range("A2")
    
    rg1.Formula = "=C1"
    Debug.Print "(1)Formula: " & rg1.Formula
    Debug.Print "(1)FormulaR1C1: " & rg1.FormulaR1C1

    rg2.FormulaR1C1 = rg1.FormulaR1C1
    Debug.Print "(2)Formula: " & rg2.Formula
    Debug.Print "(2)FormulaR1C1: " & rg2.FormulaR1C1

結果は次のようになります。

(1)Formula: =C1
(1)FormulaR1C1: =RC[2]
(2)Formula: =C2
(2)FormulaR1C1: =RC[2]

代入先の参照先が、C1セルからC2セルに変りました。
FormulaR1C1プロパティはRangeオブジェクトからの相対アドレスなので、それを元にFormulaプロパティを計算すると参照位置が変わります。

 

数式の前に@マークが入るケース

FormulaプロパティまたはFormulaR1C1プロパティに数式をセットすると、@マークが自動で挿入されることがあります。

次のコードは、範囲内の値を並び替えるSORT関数を数式でセットしています。

Sub formulaSample6()
    Dim rg1 As Range
    
    Set rg1 = Worksheets("Sheet1").Range("A1")
    
    rg1.Formula = "=SORT(C1:C5)"
    
    Debug.Print "Formula: " & rg1.Formula
    Debug.Print "FormulaR1C1: " & rg1.FormulaR1C1
End Sub

結果は次のようになります。

Formula: =SORT(C1:C5)
FormulaR1C1: =SORT(RC[2]:R[4]C[2])

@マークが挿入されていませんね…

実はプロパティの値ではなくて、Excelの数式バーに表示される式についてでした。

数式バーに@マーク

よく見ると、ソートした結果も最小の値が一つ表示されているだけですね。

今回の問題の対応策は、Formula2プロパティまたはFormula2R1C1プロパティに数式をセットします。

Sub formulaSample7()
    Dim rg1 As Range
    
    Set rg1 = Worksheets("Sheet1").Range("A1")
    
    rg1.Formula2 = "=SORT(C1:C5)"
    
    Debug.Print "Formula: " & rg1.Formula
    Debug.Print "FormulaR1C1: " & rg1.FormulaR1C1
    Debug.Print "Formula2: " & rg1.Formula2
    Debug.Print "Formula2R1C1: " & rg1.Formula2R1C1
End Sub

結果は次のようになります。

Formula: =SORT(C1:C5)
FormulaR1C1: =SORT(RC[2]:R[4]C[2])
Formula2: =SORT(C1:C5)
Formula2R1C1: =SORT(RC[2]:R[4]C[2])

@マークが消えました。
Excelの数式バーにも表示されません。

Excelの数式バー

ソート結果も全て表示されています。

 

Formula2プロパティとFormula2R1C1

Formula2とFormula2R1C1は配列に対応したプロパティです。
配列を受け取ると、下方や右側のセルにも値を表示します。

前項のコードに、次のコードを追加してプロパティの内容を確認します。

    Debug.Print "Formula2: " & rg1.Formula2
    Debug.Print "Formula2R1C1: " & rg1.Formula2R1C1

結果は次の通り。

Formula2: =@SORT(C1:C5)
Formula2R1C1: =@SORT(RC[2]:R[4]C[2])

@マークが挿入されていますね。
実はFormula2の値が、数式バーに表示されています。

@マークは数式内で使用できる演算子です。
SORT関数は結果を配列で返します。
その配列に@演算子が適用されると、配列の最初の値が返ります。

数式に@演算子が挿入されている理由は、Formulaプロパティ経由でFormula2プロパティが変更されたからです。
Formula、FormulaR1C1、Formula2、Formula2R1C1は同じ内容となるように整合性がとられます。

FormulaプロパティとFormulaR1C1プロパティは単一の値を想定しています。
そのため、Formula2プロパティも整合を保つために、単一の値を返すように@演算子が追加されます。

 

今後はFormula2を使用

Formula2プロパティはFormulaプロパティの機能を拡張したものです。
バージョンが古いExcelではFormula2プロパティを使用できませんが、そうでなければFormula2プロパティまたはFormula2R1C1プロパティを使用した方が良さそうです。

なお、配列を単一の値として使用したいときは@演算子を使いましょう。

更新日：2023/12/01

書いた人（管理人）：けーちゃん

関連記事

スポンサーリンク

記事の内容について

null

こんにちはけーちゃんです。
説明するのって難しいですね。

「なんか言ってることおかしくない？」
たぶん、こんなご意見あると思います。

裏付けを取りながら記事を作成していますが、僕の勘違いだったり、そもそも情報源の内容が間違えていたりで、正確でないことが多いと思います。
そんなときは、ご意見もらえたら嬉しいです。

掲載コードについては事前に動作確認をしていますが、貼り付け後に体裁を整えるなどをした結果動作しないものになっていることがあります。
生暖かい視線でスルーするか、ご指摘ください。

ご意見、ご指摘はこちら。
https://note.affi-sapo-sv.com/info.php

 

このサイトは、リンクフリーです。大歓迎です。