【VBA】SUM関数でセルの合計を求める方法｜Range・Cellsの使い方

ExcelのVBAでも、ワークシートでおなじみのSUM関数を使って、セルに入力されている数値を合計できます。

VBAでSUM関数を使う方法は、大きく分けて次の2つです。

・合計した結果だけをセルに入力する
・セルに「=SUM(B3:B5)」のような数式を入力する

この記事では、まずは簡単なコードで2つの違いを確認します。

そのあとに、値が入力されている最初の行と最後の行を自動で取得して、合計する範囲が変わっても使えるコードを作成していきます。

この記事で分かることは以下になります。

・VBAでSUM関数を使う方法
・RangeやCellsで合計範囲を指定する方法
・合計する最初の行と最後の行を取得する方法
・FormulaとWorksheetFunctionの違い

VBAでSUM関数を使ってセルの合計を求める

VBAでSUM関数を使い、上記の画像の表を参考にB3からB5までの数値を合計する場合は次のように記述します。

使用するコード

Sub SumSample()

    Range("E3").Value = Application.WorksheetFunction.Sum(Range("B3:B5"))

End Sub

Application.WorksheetFunction.Sumの中に、合計したいセルの範囲を指定します。

今回はRange(“B3:B5”)と記述しているため、B3からB5までに入力されている数値が合計されます。

計算結果はRange(“E3”).Valueで指定しているE3へ入力されます。

B3からB5までの合計が28の場合、E3には28と表示されます。

同じ処理をCellsで記述すると、次のようになります。

Sub SumCellsSample()

    Cells(3, 5).Value = Application.WorksheetFunction.Sum( _
        Range(Cells(3, 2), Cells(5, 2)) _
    )

End Sub

Cellsは、セルの位置を行番号と列番号で指定します。

Cells(3, 2)はB3、Cells(5, 2)はB5です。

この2つをRangeで囲むことで、B3からB5までの範囲を指定しています。

ここからは、値が入力されている最初の行と最後の行を取得して、合計する範囲を自動で変更する方法を紹介していきます。

値の入っているセルの位置を取得する

SUM関数で計算するにはセルの位置を指定する必要があります。最初にセルの位置を取得して計算する範囲を決めましょう。

前提
上記の表B列の年齢を合計します。

値の入っているセルの最初の位置を取得する

StartCell = Cells(1, 2).End(xlDown).Row + 1

セルの開始位置を指定して下に向かって最初の値が入力されている位置を取得する構文です。

セルの位置は変数StartCellに代入して保持するようにします。

実際に変数StartCellに格納されている値をDebug.Printで確認してみます。

変数StartCellを確認する構文

Sub test()
   Dim StartCell As Long
    StartCell = Cells(1, 2).End(xlDown).Row + 1
   Debug.Print StartCell
End Sub

出力結果

出力結果が3と出ました。表からもわかる通り値が格納されている最初の行は表のヘッダーである”age”(2行目)です。その為、本来は2と出力されますがヘッダーを除外するため＋1としたため、3と出力されます。

これでヘッダー(2行目のage)を除いた最初の行番号を取得することができました。

注意

開始位置として指定するセルは空白である必要があります。指定したセルに値が入力されている場合には値の入っている最終行の位置を取得してしまいます。

値の入っているセルの最後の位置を取得する

今度は値のある最終行を取得していきます。構文は以下の通りです。

EndCell = Cells(Rows.Count, 2).End(xlUp).Row

セルの最終行を取得して上方向に値の入っているセルを確認していきます。

セルの位置は変数EndCellに代入して保持するようにします。

同様に変数EndCellに格納されている値をDebug.Printで確認してみます。

変数EndCellを確認する構文

Sub test()
Dim EndCell As Long
 EndCell = Cells(Rows.Count, 2).End(xlUp).Row
Debug.Print EndCell
End Sub

出力結果

出力結果が5と出ました。表の最終行は5行目のため、正確に取得できたことがわかります。

これで開始行と最終行がどちらも取得できました。

最終行や最終列取得の詳細は下記を参照

セルにワークシート関数を代入するFormulaの使い方

Formulaプロパティで計算式を代入していきます。

Cells(3, 5).Formula = 数式

数式を代入する位置を指定して、その指定した位置に対して数式を代入します。

数式の書き方

VBAのコードとしてのSUM関数

"=SUM(" & Cells(開始位置, 2).Address & ":" & Cells(終了位置, 2).Address & ")"

今回はSUM関数を利用するのですがほかの関数でも考え方は同じです。上記の開始位置・終了位置となるのは前章で値を取得したStartCellとEndCellです。

それぞれの変数を代入することでSUM関数が完成します。

上記ではわかりにくいと思いますのでとりあえず通常のSUM関数を見ていきます。

通常のSUM関数

=SUM(B3:B5)

＝SUMとセルの位置を囲う()カッコ、範囲を指定する：コロンは文字列となるので(””)ダブルクォーテーションで囲う必要があります。

そしてセルの位置であるB3とB5は実際のコードでは変数で指定して、 Addressプロパティを利用してセルのアドレスを取得します。

変数と文字列はVBAでは＆を利用することで結合できます

これらを書き換えることでVBAのコードとしての数式が完成します。

作成した数式を引数として指定したセルに代入することで計算が可能になります。

作成したコードで計算をしてみる

コードを作成して実行していきます。

Sub test()
    Dim StartCell As Long, EndCell As Long
        StartCell = Cells(1, 2).End(xlDown).Row + 1
        EndCell = Cells(Rows.Count, 2).End(xlUp).Row

        Do Until IsNumeric(Cells(StartCell, 2).Value)
          StartCell = StartCell + 1
        Loop
      
        Cells(3, 5).Formula = "=SUM(" & Cells(StartCell, 2).Address _
        & ":" & Cells(EndCell, 2).Address & ")"
End Sub

最初に、開始行を入れるStartCellと最終行を入れるEndCellを定義しています。

そのあとに、合計する最初の行と最後の行を取得しています。

Do Untilでは、取得したセルに数値が入力されているかを確認します。数値が見つかるまでStartCellに1を足して、次の行を確認していきます。

最後に数式をセルに代入しています。

出力結果

指定したセル(E3)に年齢の合計が出力されました。

SUM関数が代入されたセルは下記のように表示されます。

通常のSUM関数(ワークシート関数)を利用した場合と同じように表示されていることがわかります。

WorksheetFunctionでワークシート関数を使うこともできる

最初に紹介したWorksheetFunction.Sumを使って、合計する最初の行と最後の行を自動で変更してみましょう。

WorksheetFunctionを使用したサンプル
先ほど利用した表をWorksheetFunctionを利用した記述で計算していきます。

Sub test()

    Dim StartCell As Long, EndCell As Long
        StartCell = Cells(1, 2).End(xlDown).Row + 1
        EndCell = Cells(Rows.Count, 2).End(xlUp).Row

Cells(3, 5) = Application.WorksheetFunction.Sum(Range(Cells(StartCell, 2), Cells(EndCell, 2)))

End Sub

この場合も、E3に計算結果の28が入力されます。

Formulaを使用した場合と計算結果は同じですが、E3にSUM関数は入力されません。

FormulaとWorksheetFunctionの違い

同じ結果となるとどちらを使用するのが良いか迷うかと思います。特に理由がなければ今回ご紹介したformulaプロパティを使った記述方法がおすすめです。

おすすめな理由としては双方の値を出力する方法が異なるからです。

Formula

作成したワークシート関数を直接指定したセルに代入する記述方法です。通常のワークシート関数同様に指定範囲内の値が変わるたびに再計算されます。

WorksheetFunction

ワークシート関数をVBA内で利用する記述方法です。今回の例ではコード内でSUM関数を利用して計算結果のみ(28)をセルに代入しています。

そのため、指定範囲内の値が書き換わっても数式を代入したわけではないため再計算は行われません。