ExcelマクロVBA入門
第38回.セルに計算式を設定(Formula)

Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
最終更新日:2019-07-09

第38回.セルに計算式を設定(Formula)


セルに計算式(関数)を設定する場合のマクロVBAになります。
マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、
決して使わないわけではありません。
しかし、この計算式の設定には何種類ものプロパティがあり、結構やっかいなのです。


計算式を設定できるプロパティ

以下は、Rangeオブジェクトのプロパティです。

Value
Formula
FormulaLocal
FormulaR1C1
FormulaR1C1Local

これら全てを覚える必要はありませんが、一応の知識として一通り紹介しておきます。

Valueプロパティ

これは今まで、文字・数値をセルに入れる場合に使用してきました。
同様に、計算式も入れる事が出来ます。

A1セル = B1セル + C1セル



Range("A1").Value = "=B1+C1"

これは、普通に計算式を指定すれば良いです。
関数でも同じです。
A1セル = B1~B10セルの合計

Range("A1").Value = "=SUM(B1:B10)"

Formulaプロパティ , FormulaLocalプロパティ

これは計算式を設定することに関しては、Valueと同じです。

違いは、計算式の入っているRangeを取得した時になります。
Valueプロパティは、計算結果の値が取得され、
Formulaプロパティは、計算式が取得されます。

FormulaとFormlaLocalの違いについては後述します。

FormulaR1C1プロパティ , FormulaR1C1Localプロパティ

これが解りづらいかもしれません。
Formulaプロパティとの違いは、
計算式をR1C1参照形式で指定するこということです。
R1C1形式の詳細は次で詳しく説明します。

FormulaR1C1とFormlaR1C1Localの違いについては後述します。

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と付くものは、PC環境に依存する設定を有効にする場合に使います。

書式の設定では、NumberformatLocalを使いましたが、それと同じような事です。
書式では、マシンに依存する表示を使う事が多いので、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参照形式で設定する必要があるので、まあ当然ですね。

上記の例を見る限りでは、Valueだけで良い事になります。
結論から言えば、そういう事になってしまいますが、
マクロを書くときには、可読性を考慮しますので、
R1C1参照形式で設定する場合は、FormulaR1C1を使うようにします

何故、こんなに多くのプロパティが存在しているのか

多くのプロパティが存在して、そして何故紹介する必要があるのかと言うと、
それは計算式を設定する時ではなく、設定された計算式を参照する時に使うからです。

以下の設定をした場合、

Range("A1").Value = "=B1+C1"

これを実行した後に、そのプロパティを参照すると、

Range("A1").Value ・・・ 計算結果の値
Range("A1").Formula ・・・ =B1+C1
Range("A1").FormulaR1C1 ・・・ =RC[1]

このように、取得されるものが違ってきます。

つまり、計算式を設定する場合は、
Value
で良いが、
計算式を参照する場合は、
.Formula
または、
.FormulaR1C1
を使う必要があると言う事になります。

R1C1形式を使うメリット

R1C1参照形式は、少し面倒に感じるかもしれませんが、
以下のような場合には、有効な指定方法になります。

2行目から最終行まで、B列にA列*100の計算式を設定するとしたとき、
通常の参照形式では、
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で複数のセルに計算式を入れる

マクロ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

このように、1行でも書く事が出来るようになります。
(Offsetについては、後々説明します。)
この「マクロVBA入門」を最期まで読み進めれば、これをすんなり書けるようになるはずです。
少なくとも、これがすんなり読めるようになるまではここでのVBA学習を続けてみてください。



同じテーマ「マクロVBA入門」の記事

第35回.セルの書式(罫線,Border)
第36回.総合練習問題4
第37回.ブック・シートの指定
第38回.セルに計算式を設定(Formula)
第39回.セルのクリア(Clear,ClearContents)
第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)
第41回.セルのコピー&値の貼り付け(PasteSpecial)
第42回.セルをコピーするとは
第43回.総合練習問題5
第44回.VBA関数について
第45回.VBA関数(Format)


新着記事NEW ・・・新着記事一覧を見る

Byte配列と文字コード関数について|VBA技術解説(8月20日)
PowerQueryの強力な機能をVBAから利用する方法|VBA技術解説(8月4日)
練習問題31(セル結合を解除して値を埋める)|VBA練習問題(7月30日)
練習問題30(マトリックス→リスト形式)|VBA練習問題(7月25日)
Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説



  • >
  • >
  • >
  • セルに計算式を設定(Formula)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。




    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ