【Excel VBA】セルに関数を入れる方法!複数セルへ一括挿入も!

Excel VBAでセルに関数を入れたいときはないでしょうか。

けど、そんな中で悩むことは、

・Excel VBAでセルに関数を入れるやり方がわからない。
・Excel VBAで複数のセルに一括で関数を入れがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する

・セルに関数を入れるVBAの基本
・複数セルに一括で関数を入れるVBA3種類

についてまとめます!

Excel VBAでセルに関数を入れる完成イメージ

Excel VBAでセルに関数を入れる完成イメージについて説明します。

まず、VBAで1つのセルを対象にSUM関数入れます。

A1とB1で値を手入力し、

SUM関数はVBAで自動挿入します。

次にVBAで複数のセルに対して関数を一括で入れていきます。

方法は2(+1)パターンあり、それぞれ説明をします。

それではさっそくやってみましょう。

セルに関数を入れるFormulaプロパティについて

セルに関数を入れるためには、この値は数式/関数ですよということを明示する必要があります。

その設定はFormulaプロパティを使います。ちなみに「Formula」は式や公式の意味がありますので、読んで字のごとくですね。

タカヒロ
タカヒロ
セル代入の値の先頭に「=」を入れればセル挿入時点で関数入りの数式であるとExcel側で判断しますので、一応Formulaを使わなくてもできます。

Formulaプロパティの構文

使い方はカンタンで、RangeオブジェクトにFormulaプロパティを指定するだけです。

Range(“<挿入先セルA1値>”).Formula = “<数式>”
Cells(“<挿入先セル番号>”).Formula = “<数式>”

セルに関数を入れるVBA

セルに関数を入れるVBAを実装していきましょう。

A1:B1の数値合計をC1セルへ算出するSUM関数を設定する内容となります。

以下サンプルコードです。

Sub セルにSUM関数を入れる_Range版()
'Rangeプロパティ指定の場合
Range("C1").Formula = "=SUM(A1:B1)"
End Sub
Sub セルにSUM関数を入れる_Cells版()
'Cellsプロパティ指定の場合
Cells(1, 3).Formula = "=SUM(A1:B1)"
End Sub

A1とB2に適当な数値をいれ、

VBAを実行します。

はい、数式が挿入されましたね。

複数セルに一括で関数を入れるVBA(AutoFillメソッド)

次にVBAで複数のセルに対して一括で関数を挿入してみます。
C1セルへ入力されているSUM関数を5行目までコピーしていきます。

まず、一番カンタンな方法である連続データを作成するAutoFillメソッドを利用する方法について説明をします。

AutoFillメソッドの構文

AutoFillメソッドの構文は以下の通りです。

Range(“<挿入先セルA1値>”).AutoFill Destination:=Range(“<コピー範囲>”), Type:=xlFillDefault
Cells(“<挿入先セル番号>”).AutoFill Destination:=Range(“コピー範囲”), Type:=xlFillDefault

セルを指定するRangeオブジェクトにAutoFillメソッドを適用する使い方になります。
Typeは標準のオートフィル「xlFillDefault」を指定します。

タカヒロ
タカヒロ
なお、連続データを示す「xlFillSeries」、コピーを示す「xlFillCopy」 を指定しても同様の結果を得ることができます。

以下サンプルコードです。

Sub 複数セルに一括で関数を入れる_AutoFillメソッド()

    Range("C1").Formula = "=SUM(A1:B1)"
    Range("C1").AutoFill Destination:=Range("C1:C5"), Type:=xlFillCopy

End Sub

コード、めちゃくちゃシンプルですね。
Cells版にする場合はRange(“C1”)をCells(1, 3)へ変更してください。

Sub 複数セルに一括で関数を入れる_AutoFillメソッド()

    Cells(1, 3).Formula = "=SUM(A1:B1)"
    Cells(1, 3).AutoFill Destination:=Range("C1:C5"), Type:=xlFillCopy

End Sub

A1~B5まで値を手入力し、

VBAを実行します。

はい、一括で数式が挿入されましたね。

複数セルに一括で関数を入れるVBA(For Nextステートメント)

次はFor Nextステートメントで処理するベーシックな方法について説明します。

サンプルコードは以下の通りです。

Sub 複数セルに一括で関数を入れる_ForNextステートメント()
    Dim n As Integer
    
    For n = 1 To 5
        Cells(n, 3).Formula = "=SUM(A" & n & ":B" & n & ")"
    Next n

End Sub

前に比べるとごちゃついていますが、処理件数を指定したり、小回りが効くかと思います。
またおなじみFor Nextステートメントなので構文を覚えていなくても使える気楽さがあります。

VBAを実行します。

はい、こちらも一括で数式が挿入されましたね。

<追加>複数セルに一括で関数を入れるVBA(セル範囲指定をしたRangeオブジェクトへ数式を指定)

読者様よりコメント頂きました、セル範囲指定をしたRangeオブジェクトへ数式を指定し、一括で関数を入れる方法について追記します。

サンプルコードは以下の通りです。

Sub 複数セルに一括で関数を入れる_セル範囲Rangeへ数式指定()
    Range("C1:C5").Formula = "=SUM(A1:B1)"
End Sub

セル範囲を指定したRangeオブジェクトのFormulaプロパティに先頭セルの数式を指定すると、以降のセルには
AutoFillメソッドと同じように、数式がコピーされ挿入されていきます。

タカヒロ
タカヒロ
コードが少なく済むのがよいですね。
ご教示ありがとうございます。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、

・セルに関数を入れるVBAの基本
・複数セルに一括で関数を入れるVBA3種類

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








2 件のコメント

  • Range(“C1:C5”).Formula = “=SUM(A1:B1)”
    の一行だけでよいのでは( ^ω^)・・・?

    • ご連絡ありがとうございます。

      ご教示頂いた方法で同様の結果が得られることが確認できました。
      大変効率がよい書き方でとても参考になりました。
      (恐縮ながら、記事にも追記させて頂きました。)

      他にもよいコードの書き方や改善点などあればコメント頂けますと幸いです。
      よろしくお願いいたします。

  • コメントを残す

    メールアドレスが公開されることはありません。 * が付いている欄は必須項目です