自作マクロイベント機能をいつでもどこでも使えるようにする方法

前回はダブルクリックで色を塗りたいブックに直接マクロを作成したが、これではこの機能を使いたくなった時に毎回ブックにコードを記載する必要があり手間である。

www.excellovers.com

そこで自作アドインに上記の機能を取り込み、ボタン１つで使用できるようにしてみようと思う。

完成形
コード
アドイン化
アドイン有効化
まとめ

完成形

さっそくだが完成形を見て頂きます。
f:id:ExcelLover:20210123163245j:plain

このようにリボンの中にメニューが表示されています。
「DoubleClickSetColor」をクリックすると前回紹介したシートをダブルクリックすると色を塗るワークシートイベントマクロが有効になります。
「DoubleClickSetColor」ボタンをクリックした時にアクティブ状態のシートが対象になります。

「DoubleClickSetColorOff」をクリックするとワークシートイベントマクロが無効になります。「DoubleClickSetColor」クリック後、別のシートがアクティブ状態で再度「Dクリック色塗り」クリックすると、後で選択したシートでのみイベントが発生します。

コード

今回は普段コードを記入する標準モジュールとは別に「EventGet」クラスを作成しました。

標準モジュール

'当初2つ共に引数に「ByVal control As IRibbonControl」指定していたが削除
Dim myApp As New EventGet  
  
Sub DoubleClickSetColor()
     Set myApp = New EventGet
     Set myApp.setWS = ActiveSheet
End Sub
Sub DoubleClickSetColorOff()
     Set myApp = Nothing
End Sub

EventGetクラス

Public WithEvents myApps As Application
Private TargetWS As Worksheet

Private Sub myApps_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
     If TargetWS.Parent.FullName & "-" & TargetWS.Name = Sh.Parent.FullName & "-" & Sh.Name Then
          With Target.Interior
               If .ColorIndex = xlNone Then
                    .Color = 49407
               Else
                    .Color = xlNone
               End If
          End With
          Cancel = True
     End If
End Sub

Public Property Set setWS(ws As Worksheet)
     Set myApps = Application
     Set TargetWS = ws
End Property

標準モジュールの記載

標準モジュールには「DoubleClickSetColor」、「DoubleClickSetColorOff」の２つを作成します。
「DoubleClickSetColor」でクラスインスタンスを作成してイベントの補足を開始、
「DoubleClickSetColorOff」でクラスインスタンスを破棄してイベントの補足を中止します。

「DoubleClickSetColor」を開始したときのアクティブシートをクラスに渡しています。
これについてはクラスの項で説明します。

クラスの作成

まずEventGetクラスを作成します。
上部メニューから「クラスモジュール」を選択してください。
f:id:ExcelLover:20210123163353j:plain

挿入されたクラスの名前をEventGetに変更。クラスをダブルクリックしてクラスのコード入力画面を開きます。
f:id:ExcelLover:20210123163424j:plain

まずクラスの冒頭に「Public WithEvents myApps As Application」を記載してください。
その後は下記の作業でイベントの雛形を設定します。 f:id:ExcelLover:20210123163459g:plain

最初に「Public WithEvents myApps As Application」を記載しておかないと、オブジェクトボックスの選択の際にmyAppsが表示されません。
入力された雛形に上記のコードを入力してください。

上記のコード全てをコピペでも、もちろん大丈夫です。

上記の作業で入力された「myApps_NewWorkbook」のイベントは削除しても大丈夫です。

WithEvents

アドインでイベントを補足するにはWithEventsキーワードを使用します。

Private | Public WithEvents 変数名 [as タイプ]

アドインでイベントを補足する場合にはタイプにApplicationを指定します。

これでEventGetクラスのインスタンスが作成されExcelが開いている間は、どのブックのどのシートのどのイベントでも補足することが出来ます。

今回はダブルクリックを補足したいので「SheetBeforeDoubleClick」を選択しました。

SheetBeforeDoubleClick

オブジェクトにApplicationを選択した場合にダブルクリックを補足する場合は「SheetBeforeDoubleClick」を使用します。

Private Sub オブジェクト変数_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

引数「Sh」にはダブルクリックしたシート(オブジェクト)、引数「Target」にはクッリクしたセル(Rangeオブジェクト)の情報が格納されています。

標準モジュールの項で紹介した「DoubleClickSetColor」をクリックした時のアクティブシートの情報とダブルクリックをしたシートの情報を突き合わせて、「両者のブック名-シート名」が一致した場合のみダブルクリックイベントの処理を実行しています。
これは想定外のシートでの処理を防ぐための対応です。