Application.WorksheetFunctionで他ブックやシートを参照する方法

これも単純だけど結構悩んだのでメモ。

VBAのApplication.WorksheetFunctionはとても便利で、VBAでエクセルのワークシートにある標準関数を呼び出すことができる魔法の呪文！

今回示すのは読み込むブック・シートと書き込むブック・シートが違う場合の記述。

たとえばこのようなデータを持つブックのシート（A.xlsのsheet1）があったとして、B2とB3の合計を別のブックのシート（B.xlsのsheet2）に表示させたいとする。
このくらい単純で簡単なものはさっさと同じシートでやれ、という感じだけれども、データが増えると他のブックやシートに表示させたいこともあるので。（※データは週末に行われていた2012年B-1グランプリin北九州さんの公式発表を勝手に使わせていただきました。すみません。）

通常、同じシートに表示させたい場合は、

Range("B4")=Application.WorksheetFunction.Sum(Range("B2:B3"))

というように記述。
←セル("B4")に合計来場者数が表示される

それを、

Workbooks("B.xls").Worksheets("sheet2").Range("A2")=Application.WorksheetFunction.Sum(Workbooks("A.xls").Worksheets("sheet1").Range("B2:B3"))

とすればよい。もちろんブックやシート名を変数にしてもOK。
←A.xlsのsheet1から読み込んで
←B.xlsのsheet2へ出力

元データのB2を基準（例えば、今回はdpという変数名を使って、Set dp = Range("B2")) とした相対パスでB2とB3の合計を求める場合は、

Dim dp As Object
Set dp = Workbooks("A.xls").Worksheets("sheet1").Range("B2") 

Workbooks("B.xls").Worksheets("sheet2").Range("A2")=Application.WorksheetFunction.Sum(Workbooks("A.xls").Worksheets("sheet1").Range(dp.Cells(1, 1).Address & ":" & dp.Cells(2,1).Address))

とする。

ここで注意するのは関数のなかで計算をさせたいRangeのAddressをとっても、複数のブック・シートをまたいでいる場合、どのブックにあるシートのAddressなのかわからなくなる点。つまりApplication.WorksheetFunction.ぴよぴよ()のなかでも計算処理をかける元データのブック・シートを明示しておかねばならない。

ちょっと日本語がややこしくなったけれど、結局は単純で、

「ブック名．シート名．セル名」を常に明示させるということ。

基本的なことだけど、かなり悩んでしまったので次回からは気をつけていきたい。