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

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

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

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

ですよね。

今回はそんなお悩みを解決する
Excel VBAでセルに関数を入れる方法について
まとめます!


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

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

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

A1とB1で値を手入力し、

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

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

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

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



セルに関数を入れる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(For Nextステートメント)

最終行が変動的であれば、最終行を取得できるおなじみの公式、

Cells(Rows.Count,<列番号>).End(xlUp).row

を使ってみても良いでしょう。

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

Sub 最終行取得の上複数セルに一括で関数を入れる_ForNextステートメント()
    Dim n As Integer
    
    For n = 1 To Cells(Rows.Count, 1).End(xlUp).row
        Cells(n, 3).Formula = "=SUM(A" & n & ":B" & n & ")"
    Next n

End Sub

実行結果はこれまでと同様です。



VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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


さいごに

いかがでしょうか。

今回は、
Excel VBAでセルに関数を入れる方法について
まとめました。

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



コメントを残す

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