ユーザー定義関数で解決！

先の VBA の話の実践適用例です。

例えば、「シート1」、「シート2」、…「シート10」までの 10個のワークシートにデータが入っていて、これを集計・操作した「サマリー」シートを作ることを考えてみます。 このような場面はよくあることではないかと思うのですが、ここで困るのがシートの参照の仕方です。 ご存知のように「シート1」のセル A1 は以下のように参照します。

シート1!A1

このようにワークシートはシート名で参照するので、素直に作業を進めるとワークシートのタブから名前をコピーしてセルに貼り付けるような操作が必要になってしまうわけです。 列は A、B、C、…、行は 1、2、3、… と参照できますが、ワークシートについては名前でしか参照できず、残念ながら「N番目のワークシートを参照する」みたいなワークシート関数も存在しません。

そこをもう少し表計算ソフトらしく処理するために「ワークシート名を取り出す処理」を考えようということになるのですが、それも簡単ではありません。 例えば、こちらにその方法が丁寧に説明されていますが、何だかなあ、という感じです。

こんなとき VBA を使って自作関数を作ることができれば、以下のような関数を作って呼べば済みます。

Function GetWorkSheetNameByIndex(i)
  GetWorkSheetNameByIndex = Worksheets(i).Name
End Function

1枚目のシートの名前は「=GetWorkSheetNameByIndex(1)」、2枚目は「=GetWorkSheetNameByIndex(2)」というように呼び出して使うことができます。 このようにワークシート関数だけでやろうとすると複雑になってしまう処理でも VBA を使えばシンプルでわかりやすくなる場合があります。

VBA ユーザー定義関数の再計算

ここで気をつけねばならないのですが、VBA で作成したユーザー定義関数は F9 を押しても再計算されません。 この問題に対処するために Application.Volatile という関数が用意されていて、これを GetWorkSheetNameByIndex の中で呼ぶとセル修正のたびにこの関数の呼び出しが再計算されるようになります。

しかし、どこかのセルを修正する度に再計算されることになるので、Application.Volatile を利用するかどうかは慎重に検討すべきです。 先の例ではサマリーシートを作成する段階であればデータ用シート名をそうそう頻繁には変えないでしょうから、わざわざ Application.Volatile を呼ばなくても関数を呼び出しているセルを編集し直して再計算という方法で十分でしょう。

おまけ

まあ、「同じ位置のセルを単純集計する」程度の話であれば、ユーザー定義関数を作らなくても 3-D 集計を知っておくだけで済むかも知れません。