第38回.セルに計算式を設定（Formula）
セルに計算式（関数）を設定する場合です。
マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、
使わないわけではありません。
しかし、この計算式の設定には、何種類ものプロパティがあり、
結構やっかいなのです。
計算式を設定できるプロパティ
.Formula
.FormulaLocal
.FormulaR1C1
.FormulaR1C1Local
これら全てを覚える必要はありませんが、一応の知識として紹介しておきます。
.Value
同様に、計算式も入れる事が出来ます。
A1セル = B1セル + C1セル
Range("A1").Value = "=B1+C1"
これは、普通に計算式を指定すれば良いです。
関数でも同じです。
A1セル = B1〜B10セルの合計
Range("A1").Value = "=SUM(B1:B10)"
.Formula
.FormulaLocal
.FormulaR1C1
と
.FormulaR1C1Local
計算式を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について
書式の設定では、
NumberformatLocalを使いましたが、それと同じような事です。
書式では、マシンに依存する表示を使う事が多いので、Localを使う事を勧めました。
しかし、計算式ではそんなに多くはないですが、あるにはあります。
例えば、JIS関数を設定するなら、Localを使わないとエラーとなります。
それぞれの違い（Localは除く）
Range("A2").Formula = "=B1+C1"
Range("A5").Formula = "=RC[1]"
Range("A6").FormulaR1C1 = "=RC[1]"
上記では、A3セルのみ正しく設定されません。
R1C1の場合は、R1C1参照形式で設定する必要がある訳ですが、まあ当然ですね。
それなら、結果として、.Valueだけで良い事になります。
結論からすれば、そういう事なのですが、
マクロを書くときには、可読性を考慮しますので、
R1C1参照形式で設定する場合は、R1C1を使うようにします。
何故、こんなに多くのプロパティが存在しているのか
そして、このブログでも何故紹介したのかと言うと、
それは、設定する時ではなく、その設定された計算式を参照する時に使うからです。
以下の設定をした場合、
Range("A1").Value = "=B1+C1"
設定後に、そのプロパティを参照すると、
Range("A1").Value ・・・ 計算結果の値
Range("A1").Formula ・・・ =B1+C1
Range("A1").FormulaR1C1 ・・・ =RC[1]
のように、参照結果が違ってきます。
つまり、計算式を設定する場合は、
.Value
で良いが、
計算式を参照する場合は、
.Formula
または、
.FormulaR1C1
を使うと言う事になります。
R1C1を使うメリット
以下のような場合には、有効な指定方法になります。
| Sub サンプル1() Dim i As Long For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row Cells(i, 2).FormulaR1C1 = "=RC[-1]*100" Next i End Sub |
R1C1参照形式は、設定するセルからの、相対参照・絶対参照の組み合わせになっていますので、
設定するセルが、どのセルであろうと、同一の計算式を設定する事が出来ます。
さらに、工夫すれば
| Sub サンプル2() Range(Range("B1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value = "=A1*100" End Sub |
このように、１行でも書く事が出来るようになります。
（Offsetについては、後々説明します。）
この「マクロ超入門」を最期まで読み進めれば、これをすんなり書けるようになります、
ただし、少し真面目に勉強すればですけどね（笑）
同じテーマ「マクロ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）
新着記事 ・・・新着記事一覧を見る
Excelシートの複雑な計算式を解析するVBA｜VBAサンプル集（2月18日）
VBAクラスの作り方：独自Rangeっぽいものを作ってみた｜VBA技術解説（2月16日）
VBAクラスの作り方：列名のプロパティを自動作成する｜VBA技術解説（2月14日）
VBAクラスの作り方：列名の入力支援と列移動対応｜VBA技術解説（2月11日）
クラスを使って他ブックのイベントを補足する｜VBA技術解説（2月6日）
Excelアドインの作成と登録について｜VBA技術解説（2月3日）
参照設定、CreateObject、オブジェクト式の一覧｜VBA技術解説（1月20日）
VBAでファイルを規定のアプリで開く方法｜VBA技術解説（1月20日）
ドキュメントプロパティ（BuiltinDocumentProperties,CustomDocumentProperties）｜VBA技術解説（1月19日）
他ブックへのリンクエラーを探し解除｜VBAサンプル集（1月15日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.変数とデータ型（Dim）｜ExcelマクロVBA入門
4.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
5.RangeとCellsの使い方｜ExcelマクロVBA入門
6.マクロって何？VBAって何？｜ExcelマクロVBA入門
7.繰り返し処理（For Next)｜ExcelマクロVBA入門
8.とにかく書いて見よう（Sub,End Sub）｜VBA入門
9.定数と型宣言文字（Const）｜ExcelマクロVBA入門
10.ひらがな⇔カタカナの変換｜エクセル基本操作
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。
