セルの数式をVBAで入力するためのプロパティ「Formulaプロパティ」の使い方を解説します。
基本構文
数式を入れたいセル.Formula = "=入力したい数式"
使い方
Range("D10").Formula = "=SUM(D4:D9)"
このように、入力したい数式をそのままRangeオブジェクトのFormulaプロパティに代入することで、ワークシートの数式をVBAから入力することができます。
とてもストレートな処理なので、覚えやすいですね。
1点注意しなければいけないのが「"」の扱いです。
"をそのまま打てば、VBAさんには「文字列を囲っている"」と認識されてしまうため、
「""」 のように2個並べて「これは文字列の区切りじゃなくて"だよ」とVBAさんにお伝えする必要があります。
例えば、文字列を使ったIF関数などは、
Range("A1").Formula = "=IF(A2="""","""",○○)" Range("A1").Formula = "=IF(A2=""みかん"",△△,□□)"
このように、"を1箇所につき2個ずつ書いてください。
さて、これでセルへの数式入力を行うことはできます。
しかし、「ワークシートの数式をマクロで入力したい」という需要は、
これではまったく満たされないのではないかと思われます。
シート関数というのは、
「コピーすることでたくさんのセルを一気に計算ができる」
のが一番メリットです。
それをマクロでやるわけですから、同じ様に一括で計算できないと、せっかくVBAにした意味がありません。
Range("D10").Formula = "=SUM(D4:D9)"
わざわざこれ書くくらいなら、手でD10に書くわい。
って話ですよね。
セル範囲に数式を一括入力する方法
普段ワークシート上でやっているような、
「1つのセルに数式を作って、コピーして一括で計算」
をマクロでやる場合は、以下のコードを実行します。
' 売上の計算 Range("E4:E9").Formula = "=C4*D4*(1+$E$1)" ' 合計の計算 Range("D10:E10").Formula = "=SUM(D4:D9)"
このサンプルのようにセル範囲のFormulaプロパティへ代入すると、
セル範囲に数式を一括入力することができます。
正確に仕様を記述しておきますと、
「セル範囲.Formula = "=数式"」を実行した場合、
セル範囲の第1セルにその数式を入力し、
その後他のセルへコピーしたときと同じように数式が入ります。
今回の式では、
相対参照の「価格」と「個数」はしっかり座標が動き、
絶対参照の「消費税」がちゃんと固定されて入力されます。
条件付き書式を数式でつけたことがある人は、
あれと同じような書き方というとわかりやすいでしょうか。
「A列が""なら、その行全体を赤くする」という条件付き書式を、
「=$A2=""」をすべてのセルで設定しているときと同じような仕様です。
この書き方で数式の一括入力ができますので、
マクロによる数式作成の自動化にご活用ください。
もともとセルに入っている数式を活用する方法
↓のように、「第1行にだけ数式が入っていて、それを利用したい」場面もあると思います。
Formulaはプロパティですので、当然読み取ることも可能です。
Formulaプロパティの読み取りを活用した数式のコピーがこちらです。
' 既存の数式をコピー Range("E4:E9").Formula = Range("E4").Formula
これでFormulaプロパティから取得した数式を、他のセルにコピーすることができます。
この方法には、注意しなければいけない点があります。
使ったコードをよーく眺めてください。
よく見ると、
「コピー先のエリア」に、「コピー元であるE4セル」が含まれていますね。
これがかなり重要なポイントです。
手作業でやる「コピー＆ペースト」のイメージで、
Range("E5:E9").Formula = Range("E4").Formula
こう書いてしまうと、4行目用の数式が5行目に入って、以降関数が1行ずつズレてしまいます。
なぜこうなってしまうのかというと、そもそも今回のコードは、
「セル範囲.Formula = "=数式"」を実行した場合、
セル範囲の第1セルにその数式を入力し、
その後他のセルへコピーしたときと同じように数式が入ります。
というFormulaプロパティの仕様を利用した書き方でした。
この仕様と書いたコードをよーく見比べれば、
↑でダメなのは至極当たり前のことですね。
ただ仕様通りに処理されただけです。
いつものノリで「E4の数式をE5から先へ」と考えてしまうため、
かなり錯覚しやすい部分です。十分に気を付けてください。
セルに既にある関数を利用する場合は、
「関数が入っているセルもペースト先に含める」ことを忘れずに。
実はこの方法、処理が超高速です
Formulaプロパティのコードを探していた方は、
「関数をシートに入力するのを自動化したい」
という目的で探されていた方が多いと思います。
ですが、この方法、実は処理速度が滅茶苦茶速いです。
この表に「関数を入れたい」のではなく、
「普通にE列を計算したい＝値を入れたい」とします。
この時、
' For文で行ごとに計算 For R = 4 To 9 Cells(R, 5) = Cells(R, 3) * Cells(R, 4) * (1 + Range("E1")) Next ' シートに数式を入力して計算 Range("E4:E9").Formula = "=C4*D4*(1+$E$1)"
この「やっている計算は全く同じ二つの方法」を比較すると、
後者の数式による計算が、圧倒的に速く処理を終えます。
つまり、「シート関数をマクロで作りたい」ではなく、
「普通に計算したい」場合にも、セルに数式を入れる方法が使えるということです。
この方法による高速化は、数式内にシート関数を使っていてももちろん利用できます。
例えば今回の表で、「価格」の列を「くだもの価格表」から調べてくるとしましょう。
みんな大好き「VLOOKUP」を使えば行けますね。
' VlookupをWorksheetFunctionで For R = 4 To 9 Cells(R, 3) = WorksheetFunction.Vlookup(Cells(R, 2), wsくだものリスト.Columns("A:B"), 2, False) Next ' VLOOKUPをFormulaで Range("C4:C9").Formula = "=VLOOKUP(B4,くだものリスト!A:B,2,FALSE)"
このように、「中身はまったく同じWorksheetFunction VS Formula」の戦いでも、Formulaが圧勝します。
高速でかつ、書き方も超単純と、これを活用しない手はありませんので、
どんどん活用してください。
計算後に値を固定する
さてシート関数で高速計算を終わったら、関数を残したいのでなければ値にしたくなりますね。
この場合は、
' セルの数式を活用して計算後、値を固定する Range("E4:E9").Formula = "=C4*D4*(1+$E$1)" Range("E4:E9").Value = Range("E4:E9").Value
このように、
「セル範囲.Value = セル範囲.Value」で値貼り付けと同じ処理ができます。
今回のようにセル範囲に同じRangeを用いれば、
よくある「コピーして同じ場所に値貼り付けして関数を切る」ができます。
この「セル範囲.Value = セル範囲.Value」が値貼り付けということを知らなかった方は、この機会にこちら覚えてしまいましょう。
PasteSpecialより圧倒的に速いですし、読みやすく書きやすいです。
さてこの時、注意しなければいけないのが、「関数の自動計算」です。
Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.EnableEvents = False
↑このマクロ高速化トリオ「計算・描画・イベントのOFF」をやっている方は、
Application.Calculation = xlCalculationAutomatic
↑これだけ元に戻してください。
数式で計算するんだから、
それを値にする前に更新しなければいけないのは当然ですね。
※ 実は今回の数式なら自動計算がOFFになっていても正しく動くのですが、
正しく動くときとそうでない時を解説するのが面倒誤認する危険があるので、
シート関数を利用時は常に自動計算をONに戻すようにしましょう。
おまけ：関数にしてしまう
さて先ほどの
' セルの数式を活用して計算後、値を固定する Range("E4:E9").Formula = "=C4*D4*(1+$E$1)" Range("E4:E9").Value = Range("E4:E9").Value
このコードですが、Range("E4:E9")が3回も出てきてしつこいです。
いやRange("E4:E9")ならまだいいんですが、
Range(Cells, Cells)の形で取得している範囲だと、
Range(Cells(R1, C1), Cells(R2,C2)).Formula = "=C4*D4*(1+$E$1)" Range(Cells(R1, C1), Cells(R2,C2)).Value = Range(Cells(R1, C1), Cells(R2,C2)).Value
こんなんなって、大事なところが埋もれてしまいます。
こういう冗長なコードを見たら、関数化のポイントです。
関数化するとこんな感じ。
' シート関数で計算 ⇒ 値を固定を汎用関数に Sub シート関数で計算する(計算エリア As Range, シート関数式 As String) 計算エリア.Formula = シート関数式 計算エリア.Value = 計算エリア.Value End Sub ' 使い方 Call シート関数で計算する(Range("E4:E9"),"=C4*D4*(1+$E$1)") ' さっきのしつこいパターンではこう Call シート関数で計算する(Range(Cells(R1, C1), Cells(R2,C2)),"=C4*D4*(1+$E$1)")
中身は馬鹿みたいに簡単な関数ですが、効果は絶大ですね。
コードがかなり見やすくなりました。
「関数は複雑な処理のためのもの」と思われがちですが、
案外こういう「しつこい記述を簡単にする」ための関数の方が便利だったりします。
書くのも簡単ですしね。
ちなみに今回の場合では、
' 値を固定するかどうかを、引数で指定可能に（省略時は固定） Sub シート関数で計算する(計算エリア As Range, シート関数式 As String _ , Optional is計算完了後に関数を切る As Boolean = True) 計算エリア.Formula = シート関数式 If is計算完了後に関数を切る Then 計算エリア.Value = 計算エリア.Value End Sub ' 値になる Call シート関数で計算する(Range("E4:E9"),"=C4*D4*(1+$E$1)") ' 関数が残る Call シート関数で計算する(Range("E4:E9"),"=C4*D4*(1+$E$1)", False)
こんなのもおすすめです。
関数を残すときも切るときも、ほとんど同じコードを書けるため、
非常にメンテしやすくなりますし、
テスト時はFalseで正しい関数が入っているかを確認し、
プログラムが完成したら、最後Falseを消してデバッグモード終了！
みたいな使い方もいけます。
お好きな方をどうぞ。
おまけ：なぜFormulaは速いのか
さて、最後にFormulaが速い理由を説明します。
Formulaが速いのは、単純に「セルひとつひとつに入力していないから」です。
よく「配列にすると速い」と言われますよね。
あれは「配列でメモリ上で処理するから速い」というのもありますが、
「処理後に配列ごと一気にセルに戻している」のが、速さの一番の理由です。
配列に限らず、
' 100回かけて1を代入 For R = 1 To 100 Cells(R, 1) = 1 Next ' 1回で1を代入 Range("A1:A100").Value = 1
↑のような単純な処理でも、後者が圧倒的に速いというのが、
配列を使った高速化のカラクリです。
今回のFormulaプロパティへの関数一括入力は、
すべてのセルに対して同時に入力されていますので、
配列でやったときに近い速度効果が得られるというわけですね。
この方法がチートなのは、
「配列などの高度な高速化に比べて、圧倒的にコーディング難易度が低い」ことです。
マクロを速くしたいと思っているけど、配列に手を出す余裕はないな～って思っていた方は、是非こちらの方法で高速化を試みてください。
ということで、ようやくこの記事は終了です。
ここまで読んでいただきありがとうございました。
ただ「マクロで楽して関数を入れたい」と思って来ただけなのに、
思いがけず長文を読む羽目になった方は申し訳ない。
この記事は、このブログを立ち上げる際、私がどうしても文字に起こしておきたかった四天王のうちの一人（多分最弱じゃない）です。
書いてる方も結構頑張って書きましたので、少しでも疲れに見合ったものをお持ち帰りいただければ幸いです。
長文読了、お疲れさまでした。
