VBA Sum関数(範囲、列、その他)

このチュートリアルでは、VBAでExcelのSum関数を使用する方法について説明します。

sum関数は、最も広く使われているExcel関数の1つであり、おそらくExcelユーザーが最初に使い方を覚える関数でしょう。VBAにはこれに相当するものはなく、WorksheetFunctionオブジェクトの組み込みExcel関数を使わなければなりません。

ワークシート関数の和

WorksheetFunctionオブジェクトは、Excelの「関数の挿入」ダイアログボックス内で利用可能なほとんどのExcel関数を呼び出すために使用することができます。SUM関数もその一つです。

Sub TestFunction
  Range("D33") = Application.WorksheetFunction.Sum("D1:D32")
End Sub

SUM関数には、最大30個の引数を指定することができます。各引数は、セルの範囲を参照することもできます。 以下の例では、セルD1からD9までを足し算します。

Sub TestSum()   
   Range("D10") = Application.WorksheetFunction.SUM("D1:D9")
End Sub

以下の例では、D列の範囲とF列の範囲を足し算します。Applicationオブジェクトは省略することもできます。

Sub TestSum()
   Range("D25") = WorksheetFunction.SUM (Range("D1:D24"), Range("F1:F24"))
End Sub

セルの範囲が1つの場合は、セルを表す式にRangeを使用しなかった場合にも、指定されているのと同様に扱われていることに注意してください。しかし、複数の引数を使用する場合は、明示的に指定する必要があります。

Sumの結果を変数に代入する

計算式の結果を直接Excelの範囲に書き戻すのではなく、コード内の他の場所で使いたい場合があります。そのような場合は、結果を変数に代入して、後でコードの中で使用することができます。

Sub AssignSumVariable()
   Dim result as Double
'変数に代入
   result = WorksheetFunction.SUM(Range("G2:G7"), Range("H2:H7"))
'結果を表示する
  MsgBox "この範囲の合計は " & result & " です"
End Sub

Rangeオブジェクトの合計

Rangeオブジェクトにセル群を代入して、そのRangeオブジェクトをWorksheetFunctionオブジェクトで使用することができます。

Sub TestSumRange()
   Dim rng As Range
'セル範囲を代入する
   Set rng = Range("D2:E10")
'数式で範囲を使用する
   Range("E11") = WorksheetFunction.SUM(rng)
'範囲オブジェクトを解放する
  Set rng = Nothing
End Sub

複数のRangeオブジェクトの合計

同様に、複数のRangeオブジェクトを合計することができます。

Sub TestSumMultipleRanges() 
   Dim rngA As Range 
   Dim rngB as Range
'セル範囲を代入する 
   Set rngA = レンジ("D2:D10") 
   Set rngB = レンジ("E2:E10")   
'数式で範囲を使用する 
Range("E11") = WorksheetFunction.SUM(rngA, rngB)
'レンジオブジェクトを解放する
  Set rngA = Nothing 
  Set rngB = Nothing
End Sub

列または行全体の合計

また、Sum関数を使って、列全体や行全体を足し算することもできます。 以下のプロシージャは、D列のすべての数値セルを足し算します。

Sub TestSum()    
   Range("F1") = WorksheetFunction.SUM(Range("D:D"))
End Sub

一方、以下のプロシージャは、9行目の数値セルをすべて足し算します。

Sub TestSum()     
   Range("F2") = WorksheetFunction.SUM(Range("9:9")) 
End Sub

配列の和

WorksheetFunction.Sumを使えば、配列の中の値を足し算することも可能です。

Sub TestArray()
   Dim intA(1 To 5) As Integer
   Dim SumArray As Integer
'配列に入力する
   intA(1) = 15
   intA(2) = 20
   intA(3) = 25
   intA(4) = 30
   intA(5) = 40
'配列を足し算して結果を表示する
   MsgBox WorksheetFunction.SUM(intA)
End Sub

SumIf関数の使用

もう一つよく使われるワークシート関数に、SUMIF関数があります。

Sub TestSumIf()
  Range("D11") = WorksheetFunction.SUMIF(Range("C2:C10"), 150, Range("D2:D10"))
End Sub

上記のプロシージャは、Range(D2:D10)のセルのうち、C列の対応するセルが150であるもののみを足し算するものです。

数式の和

WorksheetFunction.SUMを 使用してワークシートのセルに合計を入れる場合、数式ではなく、値が使われます。つまり、Excelの表中の数値が変わっても、WorksheetFunctionが返した値は変わりません。

上の例では、プロシージャTestSumがRange(D2:D10)を足し算して、その結果をD11に入れています。数式バーを見ればわかるように、この結果は数式ではなく、数値です。

そのため、Range(D2:D10)内のいずれかの値が変化しても、D11の結果は変わりません。

WorksheetFunction.SUMを使う代わりに、VBAでFormulaや FormulaR1C1メソッドを使って、セルにSum関数を適用することができます。

Formula メソッド

以下のように、D2:D10 のようなセル範囲を指定することができます。

Sub TestSumFormula
  Range("D11").Formula = "=SUM(D2:D10)"
End Sub

FormulaR1C1メソッド

FromulaR1C1メソッドは、セルの範囲を限定しない点で、より柔軟性があります。以下の例では、上の例と同じ答えが得られます。

Sub TestSumFormula()
  Range("D11").FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

しかし、より柔軟な計算式にするために、次のようなコードに修正することも可能です。

Sub TestSumFormula() 
   ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
End Sub

ワークシートのどこにいても、数式はその真上の8つのセルを足し算して、答えをActiveCellに表示します。SUM関数内のRangeは、行（R）と列（C）の構文を使用して参照する必要があります。

これらの方法によって、VBAの中で動的にExcelの数式を使用することができます。

D11の中身は、値ではなく数式となります。