【VBA】コピペですぐ使える!! リボンへアドインを追加(表示)する

2019年10月1日Excel,PC

Excelのアドインをリボンへ表示させる＝新規追加する方法を紹介します。アドインとして表示させれば誰にでも使いやすいので、マクロ入りブックを配布する際にも便利ですよ。

これをコピペでOK

今回のマクロのイメージは下のようになります。リボンへアドインタブを表示させるので、「アドイン1」と「アドイン2」それぞれのマクロが実行されます。

まずは、できあがりのマクロを見て下さい。細かい説明がいらなければ、これをコピペですぐに使えますよ！

ThisWorkbookに下記2つを追加

Private Sub Workbook_Open()
　On Error Resume Next
     Call AddInsInstall
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)　　
    On Error Resume Next
        Call AddInsUninstall　
End Sub

標準モジュールに下記3つを追加

Public Sub AddInsInstall()
    Dim objCB As CommandBar
    Dim objCBCtrl As CommandBarControl
    Set objCB = Application.CommandBars("Worksheet Menu Bar")
　
    On Error Resume Next
    ' メニューの初期化
    objCB.Controls("アドイン名").Delete
    On Error GoTo 0
　
    Set objCBCtrl = objCB.Controls.Add(Type:=msoControlPopup, temporary:=True)
    objCBCtrl.Caption = "アドイン名"
　
    With objCBCtrl
        .Controls.Add Type:=msoControlButton
        With .Controls(1)
            .Caption = "アドイン1"
            .OnAction = "case1"
        End With
        .Controls.Add Type:=msoControlButton
        With .Controls(2)
            .Caption = "アドイン2"
            .OnAction = "case2"
        End With
    End With
End Sub

Public Sub AddInsUninstall()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Delete
End Sub

' 実行する内容をここへ書く
Private Sub case1()
    MsgBox "1つめの実行内容を記入する"
End Sub
　
Private Sub case2()
    MsgBox "2つめの実行内容を記入する"
End Sub

これらのプロシージャーをコピペして保存したら、開き直してみて下さい。これで、最初見たようにリボンへアドインが表示されます。

ThisWorkbookの内容

では、マクロの内容を確認しましょう。まずはThisWorkbookに書いた2つのプロシージャーからです。

Private Sub Workbook_Open()
    On Error Resume Next
        Call AddInsInstall
End Sub

このマクロは、ファイルを開いた際に標準モジュールに書いたAddInsInstallプロシージャーを読み込ませるものです。このプロシージャーは、メニューを作成するものです。

そして2つめのマクロは、ファイルを閉じる前に標準モジュールに書いたAddInsUninstallプロシージャーを読み込ませるものです。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
        Call AddInsUninstall
End Sub

AddInsUninstallはアドインを解除するプロシージャーです。

リボンへアドインを表示させる場合、メニューを作成するプロシージャー、メニューを削除するプロシージャー、実行する内容のプロシージャーと大きく3つが必要になります。

標準モジュールの内容

それでは、標準モジュールに書いたメニューを作成するプロシージャーの内容を確認しましょう。ここが一番量が多いですが、それでも30行にも満たないので安心して下さい。

まずは2～3行目で最初に変数の宣言をします。4行目で変数「objCB」にツールバーを代入しています。

2｜　Dim objCB As CommandBar
3｜　Dim objCBCtrl As CommandBarControl
4｜　Set objCB = Application.CommandBars(“Worksheet Menu Bar")

CommandBarの名前は"Worksheet Menu Bar”でなくともお好きに変えてもらってOKですが、表に出るものではないので、特段のこだわりがなければそのまま使ってもOKです。

次に6～9行目ですが、今回のアドインと同じ名前のツールバーが存在するとエラーになるため、「On Error Resume Next」でエラーを無視して、ツールバーを作成する前にDeleteメソッドで同じ名前のツールバーを削除してエラーを防ぎます。

6｜　On Error Resume Next
7｜　’ メニューの初期化
8｜　objCB.Controls(“アドイン名").Delete
9｜　On Error GoTo 0

アドイン名という部分には共通で同じ名前を付けて下さい。ここがリボンへ表示される部分です。

続いて11～12行目で、変数「objCB」に代入したツールバーにサブメニューを持つメニューを作成します。「msoControlPopup」でサブメニューを持つメニューを指定できます。

11｜　Set objCBCtrl = objCB.Controls.Add(Type:=msoControlPopup, temporary:=True)
12｜　objCBCtrl.Caption = “アドイン名"

14行目以降はサブメニューの設定です。下の15～19行目がひとまとまりで、これを追加することでサブメニューを増やせます。

15｜　.Controls.Add Type:=msoControlButton
16｜　 With .Controls(1)
17｜　 　.Caption = “アドイン1"
18｜　 　.OnAction = “case1"
19｜　 End With

Captionがサブメニュー名、OnActionの内容が実行するプロシージャーになります。

これでメニューを作成するプロシージャーの内容確認は終わりです。

残りはメニューを解除する下のプロシージャーです。

Public Sub AddInsUninstall()
    On Error Resume Next
    Application.CommandBars("Worksheet Menu Bar").Delete
End Sub

説明も何もありませんね。ThisWorkbookに書いたAddInsUninstallはこの部分を呼び出すものです。

そしてあとは、実行したい内容を書いたプロシージャーです。メニューを作成するプロシージャーで指定したOnActionはこの部分ですね。

' 実行する内容をここへ書く
Private Sub case1()
    MsgBox "1つめの実行内容を記入する"
End Sub
　
Private Sub case2()
    MsgBox "2つめの実行内容を記入する"
End Sub

ここまで来てやっとマクロを実行する準備が整ったという感じです。最初は手間ですが、予めアドインを表示させるマクロファイルとしてテンプレートで保存しておけば、2回目以降はいつもと同じようにマクロを書くだけです。

リボンへのアドイン表示はなかなか使えるのですが、配って使ってもらうこと前提だとエラー処理も必要になります。私は、これとシートの存在確認はセットで使うことが多いです。

あわせて参考にしてもらえればと思います。