3D集計・3D参照する数式を入力するExcelマクロ

「3D集計する数式をマクロから入力するにはどうしたらいいのでしょうか？」
といった、Excel VBA（Visual Basic for Applications）に関するご質問をいただくことがあります。

例えば、4月から9月までの月ごとの6枚のワークシートが並んでいるときに、集計用シートに、
「=SUM('4月:9月'!A1)」
という3D参照した数式を入力すれば、

「4月」シートから「9月」シートまでのA1セルの値の合計を計算できます。

この数式を入力するマクロをどのよう書けばいいのか、疑問を持った方からいただくご質問です。

実際に使うマクロではいろいろな要望がありそうですが、ここでは参考になりそうなマクロを2つご紹介しておきます。

集計用シートが後ろにある場合に3D参照する数式を入力するサンプルマクロ

一つ目は、3D参照する集計用シートが、集計元となる個々のシートより後ろにある場合のサンプルマクロです。

上記のマクロを実行すると、1枚目（先頭）のシートから、アクティブシートの左隣のシートまでを3D参照して合計を求める数式が、アクティブなセルに入力されます。

ここでは、合計する元のセルアドレスをアクティブなセルと同じにしています。

数式を入力しているのは、最後の、
　ActiveCell.Formula = _
　　"=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"
の部分です。

変数を使わなくても書くことはできますが、そうすると、かなり長い一行になってしまいますので、事前に変数へ各種値を代入するようにしました。

アクティブセルのアドレスを$マークのついていない相対参照の形で、変数・addrに代入しています。
　addr = ActiveCell.Address(False, False)

1枚目のシートの名前は、変数・ws_aに代入しています。
　ws_a = Sheets(1).Name

集計する最後のシート（この例では左隣のシート）の名前を、変数・ws_zに代入しています。
　ws_z = ActiveSheet.Previous.Name
WorksheetオブジェクトのPreviousプロパティで、左隣のシートを取得できるのがポイントでしょうか。

最後に上記の3つの変数を使って、アクティブセルに数式を入力しています。
　ActiveCell.Formula = _
　　"=SUM(" & ws_a & ":" & ws_z & "!" & addr & ")"

集計用シートが前にある場合に3D参照する数式を入力するサンプルマクロ

二つ目のマクロは、3D参照する数式の入力される集計用のシートが、集計元となる個々のシートより前にある場合のサンプルです。

上記のマクロを実行すると、右隣のシートから、最後のシートまでを3D参照して合計を求める数式が、アクティブセルに入力されます。

変数・addrは先のマクロと同じです。
　addr = ActiveCell.Address(False, False)

3D参照するシート名を取得する部分が、先のマクロとは異なっています。

このマクロでは、先頭のシートをアクティブシートの右隣のシートとしていますから、変数・ws_aには右隣のシート名を代入しています。
　ws_a = ActiveSheet.Next.Name
WorksheetオブジェクトのNextプロパティで、右隣のシートを取得することができます。

最後のシートまで集計するので、変数・ws_zには最後のシートの名前を代入しています。
　ws_z = Sheets(Sheets.Count).Name
Sheetsコレクションオブジェクトのデフォルトプロパティの引数に、シートの枚数（Sheets.Count）を指定することで、最後のシートを取得しています。