キャスレーコンサルティングSI部の高橋です。

前回の「単純作業は自動化しよう(VBA編(1))」は初回ということもあり、

Excelに直接VBAを書き、実行する方法を紹介しましたが

この方法ですと、VBAを実行したいときに、毎回VBAを書いたExcelを開き実行しなければいけません。

そうなるとVBAごとにExcel管理をしたり、VBAを利用したい度にそのExcelを開き実行するという手間から

折角VBAを作成しても、利用頻度が落ちる可能性があります。

そこで、今回はExcelのメニューバーからいつでも実行できるようにするアドインを作成していきたいと思います。

アドインを取り込んでおくことで、 ExcelからいつでもVBAを実行することができ

また、アドイン化することで他の人への配布も楽になりますし、

ツールとして利用しやすく(操作しやすく)なります。

最終的には、以下のようにメニューに表示されます。

0

作成したVBAをメニューに出力させる手順

作成したVBAをメニューに出力させるまでの手順を説明いたします。

新規でExcelアドインを作る

新規でExcelを開き、シートを1つに(不要なシートを削除)して、

ファイル⇒名前を付けて保存⇒ファイルの種類「Excel97-2003アドイン(*.xla)」を選択し、

ファイル名には好きな名前を付けて(今回は「自動化アドイン」)保存

1-1

Visual Basicを開く

作成したアドインを開く

2-1

開発⇒Visual Basicを選択し、Visual Basicを開きます。

※開発タブがないときは「単純作業は自動化しよう(VBA編(1))」を参考に、

開発タブを表示させてください。

2-2

VBAを書く

以下のように、VBAを書きます。

【ThisWorkbook】

メニュー表示処理

3-1

3-2

【CopyAndRename】

メニューのボタンより実行される処理

3-3

【ThisWorkbook】

Option Explicit
Private Const TOOL_BAR_NAME As String = "Worksheet Menu Bar"
Private Const TOOL_MENU_NAME = "自動化メニュー"

'アドインインストール時実行
Private Sub Workbook_AddinInstall()
Call SetAddin 'アドインセット処理を実行
End Sub

'アドインアンインストール時実行
Private Sub Workbook_AddinUnInstall()
Call DelAddin 'アドイン削除処理を実行
End Sub

'アドインセット処理
Private Sub SetAddin()
'コマンドバー
Dim cmdBar As CommandBar
Dim mainCmdBarPopup As CommandBarPopup
Dim mainCmdBarCtl As CommandBarControl
Dim mainMenu As CommandBarPopup

Dim SubMenuDicPathSelect As CommandBarButton
Dim SubMenu As CommandBarButton
Dim SubMenuPopup As CommandBarPopup

'既にメニューバーに追加済みの場合、何もしない
For Each cmdBar In Application.CommandBars
If cmdBar.Name = TOOL_BAR_NAME Then
For Each mainCmdBarPopup In cmdBar.Controls
If mainCmdBarPopup.Caption = TOOL_MENU_NAME Then Exit Sub
Next
End If
Next

'メニューをセット(メニューバーに自動化メニューを追加)
Set mainMenu = Application.CommandBars(TOOL_BAR_NAME).Controls.Add(Type:=msoControlPopup)

'メニュータイトルを自動化メニューにする
mainMenu.Caption = TOOL_MENU_NAME

'自動化メニューのサブメニューを設定
Set SubMenuDicPathSelect = mainMenu.Controls.Add
'子メニュー名
SubMenuDicPathSelect.Caption = "雛形コピーと名前変更"
'子メニューを選択時に実行する処理名
SubMenuDicPathSelect.OnAction = "CopyAndRename"

End Sub

'アドイン削除処理
Private Sub DelAddin()
Dim cmdBar As CommandBar
Dim mainCmdBarPopup As CommandBarPopup
Dim mainCmdBarCtl As CommandBarControl

'メニューバーから追加したメニューを削除する
For Each cmdBar In Application.CommandBars
If cmdBar.Name = TOOL_BAR_NAME Then
For Each mainCmdBarPopup In cmdBar.Controls
If mainCmdBarPopup.Caption = TOOL_MENU_NAME Then
Application.CommandBars(TOOL_BAR_NAME).Controls(TOOL_MENU_NAME).Delete
Exit Sub
End If
Next
End If
Next
End Sub

【説明】

追加メニューをする定義

メニューバーに表示する名称を定義します

Set mainMenu = Application.CommandBars(TOOL_BAR_NAME).Controls.Add(Type:=msoControlPopup)

mainMenu.Caption = TOOL_MENU_NAME

子メニューを追加する定義

メニューバーに表示するメニューを押下した時に表示する子メニューと、実行させる処理を定義します

これを追加することで、複数の子メニューが作成できます

Set SubMenuDicPathSelect = mainMenu.Controls.Add

SubMenuDicPathSelect.Caption = “雛形コピーと名前変更”・・・表示する名称

SubMenuDicPathSelect.OnAction = “CopyAndRename”・・・実行する処理

アドイン取り込み

Excelを開き、作成したアドインをセットする

オプションを選択

4-1

アドインを選択し、設定ボタンを押下

4-2

参照ボタンを押下

4-3

作成したアドインを選択し、OKボタンを押下

4-4

作成したアドインを選択し、OKボタンを押下

4-5

アドインタブより、作成したメニューが表示され、このメニューよりVBAが実行できます。

4-6

最後に

これで、実行するまでの手間が大幅削減することができます。

また、アドインとして取り込んだので、「開発⇒Visual Basic」よりアドインの修正も可能になります。

次回はVBA内で定義されている情報を、

フォームコントロールを利用して入力し実行する方法を紹介したいと思います。