第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 |
このように、1行でも書く事が出来るようになります。
(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コードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。