【ExcelVBA】セル範囲にシート関数をコピーする - 基本構文とサンプルコード

セルに入力されたシート関数を、
VBAを使って別のセル範囲へコピーする方法を紹介します。

セル範囲にシート関数を入力するコード
言葉の意味そのままに「シート関数をコピーする」コード
重要なおまけ：実はこの方法、超高速マクロの入り口です

セル範囲にシート関数を入力するコード

関数のコピーを行う表

D列に関数を入れましょう。
書かなくてもわかる気もしますが、「=B2*C2」が目的の関数ですね。

単刀直入に答えから。

Range("D2:D7").Formula = "=B2*C2"

これでOKです。

「セル範囲.Formula = 関数式」と入力した場合は、
セル範囲の第1セルにその関数を入力し、
その後他のセルへコピーしたときと同じように数式が入ります。

条件付き書式の数式をいじったことがある人は、
あれと同じような書き方というとわかりやすいでしょうか。

言葉の意味そのままに「シート関数をコピーする」コード

さて↑でいきなり「答え」とか言ってますが、
よく見るとシート関数はコピーしてないですね。

本記事タイトルと違うことしてて、タイトル詐欺になっちゃうので、
シート関数をコピーするコードもちゃんと書くとこうです。

Range("D2").Formula = "=B2*C2"
Range("D2:D7").Formula = Range("D2").Formula

こう書きたくなる気持ちはわかります。

最初のセルに関数を入れて　⇒　他のセルへコピー

と、手作業ではやりますからね。

それをマクロで自動化したいというのが、
この記事を読まれている方の需要かと思われますが、
冒頭のコードを知った今、既に需要はないでしょう(笑)

ですが、↓の注意点を説明するために必要なので書きました。

いけそうだけどダメな方法

関数をコピーしたコードは、注意しなければいけない点があります。

手作業と明確に異なる点があったのですが、気づきましたでしょうか？
　

Range("D2:D7").Formula = Range("D2").Formula

↑よく見ると、
「コピー先のエリア」に、「コピー元であるD2セル」が含まれていますね。

これ、とても重要です。

まず、関数をコピーというと、以下の式が思いつきます。

Range("D2").Formula = "=B2*C2"
Range("D3:D7").Formula = Range("D2").Formula

D2をD3:D7へ、という素直な書き方です。

ですが、これをやると、D3に「=B2*C2」が入力され、
以降1行ずつ関数がズレてしまうのです。

なぜでしょう。

手作業ならいけそうな処理なので、
なぜダメなのか、すぐにはわかりませんよね。

ここで冒頭の説明を思い出してください。

です。
そしてこれをもう一度↓

Range("D2").Formula = "=B2*C2"
Range("D3:D7").Formula = Range("D2").Formula

よーく考えてコードを見ると、素直に仕様通りに処理されただけですね(笑)

ダメな理由を解説しようにも、
「そもそもこれで行けると思ったのが手作業の慣れからくる脳の錯覚です」
としか説明できません(笑)

ということで、昔の私と同じ錯覚に陥っていた方がいらっしゃいましたら、
謎が解けて、めでたしめでたしです。

まあ、

Range("D2:D7").Formula = "=B2*C2"

もうこれでやるから謎が解けたところで使いどころはありませんけど。

使いどころ

と冗談はおいておいて、言うほど使いどころが0ではありません。

Range("D2").Formula = "=B2*C2" ' … ①
Range("D2:D7").Formula = Range("D2").Formula ' … ②

↑このコードは①で数式の入力もマクロでやっており、
もちろんそんな使い方はもうしないでしょうが、
数式は手でワークシートに入力しておき、それを②のマクロでコピーというのはメリットがあります。

空のシートの2行目に、数式をテンプレートとして入力しておく
マクロによるデータの加工は3行目以降に行う
データが完成時に、数式をコピーする

ような使い方は考えられます。

その際は、「シート関数のテンプレートである2行目もペースト先に含める」ことをお忘れなく。

いけるけどおすすめしない方法

さて、先ほどダメだった

Range("D2").Formula = "=B2*C2"
Range("D3:D7").Formula = Range("D2").Formula

この脳の錯覚パターンですが、実は

Range("D2").FormulaR1C1 = "=B2*C2"
Range("D3:D7").FormulaR1C1 = Range("D2").FormulaR1C1

と、プロパティを「FormulaR1C1」に変えると成功します。

成功しますが、これはあまりおすすめしません。

理由は単純で、R1C1形式で普段関数を使っていない人が、
おまじない的に手を出すと、いらぬ罠にはまるからです。
　

FormulaR1C1 = "=B2*C2"

この「A1形式の式をR1C1プロパティに入れる」っていう時点で、すでにちょっと裏技です。
この方法は、普段からR1C1形式を使っている方以外は、使わないようにしましょう。

おまけ：すばらしい方法

Range("D2").Copy
Range("D3:D4").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

己の力では無理と悟るや否や、初心のマクロ記録に立ち返る、その意気や良し。

重要なおまけ：実はこの方法、超高速マクロの入り口です

この方法を探していた方は、
シートに入力する関数の作成も自動化したいとか、
関数の方が楽に書けるような処理をマクロでやりたいとか、
そういった需要と推測します。

が、この方法、簡単に書けるだけでなく、実は滅茶苦茶早いです。
　

For R = 2 To 7
    Cells(R, 4) = Cells(R, 2) * Cells(R, 3)
Next

こんな感じで普通にForを回すより速いのは当然なのですが、
　

' VLOOKUPをFormulaで
Range("D2:D7").Formula = "=VLOOKUP(A2,くだものリスト!A:B,2,FALSE)"

' VlookupをWorksheetFunctionで
For R = 2 To 7
    Cells(R, 4) = WorksheetFunction.Vlookup(Cells(R, 1), wsくだものリスト.Columns("A:B"), 2, False)
Next

↑このような、
「まったく同じ関数で戦う　Formula VS WorksheetFunction」の戦いでも、
Formulaが圧勝します。

理由を説明しますと、
1番大きいのが、「セルひとつひとつに入力していないから」です。

よく「配列にすると早い」と言われますよね。

あれは「配列でメモリ上で処理するから早い」というのもありますが、
「処理後に配列ごと一気にセルに戻している」のが、早さの一番の理由です。

配列に限らず、

' 100回かけて1を代入
For R = 1 To 100
   Cells(R, 1) = 1
Next

' 1回で1を代入
Range("A1:A100").Value = 1

↑のような単純な処理でも、後者が圧倒的に速いというのが、
配列を使った高速化のカラクリです。

今回のFormulaプロパティへの関数一括入力は、
すべてのセルに対して同時に入力されていますので、
配列でやったときに近い速度効果が得られるというわけですね。

この方法がチートなのは、
「配列などの高度な高速化に比べて、圧倒的にコーディング難易度が低い」ことです。

「わたしシート関数はわかりますが、VBAはそんなには…。」

くらいの人が、書けてしまいそうな難易度です。

そのうち検証記事を書くと思いますが、皆さんも使ってみてください。

和風スパゲティのレシピ

日本語でコーディングするExcelVBA

セル範囲にシート関数をコピーする - 基本構文とサンプルコード