はじめに
VBAでセルに値を直接入力するだけでなく、「数式そのもの」を設定したい場面があります。たとえば「合計はSUM関数で計算させたいが、その式自体をマクロで一括入力したい」といったケースです。このようなときに使うのがFormulaプロパティとFormulaR1C1プロパティです。
この記事では、FormulaとFormulaR1C1の基本的な使い方の違いから、複数セルへの一括数式設定、相対参照・絶対参照の指定方法まで、実務で使える形で解説します。
Formulaプロパティの基本
Formulaプロパティは、Excelの数式バーに表示されるのと同じ形式（A1形式）で数式を文字列として設定・取得できます。
Sub SetFormulaBasic()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2").Formula = "=B2*C2"
End Sub
このコードを実行すると、D2セルに「=B2*C2」という数式が入力され、B2とC2の掛け算の結果が表示されます。文字列として数式を組み立てられるため、セル参照を変数で動的に変えることも簡単です。
複数セルに数式を一括設定する
最終行まで同じパターンの数式を入力したい場合は、ループと組み合わせます。
Sub SetFormulaInBulk()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, "D").Formula = "=B" & i & "*C" & i
Next i
End Sub
行番号を文字列として連結することで、行ごとに正しいセル参照を持つ数式を組み立てています。件数が多いデータでも、この方法なら一瞬で数式を入力できます。
範囲指定で一気に数式を設定する
実は、Rangeオブジェクトに複数セルを指定してFormulaを設定すると、範囲内のすべてのセルに同じ数式が一括で入力されます。相対参照はセルごとに自動調整されるため、ループを使わずにシンプルに書けます。
Sub SetFormulaToRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2:D100").Formula = "=B2*C2"
End Sub
D2に設定した「=B2C2」を基準に、D3には「=B3C3」、D4には「=B4*C4」というように、Excelが自動的に相対参照を調整して入力してくれます。
FormulaR1C1プロパティとの違い
FormulaR1C1は、セル参照を「基準セルからの相対位置」で表す形式（R1C1形式）を使います。行を「R」、列を「C」で表し、R[n]C[n]のように相対位置を指定します。
Sub SetFormulaR1C1Basic()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2").FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
RC[-2]は「同じ行の2列左」、RC[-1]は「同じ行の1列左」を意味します。D2から見るとB列・C列にあたるため、結果はFormulaで書いた「=B2*C2」と同じになります。
FormulaR1C1が便利なのは、基準セルの位置に関係なく同じ相対位置のルールで数式を組み立てられる点です。ループ処理で行番号を文字列連結する必要がなく、コードがシンプルになります。
Sub SetFormulaR1C1ToRange()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2:D100").FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
絶対参照を組み込む方法
行や列を固定した絶対参照（$B$1のような形式）を使いたい場合は、Formulaプロパティにそのまま$記号を含めて記述します。
Sub SetFormulaWithAbsoluteRef()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("D2:D100").Formula = "=B2*$C$1"
End Sub
C1セルに単価などの共通値を入力しておき、各行のB列にその単価を掛ける、といった計算パターンで使えます。$C$1は絶対参照のため、コピーされても常にC1セルを参照し続けます。
設定した数式を確認・取得する
Formulaプロパティは数式の設定だけでなく、既存セルの数式を文字列として取得する際にも使います。
Sub GetFormulaSample()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Debug.Print ws.Range("D2").Formula
End Sub
イミディエイトウィンドウに、D2セルに入力されている数式がそのまま表示されます。他ブックの数式の内容を調査したいときや、動作確認のデバッグに役立ちます。
まとめ
FormulaとFormulaR1C1プロパティを使いこなせば、VBAから数式そのものをセルに設定でき、値だけでなく計算ロジックごと自動化できます。
FormulaはA1形式で数式を文字列として設定・取得する- 範囲を指定して
Formulaを設定すると、相対参照が自動調整されて一括入力できる FormulaR1C1はR1C1形式（相対位置）で数式を組み立てられる- 絶対参照を使いたい場合は
$記号を含めてFormulaに記述する
セルに値だけでなく数式ごと自動入力できるようになると、集計シートやテンプレート作成の自動化の幅が大きく広がります。
コメント