Home » エクセルマクロ・Excel VBAの使い方 » WorksheetFunction » WorksheetFunction.Sumでアクティブでない別シートの合計を

WorksheetFunction.Sumでアクティブでない別シートの合計を

対象：Excel2007, Excel2010, Excel2013, Windows版Excel2016

「excelvba worksheetfunction.sum 別のシート」
「worksheetfunction sum 別のシート range(cells」
といった検索で、このサイト・インストラクターのネタ帳へのアクセスが時折あります。

WorksheetFunctionオブジェクトのSumメソッドに関する、

定番の疑問です。

WorksheetFunction.Sumを使って、アクティブではない別のワークシートの合計を求めるには、どういう書き方をすればいいのか探していた方による検索です。

［スポンサードリンク］

Rangeプロパティの引数に文字列を指定してアクティブでない別シートの合計を計算するサンプルマクロ

以下のマクロを実行すると「シート1」という名前のシートのA1:A10セルの合計がメッセージボックスに表示されます。

Sub アクティブでないシートの合計_Range_文字列指定()

　MsgBox _
　　WorksheetFunction.Sum(Sheets("シート1").Range("A1", "A10"))

End Sub

シートの指定をせず、
　MsgBox _
　　WorksheetFunction.Sum(Range("A1", "A10"))
とした場合は、アクティブシートのA1:A10セルの合計が計算されます。

今回はアクティブになっていない別のワークシートの合計なので、Rangeプロパティの上位オブジェクトであるWorksheetオブジェクトから指定して、
　　WorksheetFunction.Sum(Sheets("シート1").Range("A1", "A10"))
としています。

上記のマクロの
　　WorksheetFunction.Sum(Sheets("シート1").Range("A1", "A10"))
については、特に難しくはありません。

厄介なのは、
「worksheetfunction sum 別のシート range(cells」
という検索をなさった方のケースです。

Rangeの引数にCellsを指定してアクティブでない別シートの合計を計算しようとしてエラーになるサンプルマクロ

先のマクロと同じつもりで、以下のようなマクロを書いた場合は実行時エラーが発生します。

Sub アクティブでないシートの合計_Range_Cells_エラーになる()

　MsgBox _
　　WorksheetFunction.Sum(Sheets("シート1").Range(Cells(1, 1), Cells(10, 1)))

End Sub

Rangeプロパティの2つの引数には、先のマクロのように文字列も指定できますが、Rangeオブジェクトを返すオブジェクト式も指定できます。

ところが、
　　WorksheetFunction.Sum(Sheets("シート1").Range("A1", "A10"))
ならOKだったのに、
　　WorksheetFunction.Sum(Sheets("シート1").Range(Cells(1, 1), Cells(10, 1)))
とした場合には、実行時エラーが発生してしまいます。

「worksheetfunction sum 別のシート range(cells」
という検索をなさった方は、この書き方をしていたに違いありません。

Rangeの引数にCellsを指定してアクティブでない別シートの合計を計算するサンプルマクロ

どう書けばいいかというと、以下のとおりです。

Sub アクティブでないシートの合計_Range_Cells()

　MsgBox _
　　WorksheetFunction.Sum(Sheets("シート1").Range(Sheets("シート1").Cells(1, 1), Sheets("シート1").Cells(10, 1)))

End Sub

先ほどエラーになった、
　　WorksheetFunction.Sum(Sheets("シート1").Range(Cells(1, 1), Cells(10, 1)))
というオブジェクト式の、
　　WorksheetFunction.Sum(　
の引数に指定されている
　　　　Sheets("シート1").Range(
の2つの引数、
　　　　　　Cells(1, 1)　と　Cells(10, 1)
の前にそれぞれ、
　　　　　　Sheets("シート1").
という「シート1」シートを取得するオブジェクト式が追加されています。

それが、
　　WorksheetFunction.Sum(Sheets("シート1").Range(Sheets("シート1").Cells(1, 1), Sheets("シート1").Cells(10, 1)))
という、長いオブジェクト式です。

Rangeプロパティも、Cellsプロパティも、上位のオブジェクト式を省略した場合は、アクティブなシートのRangeプロパティやCellsプロパティが指定されたと解釈されます。

ですから、Rangeプロパティの前に「Sheets("シート1").」を書くだけでなく、2つのCellsプロパティのそれぞれの前にも「Sheets("シート1").」を書かなければなりません。

Rangeの引数にCellsを指定してアクティブでない別シートの合計を計算する際にオブジェクト変数・Withを使ったサンプルマクロ

RangeにもCellsにも「Sheets("シート1").」を書く、
　　WorksheetFunction.Sum(Sheets("シート1").Range(Sheets("シート1").Cells(1, 1), Sheets("シート1").Cells(10, 1)))
というオブジェクト式は、さすがに長く読み辛いので、オブジェクト変数やWithキーワードを使うほうがいいでしょう。

オブジェクト変数を使う場合は以下のとおりです。

Sub アクティブでないシートの合計_Range_Cells_オブジェクト変数()

　Dim sh As Worksheet

　Set sh = Sheets("シート1")
　MsgBox _
　　WorksheetFunction.Sum(sh.Range(sh.Cells(1, 1), sh.Cells(10, 1)))

End Sub

Withキーワードを使う場合は、以下のとおりです。

Sub アクティブでないシートの合計_Range_Cells_With()

　With Sheets("シート1")
　　MsgBox _
　　　WorksheetFunction.Sum(.Range(.Cells(1, 1), .Cells(10, 1)))
　End With

End Sub

［スポンサードリンク］