第38回.セルに計算式を設定（Formula）
セルに計算式（関数）を設定する場合のマクロVBAになります。
マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、
決して使わないわけではありません。
しかし、この計算式の設定には何種類ものプロパティがあり、結構やっかいなのです。
計算式を設定できるプロパティ
Value
Formula
FormulaLocal
FormulaR1C1
FormulaR1C1Local
これら全てを覚える必要はありませんが、一応の知識として一通り紹介しておきます。
Valueプロパティ
同様に、計算式も入れる事が出来ます。
Range("A1").Value = "=B1+C1"
これは、普通に計算式を指定すれば良いです。
関数でも同じです。
A1セル = B1～B10セルの合計
Range("A1").Value = "=SUM(B1:B10)"
Formulaプロパティ , FormulaLocalプロパティ
Valueプロパティは、計算結果の値が取得され、
Formulaプロパティは、計算式が取得されます。
FormulaR1C1プロパティ , FormulaR1C1Localプロパティ
Formulaプロパティとの違いは、
計算式をR1C1参照形式で指定するこということです。
R1C1形式の詳細は次で詳しく説明します。
R1C1参照形式
行位置をRに続けて記述し、列位置をCに続けて記述します。
R ・・・ Rの後に何も記述しない場合は同一行
C ・・・ Cの後に何も記述しない場合は同一列
R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数
C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数
R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をずらした行
C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をずらした列
これらを組み合わせて、計算式を設定するセルからの位置を指定します。
例．C5セルに計算式を設定する場合、
RC1 ・・・ A5セル
R1C ・・・ C1セル
R[1]C[2] ・・・ E6セル
RC[-1] ・・・ B5セル
このようになります。
Localが付くプロパティについて
Localと付くものは、PC環境に依存する設定を有効にする場合に使います。
書式では、マシンに依存する表示を使う事が多いので、Localを使う事を勧めました。
しかし、計算式ではそんなに多くはありません。
ただし、全くないわけではありません、あるにはあります。
例えば、
JIS関数を設定するなら、Localを使わないとエラーと正しく設定できません。
Range("B1").Formula = "=JIS(A1)"
これは、#NAME?となってしまいます。
JIS関数を入れたセルのFormulaプロパティを取得すると、
"=DBCS(A2)"と入っている事が分かります。
従って、この"=DBCS(A2)"をFormulaプロパティに入れれば正しく設定きます。
しかし、
FormulaLocalプロパティなら、
Range("B1").FormulaLocal = "=JIS(A1)"
これで正しく設定できます。
それぞれの違い（Localは除く）
Range("A1").Value = "=B1+C1"
Range("A2").Formula =
"=B1+C1"
Range("A3").FormulaR1C1 =
"=B1+C1"
Range("A4").Value = "=RC[1]+RC[2]"
Range("A5").Formula = "=RC[1]+RC[2]"
Range("A6").FormulaR1C1 = "=RC[1]+RC[2]"
上記では、A3セルのみ正しく設定されません。
R1C1の場合は、R1C1参照形式で設定する必要があるので、まあ当然ですね。
結論から言えば、そういう事になってしまいますが、
マクロを書くときには、可読性を考慮しますので、
R1C1参照形式で設定する場合は、FormulaR1C1を使うようにします。
何故、こんなに多くのプロパティが存在しているのか
それは計算式を設定する時ではなく、設定された計算式を参照する時に使うからです。
Range("A1").Value = "=B1+C1"
これを実行した後に、そのプロパティを参照すると、
Range("A1").Value ・・・ 計算結果の値
Range("A1").Formula ・・・
=B1+C1
Range("A1").FormulaR1C1 ・・・ =RC[1]
このように、取得されるものが違ってきます。
Value
で良いが、
計算式を参照する場合は、
.Formula
または、
.FormulaR1C1
を使う必要があると言う事になります。
R1C1形式を使うメリット
以下のような場合には、有効な指定方法になります。
通常の参照形式では、
2行目は、A2*100
3行目は、A3*100
というようになり、行ごとに計算式を変更しなければならなくなります。
しかし、R1C1参照形式を使えば、
Sub サンプル1()
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 2).FormulaR1C1 = "=RC[-1]*100"
Next
i
End Sub
R1C1参照形式は、
設定するセルからの、相対参照・絶対参照の組み合わせになっていますので、
設定するセルがどこのセルであろうと、同一の計算式を設定する事が出来ます。
たった1行のVBAで複数のセルに計算式を入れる
たった1行のVBAで複数のセルに計算式を入れることもできるようになります。
2行目から最終行までのB列に、A列*100の計算式を設定する場合、
Sub サンプル2()
Range(Range("B1"), Cells(Rows.Count, 1).End(xlUp).Offset(0,
1)).Value = "=A1*100"
End Sub
このように、１行でも書く事が出来るようになります。
（Offsetについては、後々説明します。）
この「マクロVBA入門」を最期まで読み進めれば、これをすんなり書けるようになるはずです。
少なくとも、これがすんなり読めるようになるまではここでのVBA学習を続けてみてください。
同じテーマ「マクロVBA入門」の記事
第9回.Rangeでのセルの指定方法
第10回.Range以外の指定方法（Cells,Rows,Columns）
第11回.RangeとCellsの使い方
第38回.セルに計算式を設定（Formula）
第12回.変数宣言のDimとデータ型
第13回.定数宣言のConstと型宣言文字
第14回.文字の結合（&アンパサンド）と継続行（_アンダーバー）
第15回.四則演算と注釈（コメント）
第16回.繰り返し処理（For Next)
第17回.繰り返し処理（Do Loop）
第18回.最終行の取得（End,Rows.Count）
新着記事NEW ・・・新着記事一覧を見る
多階層フォルダ（ディレクトリ）の作成｜VBAサンプル集（7月31日）
VBAのインデントについて｜VBA技術解説（7月16日）
「VBA Match関数の限界」についての誤解｜エクセル雑感（7月15日）
省略可能なVariant引数の参照不可をラップ関数で利用｜VBA技術解説（7月12日）
100桁の正の整数値の足し算｜エクセル雑感（7月9日）
LSetとユーザー定義型のコピー（100桁の足し算）｜VBA技術解説（7月9日）
Variant仮引数のByRefとByValの挙動違い｜エクセル雑感（7月5日）
Variant仮引数にRange.Valueを配列で渡す方法｜エクセル雑感（7月5日）
Variantの数値型と文字列型の比較｜エクセル雑感（7月1日）
VBAのVariant型について｜VBA技術解説（6月30日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.マクロはどこに書くの（VBEの起動）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。