VBA セルに数式を入力する方法 Formula

今回はマクロを利用して、Excelの数式を入力する方法を紹介してきます。
セルの数式を入力するには、「Formula」プロパティを利用しましょう。

セルに数式を入力 Formula

Formulaの使い方

セルに計算式を入力するには、下記のようにFormulaを利用します。

任意のセル.Formula = "=数式"

実際に例を見てみましょう。

Private Sub btnFormula_Click()

    'Cellsを利用した方法
    Cells(1, 1).Value = 10
    Cells(1, 2).Value = 20
    Cells(1, 3).Formula = "=A1+B1"

End Sub

解説
4-5行目:「セルA1」に10、「セルB1」に20の値を入力
6行目:「セルC1」にA1+B1の数式を入力

結果

結果を見ると、C1に数式(=A1+B1)が入力されていることが確認できました。

Rangeを利用した方法

また、Range("セルの場所").Formulaという書き方もできます。

Private Sub btnFormula_Click()

    'Rangeを利用した方法
    Cells(2, 1).Value = 50
    Cells(2, 2).Value = 100
    Range("C2").Formula = "=A2+B2"

End Sub

結果

RangeでもCells.Formulaと同じような内容になりました。

関数の入力

EXCELの数式には「SUM」や「IF」などの関数が用意されています。
Formulaを使うことで、これらの関数も同じように利用することができます。

Private Sub btnFormula_Click()

    '関数の入力
    Cells(3, 1).Value = 100
    Cells(3, 2).Value = 200
    Range("C3").Formula = "=SUM(A3:B3)"

End Sub

解説
6行目:Formulaの式にSUM関数を入力しています。

結果

セルC3に数式「=SUM(A3:B3)」が入力されていることが確認できました。
Formulaを利用することで、EXCELの関数を入力することもできるので、ぜひ利用しましょう。

 

文字列の入力

続いて文字列の入力を試してみましょう。
文字列を結合する数式を入力してみましょう。

Private Sub btnFormula_Click()

    '文字列の場合
    Cells(4, 1).Value = "ひろ"
    Cells(4, 2).Value = "にも"
    Cells(4, 3).Formula = "=A4 & B4"

End Sub

結果

文字列も問題なく数式を作成することができました。

 

ただし、文字を直接数式に埋め込む場合は注意が必要です。
EXCELの数式で文字列を扱うときは文字列をダブルコーテーション(")でくくる必要があります。
なので下記のような記載方法はNGとなります。

Cells(5, 3).Formula = "=ひろ & にも"          'NG

では続いて、下記のようにダブルコーテーションを付けてみました。

Cells(5, 3).Formula = "="ひろ" & "にも""      'NG

しかし下記画像のようなエラーが発生してしまいます。

数式でダブルコーテーションを利用したい場合は、ダブルコーテーションを2つ続けて記載しましょう。

Cells(5, 3).Formula = "=""ひろ"" & ""にも"""  'OK

このように記載することで正しく数式を入力することができます。

数式の部分にダブルコーテーションが入力されていますね。

 

Valueでもできる

今までは、Formulaプロパティを利用して処理を記載してきましたが、実はValueを利用して可能になります。
実際に例を見てみましょう。

Private Sub btnFormula_Click()

    'Cellsを利用した方法
    Cells(1, 1).Value = 10
    Cells(1, 2).Value = 20
    Cells(1, 3).Value = "=A1+B1"

    'Rangeを利用した方法
    Cells(2, 1).Value = 50
    Cells(2, 2).Value = 100
    Range("C2").Value = "=A2+B2"

    '数式の入力
    Cells(3, 1).Value = 100
    Cells(3, 2).Value = 200
    Range("C3").Value = "=SUM(A3:B3)"

    '文字列の場合
    Cells(4, 1).Value = "ひろ"
    Cells(4, 2).Value = "にも"
    Cells(4, 3).Value = "=A4 & B4"

    '文字列を直接式に入力する方法
    Cells(5, 3).Value = "=""ひろ"" & ""にも"""
    
End Sub

結果

FormulaでもValueでも同じ結果になりました。
これは、Valueの値の先頭に"イコール="が付いている時は、VBAが自動で判定して、Formulaプロパティに値を格納してくれるからです。
なので数式を入力する時は、正しいのはFormulaプロパティに値を格納することなので、初めからFormulaプロパティを利用することをお勧めします。

 

ValueとFormulaの値取得の違い

続いて数式が入力されているセルの値を取得してみましょう。
Valueで取得する場合とFormulaで取得する場合の違いを確認していきます。

Private Sub btnFormula_Click()

    'Cellsを利用した方法
    '数式の入力
    Cells(1, 1).Value = 100
    Cells(1, 2).Value = 200
    Range("C1").Value = "=SUM(A1:B1)"

    'Valueの値取得
    Dim str_value As String
    str_value = Range("C1").Value
    result = MsgBox(str_value, vbOKOnly, "Valueの値")

    'Formulaの値取得
    Dim str_formula As String
    str_formula = Range("C1").Formula
    result = MsgBox(str_formula, vbOKOnly, "Formulaの値")

End Sub

結果

上記のように「Value=300」、「Formula=SUM(A1:B1)」が取得できました。
Valueは数式の結果の値が取得でき、Formulaは数式自体を取得することができます。

 

FormulaR1C1 相対参照を利用した数式入力

最後に、FormulaR1C1について紹介していきます。

Formulaは絶対参照と呼ばれる参照方法で式を入力してきました。
絶対参照とは、参照するセルが固定されていることです。

FomulaR1C1は、相対参照と呼ばれる方法で式を入力することができます。
相対参照とは、参照するセルを自由に移動させることができます。

 

FormulaR1C1の使い方は、下記のようになります。

基準セル.FormulaR1C1 = "=R[行の指定]C[列の指定]"

Rは「Row」、Cは「Columns」を意味しています。

基準セルが(1,1)の場合、
R[0]C[1]は(1,2)
R[2]C[1]は(3,2)
を意味しています。

 

言葉では難しいと思うので実際に例を紹介していきます。
下記のような表があり、合計の部分に数式を入力しましょう。

合計の部分に数式を入力する際に1件1件Formulaで数式を入力することもできますが、
FormulaR1C1と繰り返し処理を組み合わせることで一気に数式を埋めることが可能です。

Private Sub btnFormulaR1C1_Click()

    Dim rowindex As Integer
    rowindex = 3

    Do Until Cells(rowindex, 2).Value = 0
    
        Cells(rowindex, 5).FormulaR1C1 = "=R[0]C[-2]*R[0]C[-1]"
    
        rowindex = rowindex + 1
    
    Loop

End Sub

解説
6行目:繰り返し処理を利用しています。
この行がわからない方は、こちらを参考にしてみてください。

8行目:rowindex = 3 の場合は、Cells(rowindex,5)は「合計のセル:E3」のことです。このセルに対して、「R[0]C[-2]:セルC3」、「R[0]C[-1]:セルD3」を意味しています。
このように「合計セル:E3」を基準セルと考え、相対参照していきます。するとそれぞれ下記のような内容になります。
R[0]C[-2] = 基準セルE3 から 列を-2した セルC3 
R[0]C[-1] = 基準セルE3 から 列を-1した セルD3

結果

合計の部分に数式が入力されていることが確認できました。

また8行目の[0]は省略することができ、下記のように記載することもできます。

Cells(rowindex, 5).FormulaR1C1 = "=RC[-2]*RC[-1]"

R[0]C[-2]とRC[-2]は同じ内容になります。

 

まとめ

VBAから数式を入力する場合は、Formulaプロパティを利用する。
Valueでもできるが、本来はFormulaが正しいのでFormulaを利用しましょう。

また、文字列を利用する場合は、注意が必要です。
ダブルコーテーションを2つ続けて、エスケープしましょう。

 

EXCEL VBAの記事一覧

おすすめの記事