お道具箱を常駐させる (追記あり)

今日はお天気で暖かいですねぇ。

こんちくわ壁|ω・)ﾉ

さて今回の記事は、　　

右クリコマンドバーに登録したコマンドを常にそこに、登録用マクロ起動のF5ポチなしに、そこにあるようにしましょう～。なオハナシです。

でわいきます￣▽￣)ﾉ

Contents

前回の記事では・・
で、早い話どうするの？
個人用マクロブックって？ナニ
個人用マクロブックをどう使うの？
まだ完成でわありません。
まとめ
因みに

前回の記事では・・　

以下のことを紹介しました

CommandBars("Cell").Controls.Add(Type:=msoControlButton)でコマンドバーにマクロを登録できる
引数 Type:=msoControlPopup に変えたControl.Add()メソッドと組み合わせることでコマンドに階層構造を作れる
作った階層先にカスタムマクロ起動コマンドをまとめてしまおう
右クリコマンドバー登録サイコー

fukucyndip.hatenablog.com

ただ、

　　　マクロをいちいち起動することが、たるい・・

という本来の動機を考えると、コマンドバーに登録できるコードが手に入っても、その起動がたるいわけで、起動の操作なく、空気のように常駐させないと本質的に何の問題解決に至っていないということに気が付いてしまったのでした。

どっかの毒に詳しい女性剣士が言っていたことをふと思い出しました・・

　　　常駐はできて当たり前なんですけど、できないんだったら仕方ありませんねぇ

・・・今回の記事では認めてもらえるよう (誰に?) 常駐を実現させる方法を紹介します

で、早い話どうするの？

それわ、

　　 個人用マクロブックに右クリコマンドバー登録マクロを仕込む

ということです。
いきなりの結論ですが、思いついてしまえば実に簡単でした。

個人用マクロブックって、ナニ?

皆さんのVBEのプロジェクトエクスプローラーにこんなの出てませんか？

f:id:FukuCyndiP:20210131191027p:plain

(フォーム、クラスモジュールのフォルダは別途追加したものです)

　　　　うんにゃ、そんなものないよ・ω・)

と、いう方は何でもいいので、マクロの記録をやってみてください。

そうすると、下のウインドウが現れますので、マクロの保存先を "個人用マクロブック" してマクロの記録を実行すれば、自動的に作成されます。

f:id:FukuCyndiP:20210131191145p:plain

で、この個人用マクロブックは一度作ってしまえば、エクセルを起動するたびにバックグラウンドで立ち上がってくるようになります。筆者は頻用するコードの保管庫みたいな感じで利用しています。

個人用マクロブックをどう使うの？

個人用マクロブックの ThisWorkbook (ブックモジュール) の Workbook_Openイベントに右クリコマンドバー登録マクロを起動するコードを仕込みます。

f:id:FukuCyndiP:20210131191242p:plain

上に書いたように、個人用マクロブックはエクセルを起動する度に一緒に自動で起動されてきますので、こうしておけば、Workbook_Openイベントによって右クリコマンドバー登録マクロが自動で起動されるのです(ΦωΦ)ﾌﾌﾌ

まだ完成でわありません。

さて、個人用マクロブックは内容も含めて設定したことはエクセル終了後も保存されています。その仕組みの関係で、Workbook_Openイベントに仕込んだ起動プロシージャでマクロを登録すると、起動の度に同じものが追加されてどんどん増殖していくことになります。

(エクセルを起動するたびにお道具箱が増殖してしまいます)

f:id:FukuCyndiP:20210131191319p:plain

このままではえらいことになりますので、Workbook_Openイベントには

　　　　コマンドバーを初期化 → マクロを登録

と、なるようにコーディングします。

こんなこともあろうかとMicrosoft様はちゃーんと初期化コマンドを準備していて、Application.CommandBars("Cell").Resetで追加したコマンドを削除することができます。

というわけで、Workbook_Openイベントプロシージャは以下のようになりました。

Option Explicit

Private Sub Workbook_Open()
    Call Resetツールバー
    Call お道具箱を追加
End Sub

'--------------------------------------------
Sub Resetツールバー()
　　Call Application.CommandBars("Cell").Reset  'Callは省略可能
End Sub

Application.CommandBars("Cell").ResetはもちろんOpenイベントに直接記述してもいいですし、お道具箱追加プロシージャの中の一番最初に記述してもよいかと思います。これで何度エクセルを何度起動してもお道具箱が増殖することはありません

これで認めてもらえる(´；ω；`)ｳｯ （だから誰に？)　

まとめ

今回の記事では、個人用マクロブックのWorkbook_Openイベントを使って、マクロの右クリコマンドバー登録をエクセル起動と同時に自動で行うという方法を紹介しました。これであなたのマクロをコマンドバーに空気のように常駐させることができるかと思います。

"マクロの起動すらめんどくさい" にこだわって、無駄にエネルギーを使った結果なでわありますけれども、どんな小さなことでも繰り返しが蓄積するとストレスですよねぇ。効率化の味を知っているVBAerの皆さん方は特に "めんどくさい" の感度が高いかと思いますので、この気持ちわかっていただけると信じております。

私はこの方法でたくさんのツールを登録して普段から使っていて、何か思いついたら追加したりしています。一部のツールは同僚にも配って同じように使ってもらっていますけれども、結構評判が良かったりします。

ちっちゃなことだけど地味にめんどくさい。いちいちコードを書く程でもないよねぇ～な作業こそ、右クリメニューに最適かなと思いますので、ぜひやってみてください。

同僚の目の前で何気ないフリして使って、"なんやそれ～” な反応をいただくのもよろしいかとｗ

でわまた～￣▽￣)ﾉｼ

因みに

Application.CommandBars("Cell").Resetをブックモジュールに記載した場合、Applicationを省略すると実行時エラーとなります。

f:id:FukuCyndiP:20210131191341p:plain

ところが、標準モジュールでは省略可能なんですよね。同じApplicationメンバー(SendKeysメソッド、Columnsプロパティ）では同じことが起こるわけではないので、ブックモジュールにApplicationメンバーを記述するときは省略不可というわけではなさそうです。。

とりあえず棚上げで・・ (´・ω・)ｽﾏｿ.

追記・・・

記事を公開後にことりちゅんさんから以下のご助言をいただきました。

Excel の右クリックメニューにマクロ入れると便利ですよね！

僭越ながら注意事項をお伝えさせてください。
１．Resetすると、他のマクロによるカスタマイズも消えちゃいます。
２．CommandBarsで頭にApplicationを付けない場合、書いたモジュールによって補完がチガイマス。
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2021年1月31日

慌てて確認したところ、

.ComanndBars("Cell).Reset は右クリメニューの全てをリセットするため、メニューに他のカスタマイズをしていた場合はそれもリセットされてしまいます。

右クリメニューのカスタマイズはまだマクロ追加くらいしかやったことなかったので、あんまり意識していませんでしたが、.Resetは確かに潜在的な不具合リスクを抱えるためあまりよろしくはないかもしれません。そこで、追加したコマンドを狙い撃ちで削除できる Application.CommandBars("Cell").Controls("マクロ名").Deleteを使ったコードを下に掲載しましたので、ご参考にしていただければと思います。

もちろん .Resetが絶対ダメというわけではなく、まっさらな右クリメニューに新しいコマンド追加することを想定した今回のケースでは、全く問題はないかと思います。とわいえ、.Resetを選択するにしても、その挙動を知ったうえでの選択であった方がより望ましいのではないかと筆者は考えます。

お道具箱を常駐させるコード

Option Explicit

Private Sub Workbook_Open()
    Call お道具箱を削除
    Call お道具箱を追加
End Sub

'--------------------------------------------
Sub お道具箱を削除()
    'その時にあるControlコレクションの中から "お道具箱" を探して見つかったら削除する
    Dim ctrl As Variant
    For Each ctrl In Application.CommandBars("Cell").Controls
        If ctrl.Caption = "お道具箱" Then
           Application.CommandBars("Cell").Controls("お道具箱").Delete
        End If
    Next

End Sub

　お道具箱削除プロシージャはApplication.CommandBars("Cell").Controls("お道具箱").Delete　単独でWorkBook_BeforCloseイベントに仕込むことも考えましたが、これだと想定通りの挙動とならなかったため、先に実行する構造にしました。ですので、そのプロシージャ本体では、Application.CommandBars("Cell").Controls コレクションを走査して、"お道具箱” が見つかったら削除するというロジックになっています。初めからなかったら削除できませんからね。
　
　

２. CommandBars() に Applicationを付ける場合と付けない場合では内容が異なる

オブジェクトブラウザを確認したところ、CommandBarsには2つの顔があることがわかりました。

１．ExcelライブラリのApplicationクラスCommandBarsプロパティ
２．OfficeライブラリのCommandBarsクラス

これはつまり、省略した場合にどちらを取るかはPC側に判断を委ねるということになり、これもコード作成者の意図とは異なる動きになるリスクを孕む記述となります。つまり、他のOfficeアプリケーションと連動するようなコードを書いていた場合、ライブラリメンバとして認識された結果、意図せずエクセル以外のOfficeアプリケーション操作につながる可能性があるということになります。

もちろん、筆者の意図するところはExcel操作ですので、 Application.CommandBars() とした方がより不具合リスクが少ない書き方になりますね。省略するとしても、エクセル操作に絞ったコーディングだからそこは大丈夫と理解したうえでの省略であるべきかと思います。

また、棚上げ項目についても以下の考察をいただきました

シート・ブックモジュールでは、ThisWorkbook.CommandBarsになるので、自分自身のみ=PERSONAL.XLSBを書き換えようとします。窓がないからメニューもない。だからエラーなのかも？

あと、CommandBarsを触るときはActiveWindow.Application.にだけ処理が走るっぽいので、メニューが増えない窓があったり
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2021年1月31日

なるほどです。

個人用マクロブックのブックモジュールに記述してもブックの実体がないためコマンドが実行できないということですね。

確かに .Resetだけでなく、Application.CommandBars("Cells").Controls.Add()の場合も同じ実行時エラーになります。

一方で、個人用マクロブックにはなぜかSheet1モジュールがひっついています。それで、そこでは .Reset, .Controlls.Add()ともにエラーとなりませんでした。さらに、.Add()により、.xlsmブック側のシートの右クリメニューにコマンド追加されました。

Me.Parent.Name で親オブジェクトを調べてみると、PERSONAL.XLSBとなっていて、シートモジュールの親は実体を持たない個人用マクロブックとなっていましたため、もしかしたら、Sheetモジュールは実体の側のSheetと何らかの形でつながっているのかもしれませんね。この辺り、今一つ理解には至っていない部分もありますが、さっぱりわからなかった現象を考察するアイデアをいただき大感謝です!!

ちゅんさんどうもありがとうございました！