Excel VBAの数式 – 究極のガイド

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

masahiro yoshida

Last updated on 4月 17, 2022

In this Article

VBAでの計算式
マクロの記録機能とセルの数式
VBA FormulaR1C1プロパティ
VBA Formula プロパティ
VBA 数式のヒント

このチュートリアルでは、VBAを使用してセルの数式を作成する方法について説明します。

VBAでの計算式

VBAを使用すると、Excelのセルやセルの範囲に直接数式を書き込むことができます。以下のような感じです。

Sub Formula_Example()

    '1つのセルに絶対参照を使用して数式を代入する
    Range("b3").Formula = "=b1+b2"
    
    'セルの範囲に相対参照を使った数式を代入する
    Range("d1:d100").FormulaR1C1 = "=RC2+RC3"

End Sub

知っておくべきRangeプロパティは2つあります。

.Formula – 絶対参照を使った数式を作成します。（ハードコードされたセル参照）1つのセルに数式を追加するのに適しています。
.FormulaR1C1 – 相対参照を使った数式を作成します。セル参照を変更する必要があるセル範囲に数式を追加する場合に適しています。

簡単な計算式であれば、.Formulaプロパティを使っても問題ありません。しかし、それ以外の場合は、マクロの記録機能を使用することをお勧めします…

マクロの記録機能とセルの数式

マクロの記録機能は、VBAでセルの数式を書くのに最適なツールです。簡単にできます。

記録を開始する
セルに数式を入力し（必要に応じて相対参照/絶対参照を使い分ける）、Enterキーを押す
記録を停止する
VBAを開き、数式を確認し、必要に応じてコードをコピー＆ペーストする

VBAで対応する数式を入力するよりも、セルに数式を入力する方がはるかに簡単だと思います。

vba formula formular1c1 いくつかの点に注意してください。

マクロの記録機能は、常にFormulaR1C1プロパティを使用します。
マクロの記録機能は、絶対セル参照と相対セル参照を認識します。

VBA FormulaR1C1プロパティ

FormulaR1C1プロパティは、R1C1スタイルのセル参照を使用します。（Excelで見慣れた標準的なA1スタイルとは異なります。）以下はその例です。

Sub FormulaR1C1_Examples()

    'D5を参照 (絶対)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"
    
    'セルA1からD5を参照 (相対)
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"
    
    'セルA1からD5を参照 (絶対行、相対列)
    '=D$5
    Range("a1").FormulaR1C1 = "=R5C[3]"
    
    'セルA1からD5を参照 (相対行、絶対列)
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

End Sub

R1C1形式のセル参照では、絶対参照と相対参照を設定できることに注意してください。

絶対参照

標準的なA1表記では、絶対参照は “=$C$2” となり、R1C1記法では “=R2C3” となります。

R1C1スタイルで絶対セル参照を作成するには、次のように入力します。

R + 行番号
C + 列番号

例例：R2C3 はセル $C$2 を表します（C は 3 列目）。

    'D5を参照 (絶対)
    '=$D$5
    Range("a1").FormulaR1C1 = "=R5C4"

相対参照

相対参照は、数式が移動したときに「移動」するセル参照です。標準的なA1表記では、次のようになります。”=C2″.R1C1記法では、大括弧[]を使用して、現在のセルからセル参照をオフセットします。例セルB3に「=R[1]C[1]」と入力すると、セルD4（数式セルの1行下、1列右のセル）を参照します。現在のセルより上または左のセルを参照する場合は、負の数を使用します。

    'セルA1からD5を参照 (相対)
    '=D5
    Range("a1").FormulaR1C1 = "=R[4]C[3]"

混在参照

セル参照は、部分的に相対参照、部分的に絶対参照にすることができます。

    'セルA1からD5を参照 (相対行、絶対列)
    '=$D5
    Range("a1").FormulaR1C1 = "=R[4]C4"

VBA Formula プロパティ

Formulaプロパティで数式を設定する場合、常にA1スタイルの表記を使用することになります。数式は、引用符で囲む以外は、Excelのセルに入力するのと同じように入力します。

    '1つのセルに絶対参照を使用して数式を代入する 
    Range("b3").Formula = "=b1+b2"

VBA 数式のヒント

変数を含む数式

VBAで数式を扱うとき、セルの数式内で変数を使いたいことはよくあります。変数を使用するには、&を使用して、変数と残りの数式文字列を組み合わせます。

Sub Formula_Variable()
    Dim colNum As Long
    colNum = 4

    Range("a1").FormulaR1C1 = "=R1C" & colNum & "+R2C" & colNum

End Sub

数式による引用

数式内に引用符（”）を追加する必要がある場合は、引用符を2回（””）入力します。

vba formula quotations

Sub Macro2()
    Range("B3").FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/yyyy""")"
End Sub

一つの引用符（”）は、VBAに対して文字列の終わりを意味します。一方、2つの引用符（””）は、文字列の中の引用符と同じように扱われます。同様に、クォーテーションマーク（””）で文字列を囲む場合は、クォーテーションマーク（””）を3つ使用します。

MsgBox """引用符で文字列を囲むには3を使用する"""
' これは、<"Use 3 to surround with quotes"> immediate windowと表示されます。

セルの数式を文字列変数に代入する

指定されたセルや範囲にある数式を読み込んで、文字列変数に代入することができます。

「セルの数式を変数に代入する
Dim strFormula as String
strFormula = Range("B1").Formula

セルに数式を追加するさまざまな方法

ここでは、セルに数式を代入する方法について、さらにいくつかの例を示します。

数式の直接代入
数式を含む文字列変数を定義する
変数を使って数式を作成する

Sub MoreFormulaExamples ()
' SUM式を追加する別の方法
' セルB1への
'
  Dim strFormula as String
  Dim cell as Range
  dim fromRow as long, toRow as long

  セットセル = レンジ("B1")

  ' 文字列を直接代入する
  cell.Formula = "=SUM(A1:A10)"

  ' 文字列を変数に格納する
  ' と "Formula" プロパティに代入する
  strFormula = "=SUM(A1:A10)"
  cell.Formula = strFormula

  ' 変数を使用して文字列を構築する 
  'そして、それを "Formula" プロパティに代入する
  fromRow = 1
  toRow = 10
  strFormula = "=SUM(A" & fromValue & ":A" & toValue & ")
  セル.フォーミュラ = strFormula
終了 Sub

数式のリフレッシュ

注意点として、数式を更新するには、Calculateコマンドを使用します。

計算

単一の数式、範囲、またはワークシート全体をリフレッシュするには、代わりに .Calculate を使用します。

シート("Sheet1").Range("a1:a10").Calculate。