どうでもいいプログラム研究所

とある編集者によるIT、Web、ソフトウェア、プログラミングに関する雑記と覚え書き

ワークシートのイベントプロシージャをアドイン化する方法【Excel VBA】

f:id:tdyu5021:20200919230157p:plain

Excel VBAで記述したマクロをどのExcelファイルからでも使えるために「アドイン(.xlam)」で保存する方法があります。ですが、シートモジュールのコードはアドイン化できません。そうなるとそこに記載したイベントプロシージャをアドイン化したい場合はどうすればよいでしょうか。不完全ながら暫定的な方法を発見したのでメモ代わりにまとめてみます。

ワークシートのイベントプロシージャとは

Excel VBAビギナーであれ、そこそこ慣れている方であれ、ワークシートのイベントプロシージャを使っている方は多いと思います。

ワークシートのイベントプロシージャとは何かを念のため説明すると、例えばワークシートやセルなどに対して何らかの操作が行われたことをトリガーに実行されるプロシージャのことです。通常は「シートモジュール」に記載されます。

以下の画面は、ワークシート上のセルなどの内容に変化があった場合、メッセージボックスを表示するマクロの例です。

f:id:tdyu5021:20200919231049p:plain

ワークシートのイベントプロシージャもアドイン化したい

このシートモジュールに書かれたイベントプロシージャはアドイン化することはできません。当然です。特定のシートモジュールに書かれているということは、コードがそのシートにだけしか適用されないからです。

ではどうすればワークシートのイベントプロシージャをどのExcelファイルからでも使えるようにできるでしょうか。実は私も100%の答えにたどり着けていないのですが、浅はかな知識なりに暫定的な方法を考えたので以下に紹介します。

私が思いついた方法

今回のアドイン化にあたって、一定の制約を設けています。

まず、あるExcelファイルを開いたとき、そのファイルに存在するすべてのワークシートでも、また開いたあとに追加したワークシートでも動くイベントプロシージャをつくるのは少々面倒なので、とりあえずExcel ファイルを開いたときの「アクティブなシートで」使えるようにするという方法に限定して考えます。

以下、その方法です。ここでは開くファイルををBook.xlsx、アドインのファイルをaddin.xlamと想定します。

  1. addin.xlamのクラスモジュールで、ワークシートのオブジェクトを生成しておき、そこでイベントプロシージャを記述しておく
  2. Excelファイル(ここではBook.xlsx)を開いたとき、上記の1で生成したワークシートオブジェクトのインスタンスとして、開いたExcelファイル(Book.xlsx)のアクティブシートオブジェクトを格納する
  3. 2で記した「ワークシートオブジェクトにExcelファイルのアクティブシートを格納する」というプロシージャをAuto_Openにして自動化する。

初心者の方やクラスモジュールを使われたことがない方は、上記を読んでもしかしたら「??」と思ったかもしれません。そのような方にご説明しておくと、実はイベントプロシージャはクラスモジュールに書くことができます。上記はそれを利用したものです。

クラスモジュールを利用されたことがない方であれば、「ユーザーフォームのイベントプロシージャだったらフォームモジュールに」、「ワークシートのイベントプロシージャだったらシートモジュールに」というふうに書いてきたかと思います。

ワークシートのイベントプロシージャをクラスモジュールに書く方法

話は少し脱線しますが、今回のテーマとなるコードを初心者の方でも理解できるように、そもそも「イベントプロシージャをクラスモジュールに書く」というのがどういうことなのかをここで説明しておきます。

そのため先述した

  1. addin.xlamのクラスモジュールで、ワークシートのオブジェクトを生成しておき、そこでイベントプロシージャを記述しておく
  2. Excelファイル(ここではBook.xlsx)を開いたとき、上記の1で生成したワークシートオブジェクトのインスタンスとして、開いたExcelファイル(Book.xlsx)のアクティブシートオブジェクトを格納する
  3. 2で記した「ワークシートオブジェクトにExcelファイルのアクティブシートを格納する」というプロシージャをAuto_Openにして自動化する。

こちらのステップが何を言っているかわかっている方はここは読み飛ばしてください。

ワークシートのイベントプロシージャの例

例えば、イベントプロシージャの例として、「シート上でセルを選択し直したら『選択セルを変更』というメッセージを表示する」というコードを考えてみましょう。Excel初心者の方でも10秒でかけるコードです。以下をシートモジュールに書くはずです。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    MsgBox "選択セルを変更"
End Sub

これをシートモジュールを使わずに書くと以下のようになります。標準モジュールとクラスモジュール(クラス名はデフォルトのClass1としておく)にそれぞれ以下のように記述します。

標準モジュール

Dim c As New Class1
Sub test()
    c.setting ActiveSheet
End Sub

クラスモジュール 

Public WithEvents sh As Worksheet
Sub sh_SelectionChange(ByVal Target As Range)
    MsgBox "選択セルを変更"
End Sub
Sub setting(ByVal s As Worksheet)
    Set sh = s
End Sub

もちろんこのコードを記述しただけではシート上のイベントプロシージャは発動しません。上記の中の”test”というプロシージャを実行すると、実行時のアクティブシートでイベントプロシージャが実行されるようになります。

ただ”test”という標準モジュールをいちいち実行してからでないとイベントプロシージャを利用できないのは煩わしいので、プロシージャ名のtestを“Auto_Open”にしておけば、ファイルを開いたときに自動でこのイベントプロシージャが利用できるようになります。

WithEventsキーワード

上記に記した、クラスモジュールを用いたイベントプロシージャ生成を可能するのが「WithEvents」キーワードです。これはクラスモジュール内のみで使用でき、ワークシートオブジェクト、ワークブックオブジェクト、ユーザーフォームオブジェクトなどなど、イベントプロシージャが用意されているオブジェクトの変数を宣言でき、それにイベントを割り当てられるというすぐれものです。

クラスを利用して、「ひな形」としてのワークシートオブジェクト(上記のサンプルでは"sh"という変数)を宣言しておいて、その後変数"sh"に、開いたExcelファイルのアクティブシートを格納した(=settingというメソッドに引数"activesheet"を渡した)という流れです。

こうしてアドインはでき上がる・・か?

ここまで来たらおわかりかもしれませんが、イベントプロシージャをアドイン化するアプローチとして、シートモジュールを使わずにクラスモジュール(と標準モジュール)にコードを記載すれば、今回の目的のものができ上がるだろうというのが私の狙いです。

なぜか動かない!その理由は?

ここまで来たらできるはず!と思い、上記の"test"のモジュール名を”Auto_Open”に変更した上で実際にこのファイルをxlam形式で保存。

そしてアドインを有効にしていざ試してみたのですが、その結果、うまくいきませんでした……

実行してもエラーにはならず原因がわかりません。通常のExcelファイルとして開くとうまくいくのに、アドインにするとなぜかうまくいかない。

正直、この理由がわからずずっと悩んでいたのですが、いろいろ調べるうちに動かなかった原因がようやく判明しました。その理由は単純なものでした。

標準モジュール内では以下のように、インスタンスを生成したオブジェクトにActiveSheetを格納するという処理を書いているわけですが、

Sub test()
    c.setting ActiveSheet
End Sub

どうやらExcelファイルを開くとき、「アドインファイルを読み込んだ時点では開いたExcelファイルのワークシートオブジェクトはまだ存在していない(開かれていない)」ようです。

なので、ここで記載した“ActiveSheet”は、開いたExcelのワークシートではなくどのワークブックのものでもないため、不明なオブジェクトになってしまうのです。

なので結論を言うと私の考えた上記の方法でアドイン化を行うのは無理であることがわかったのですが、対症療法的な措置として実現する方法がありました。

つまり、アドインを読み込んだ時点でまだ開いたExcelファイルのシートが存在しないなら、それが存在するのを待ってからアドインのコードを実行すればよいという対処方法です。

具体的にはAuto_Openのプロシージャの中に

c.setting ActiveSheet

記載するのでなく、それを別のプロシージャに書いておき、Auto_Openではそのプロシージャを時間差で実行するようにするという方法です。コードに書くと以下の通りです。

Dim c As New Class1
Sub Auto_Open()
    Application.OnTime Now + TimeSerial(0, 0, 2), "test"
End Sub
Sub test()
    c.setting ActiveSheet
End Sub

これは”test”というプロシージャをアドインファイルを読み込んでから2秒後に実行するというものです。このように変更したところ無事に新しく開いたExcelファイルでシートモジュールのイベントプロシージャが動きました。

ちなみにApplication.OnTimeメソッドは指定した時間にマクロを実行するためのものです。TimeSerial関数は、引数で指定した数字を時刻で示す関数です。上記の例だと、0,0,2なので0時0分2秒です。

最後に

上記でも運用上は問題ないかもしれませんがなんかすっきりしません。これをどうにかする方法はないのかなーと思っていたのですが、Twitter上で得た情報によるとワークシートのイベントプロシージャをアドイン化するれっきとした方法はあるらしいです。(具体的な方法は結局のところわかりませんが…)

気が向いたらもう少し研究してみようと思います。