LWP | VBAで数式を入れる―保存・参照・展開の違い

四行の金額を計算する表を作っていたシャケモン。「200が出れば同じ」と見えたセルでも、数量を変更すると結果が分かれました。アユさんと、セルに残るもの、参照の書き方、数式の個数を順に整理します。

対象はWindowsデスクトップ版Excelの通常のセル範囲です。スピルの例では、動的配列に対応したExcelを使います。B列が数量、C列が単価、一行が一件の明細です。末尾の見本マクロで、図と同じ四行を新しいブックへ作れます。

提出時点の金額を固定したいなら値、数量や単価の変更に追従させたいなら数式を残します。先に「どの時点の何を残すか」を決めると、プロパティを選ぶ理由が明確になります。

VBAでは数式を文字列として渡しますが、Formulaへ渡した文字列はExcelが数式として扱います。数式を設定することと再計算のタイミングは別です。手動計算の環境では、式が入ったことだけで結果の更新まで完了したと判断しません。Range.Formula、Range.Value2

R1C1の角括弧は相対的な距離、括弧のない数字は固定した行・列を表します。D列から数量B列を見るなら2列左、E列から同じB列を見るなら3列左です。式を置く列を変更するときは、相対位置も見直します。

画面がA1形式で表示されていても、FormulaR1C1へはR1C1形式で渡せます。画面の表示設定を変えることと、VBAが受け取る数式の記法を選ぶことを分けて考えましょう。Range.FormulaR1C1

D2:D5のような連続範囲へ一つのA1形式の式を設定すると、この例では行ごとに参照が変わります。確認するときはD2だけでなく、D3や最後のD5に保存された式も見ます。結果だけを見ると、参照先の誤りを見落とす場合があります。

FillDownは先頭行の内容と書式をコピーします。下の行に異なる表示形式を残したい場合は、式の設定と書式の統一を別々に考えます。ここで示す結果を、非連続の選択範囲や条件付き書式のFormula1まで一般化しません。Range.FillDown

同じ規則を全行へ適用できるなら一括設定で済みます。送料の加算や値引きなどで行ごとに異なる式が必要な場合は、出力範囲と同じ形の配列へ式を用意できます。図では説明のため、三行目に50を加算し、四行目に10%の値引きを入れています。

配列の第一要素がD2、第二要素がD3に対応します。数式中のB3やC3も自分で指定するため、配列の順番と参照先の行がそろっているかを確認します。これは数式の配列を各セルへ渡す方法で、起点一つから結果を出すスピルとは仕組みが違います。

Formula2は配列として評価する入口です。式の結果が複数ならスピルし得ますが、結果が一つなら一セルです。複数の結果を作りたいからといって、同じ式をスピル先の各セルへコピーする必要はありません。FormulaとFormula2

この例ではF2:F5を空け、F2だけへ式を入れます。F3に値があれば、結果を置けずスピルエラーになります。図のような通常範囲から試し、展開先の既存値、結合セル、Excelテーブル内かどうかを確認します。長い表へ置き換える際は、二つの入力範囲の行数もそろえます。動的配列とスピルの動作

同じ範囲へValue2を代入すると、保存されていた数式は結果の値に置き換わります。後から数量が変わっても、そのセルは追従しません。見本マクロでは比較しやすいよう、D列の式を残し、G列へ固定値を作ります。

Range.Calculateは指定範囲を計算するための操作です。外部データの取得完了まで保証するものではありません。実務では対象ブック・シート・範囲を明示し、入力側の更新が済んでいるかを確認してから、結果を固定します。Range.Calculate

最初に、セルへ固定値を残すのか、更新する数式を残すのかを決めます。次に、A1とR1C1のどちらで参照を書くか、各セルへ式を置くのか、起点一つから結果を出すのかを選びます。確認する対象は「保存された式」「参照先」「結果」の三つです。

自分の表へ応用するときは、下の見本のB列・C列を入力列へ、D列以降を出力列へ置き換えます。R1C1の列の差、配列と出力範囲の大きさ、スピル先の空き、値化してよい範囲を順に確かめましょう。

Option Explicit

Public Sub 数式を比べる_見本()

    Dim 見本 As Workbook

    Dim 表 As Worksheet

    Dim 行 As Long

    Dim 式一覧(1 To 4, 1 To 1) As Variant

    Rem ## 元のブックとは別に、四行の見本を作る

    Set 見本 = Workbooks.Add(xlWBATWorksheet)

    Set 表 = 見本.Worksheets(1)

    表.Name = "数式見本"

    With 表

        .Range("B1:H1").Value2 = Array( _

            "数量", "単価", "A1一括", "R1C1一括", _

            "スピル", "値で固定", "行別の式")

        For 行 = 2 To 5

            .Cells(行, 2).Value2 = 行

            .Cells(行, 3).Value2 = (行 - 1) * 100

        Next 行

        Rem ## 同じ入力を、異なる入口へ渡す

        .Range("D2:D5").Formula = "=B2*C2"

        .Range("E2:E5").FormulaR1C1 = "=RC[-3]*RC[-2]"

        .Range("F2").Formula2 = "=B2:B5*C2:C5"

        式一覧(1, 1) = "=B2*C2"

        式一覧(2, 1) = "=B3*C3+50"

        式一覧(3, 1) = "=B4*C4*0.9"

        式一覧(4, 1) = "=B5*C5"

        .Range("H2:H5").Formula = 式一覧

        Rem ## 計算後、比較用の固定値を別列へ残す

        .Range("D2:H5").Calculate

        .Range("G2:G5").Value2 = .Range("D2:D5").Value2

        .Columns("B:H").AutoFit

    End With

End Sub

• H2：2×100＝200。

• H3：3×200＋50＝650。

• H4：4×300×0.9＝1080。

• H5：5×400＝2000。

元資料：「VBAでセルに数式を設定する基本」。プロパティごとの動作は、次のMicrosoft公式資料を参照しています。

• Microsoft Learn：Formula

• Microsoft Learn：FormulaR1C1

• Microsoft Learn：FormulaとFormula2

• Microsoft Learn：FillDown

• Microsoft Learn：Value2

• Microsoft Learn：Calculate

• Microsoft Support：動的配列とスピルの動作

Copyright © LWP 山中 一弘 |