Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

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

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

www.excellovers.com

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

完成形

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

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

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


コード

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

標準モジュール

Dim myApp As New EventGet  
  
Sub DoubleClickSetColor(ByVal control As IRibbonControl)
     Set myApp = New EventGet
     Set myApp.setWS = ActiveSheet
End Sub
Sub DoubleClickSetColorOff(ByVal control As IRibbonControl)
     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」の2つを作成します。
「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」をクリックした時のアクティブシートの情報とダブルクリックをしたシートの情報を突き合わせて、「両者のブック名-シート名」が一致した場合のみダブルクリックイベントの処理を実行しています。
これは想定外のシートでの処理を防ぐための対応です。

アドイン化

ここまででコードの入力は完了しました。
次にこのコードをアドイン化します。

「名前を付けて保存」でファイルの種類を「Excelアドイン」に設定します。
そうすると上記のファイルの保存場所が自動的にアドイン保存用のフォルダに変更されます。
f:id:ExcelLover:20210123164228j:plain

後は任意の名前を指定して保存してください。

アドイン有効化

次にアドインを有効化します。
アドインとして保存しただけでは使用できず、有効化する必要があります。

リボンから開発→Excelアドイン
開いたアドインメニューから先程保存したアドインにチェックを入れて保存 f:id:ExcelLover:20210123164304j:plain

以上で使用する準備が整いました。

実際にマクロを使用する際は、ユーザー設定のツールバーを新規作成して登録するパターン
f:id:ExcelLover:20210123164338j:plain

アドイン自体にリボンに登録する設定を登録するパターン
こちらの記事を参照ください。

www.excellovers.com


まとめ

以上で「セルをダブルクリックで色塗り」マクロをどのブック・シートでも気軽に使用できるようになりました。
せっかく自作した機能なので、いつでもどこでも使えると便利ですね。