マクロでも数式を使えるようにFormulaプロパティの使い方をマスターする

課題

以下のExcelシートにおいて「A2+B2」を計算し、C2セルに出力したい。
このとき、C2セルには計算結果の値として「3」を代入するのではなく、
式として「=A2+B2」を代入したい。

そしてその数式を６行目まで相対参照でコピーしたい。

この操作をマクロで実行できるプログラムを作成せよ。

回答と解説

回答

FormulaR1C1プロパティを使用する。

プログラム例
//FormuraR1C1 Sample Progaram //
For i = 2 To 6

Cells(i, 3).FormulaR1C1 = “=RC[-2]+RC[-1]”

Next i

実行結果
上記のマクロを実行することで、C2セル～C6セルにわたって、「=A2+B2」の相対参照が実行できる。

解説

左辺には、数式を出力したいセルを入力します。このとき「.FormulaR1C1」と記述することにより、Formulaプロパティを使用することを宣言します。

右辺には””で囲んだ部分をシート上に出力します。このとき
=RC[-2]+RC[-1] と記述しておりますが、これは

左辺で指定したセルから相対的に見て
同じROW(行)、2列手前のCOLUMN(列)に位置するセルと
同じROW(行)、1列手前のCOLUMN(列)に位置するセルの
数値を足してください。

という指示になります。したがって、左辺がCells(2,3)、すなわちC2セルの場合は
同じROW(行)、2列手前のCOLUMN(列)に位置するセル　＝　A2セルと
同じROW(行)、1列手前のCOLUMN(列)に位置するセル　＝　B2セルの数値を足してください
すなわち=A2+B2をしてください。という指示になるので、マクロを実行した際に
C2セルに「=A2+B2」が代入されるのです。

C3セルの場合は
同じROW(行)、2列手前のCOLUMN(列)に位置するセル　＝　A3セルと
同じROW(行)、1列手前のCOLUMN(列)に位置するセル　＝　B3セルの数値を足してください
すなわち=A3+B3をしてください。という指示になるので、マクロを実行した際に
C3セルに「=A3+B3」が代入されます。

これを６行目まで繰り返せば、目的は達成されます。

Formulaプロパティを使用しなかった場合

以下のシートで

以下のプログラムを実行すると

‘1列目と2列目を足すプログラム

Sub sample()

‘TypeA

For i = 2 To 6
Cells(i, 3).FormulaR1C1 = “=RC[-2]+RC[-1]”
Next i

‘TypeB

For i = 2 To 6
Cells(i, 4) = Cells(i, 1) + Cells(i, 2)
Next i

End Sub

このようなC列とD列に同様な数値を得ることができますが、

FormuraR1C1プロパティを使用しているC列では
数式が入力されていますが、

FormuraR1C1プロパティを使用しなかったD列では
数値のみが入力されることになります。

Formuraプロパティのメリットとデメリット

メリット

FormulaR1C1プロパティを使用すると、
①マクロを使用する者から見たとき、可読性がアップする。
②ソルバー機能を含んだマクロを組めるようになる。
というメリットが予想できます。

①について、私たちが作成したマクロをブラックボックスとして使用してもらうことを想定したとき、実行した結果として、

こちらの表示の方が

こちらの表示よりも使用者にとって、作成者が何をしているのかを推測しやすくなります。

②について、物理化学現象の解析や化学工場を設計を行う多くの人は
「多元連立方程式をソルバーで解く」
という方法論を学んでいます。しかし、マクロでソルバーをやらせるには、各方程式の残差を数式で表現できていなければいけません。このとき、R1C1Formulaプロパティが役に立ちます。
※応用例
[blogcard url=”http://shimaphoto03.com/science/mccabe-macro/”]

デメリット

マクロを組む者、読む者から見たとき、可読性が低下する恐れがあります。
例えば以下の資料では
[blogcard url=”http://shimaphoto03.com/science/rk-vib/”] Function F1(ByVal t As Double, ByVal x As Double, ByVal v As Double) As Double
F1 = v
End Function

Function F2(ByVal t As Double, ByVal x As Double, ByVal v As Double) As Double
F2 = -omega0 ^ 2 * x – 2 * gamma * v + force * Cos(omega * t)
End Function

とFunctionプロシージャを使用して式を表記することで、Functionプロシージャを見るだけで、何の式を計算しているのか、大体推測ができるようになります。