DuKiccoの雑記

My Life Is Myself

【アドインを自動化する】【エクセル2013,VBA】

「これ昔似たようなコード書いたことがあるけど、どのブックに書いたっけ?探すの面倒だからまた新しく書いちゃえ。」みたいなことになったりしないでしょうか?そうなると使い方が微妙に違う亜種がいくつも発生したり、探すのが余計に面倒になったりします。コードは部品化して再利用すると、効率よく新しい機能を作れ、保守(不具合の修正など)も楽になるのですが、亜種の存在は効率性と保守性に問題を生じさせます。

また多くの第三者にいくつもの機能を使用してもらう際には、「1.配布、2.機能の追加・修正、3.利用」に問題が生じます。

 

1.配布

利用者はエクセルに詳しくない人が多いです。機能を利用するための設定が面倒な場合は、設定に手間取るか放棄する(あなたへの助力を求めることを含む)かのどちらかです。

 

2.機能の追加・修正

機能の追加や修正は必ずあるものです。追加や修正が面倒な場合は、配布時と同様の問題が生じます。また利用者は配布済みのファイルを自分が使いやすいようにアレンジしたりしている(例えば名前欄やメールアドレス欄に自分の情報を入力してる)ことが多いので、旧バージョンでも本人にとって問題なければ場合、簡単には手放してくれませんし、そのファイルが流通し続けることがあります。

 

3.利用

作成者以外にとって、機能が多い場合、どのような機能があるか、どうすれば利用できるかを把握することは容易ではありません。詳細な説明書を作成する気にもなれないでしょう。

 

配布の問題(「配布の問題」とは!?)

配布の問題(アドインとは!?)

 

エクセルアドインの利用と自動化のメリット

エクセルアドインはリボンにあるコピーやペースト同様の操作で自分が作った機能を選択することを可能にします。機能の把握と利用がとても容易になります。ただしアドインの設定は知らない人にとって、面倒です。

 

アドインに存在する機能はすべてのエクセルファイルから呼び出せるのでアドインにコードがあれば各ファイルごとにコードを記述する必要はありません。コードの多重化が少なくなります。書類の雛形シートなどもアドインファイルの中に保存しておいて、必要なシートのみコピーして使うようにすればいろいろ便利です。

 

アドインの自動化コードがあれば利用者はワンクリックで設定や機能の追加が可能となります。

 

以下コードアドインの自動化コード

=============================================================

'Sub プロシージャ内に記述

Sub SaveAsAddin()
'ボタンに本コードを登録するとワンクリックでアドインが可能となる。

Dim tst As String, FN As String

On Error GoTo ELH

'アドイン形式で保存されるアドイン名。なんでもよい。
tst = "AutoAddin1"
'保存場所。
FN = "C:\Users\" & GetUserName & "\AppData\Roaming\Microsoft\AddIns\" & tst & ".xlam"

'保存したいファイルと同名のファイルがあった場合、updateとみなしアドインをアンインストールする。
If Dir(FN) <> "" Then
'アンインストール。自動的にワークブックイベントのPrivate Sub Workbook_AddinUninstallが実行されメニューバーが削除される。
AddIns(tst).Installed = False

End If


Application.DisplayAlerts = False

'アドイン形式で保存する。

ActiveWorkbook.SaveAs Filename:=FN, FileFormat:=xlOpenXMLAddIn, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Application.DisplayAlerts = True
'アンインストール。自動的にワークブックイベントのPrivate Sub Workbook_AddinInstallが実行されメニューバーが追加される。
AddIns(tst).Installed = True

MsgBox "インストールが完了しました。"

Exit Sub

ELH:

MsgBox "アドインをインストールできませんでした。"

End Sub


Function GetUserName()
'ユーザーネームを取得するファンクション
Dim WshNetworkObject As Object
Set WshNetworkObject = CreateObject("WScript.Network")

With WshNetworkObject
GetUserName = .UserName
End With

Set WshNetworkObject = Nothing
End Function


Sub 機能1()

'ここに"サブサブメニューバー1"がクリックされた際の機能を記述する。

MsgBox "機能1"

End Sub


Sub 機能2()

'ここに"サブサブメニューバー1"がクリックされた際の機能を記述する。

MsgBox "機能2"

End Sub


Sub Addin手動削除()

'上記コードで登録されたAutoAddin1をアンインストールしメニューバーを削除する

Dim MBname As String

AddIns("AutoAddin1").Installed = False
'削除するメニューバーの名前
MBname = "メニューバー1"

Application.CommandBars("Worksheet Menu Bar").Controls(MBname & "(&C)").Delete

End Sub

 

'!!!!!ここからワークブック イベントプロシージャに記述!!!!!

Private Sub Workbook_AddinInstall()


'メニューバー追加

Dim NewM As Variant, NewC As Variant, MBname As String

'追加するメニューバー
MBname = "メニューバー1"

'新しいメニューを追加する
Set NewM = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)

NewM.Caption = MBname & "(&C)"

'サブメニューを追加する
Set NewC = NewM.Controls.Add(Type:=msoControlPopup)
With NewC

.Caption = "サブメニューバー1"
.BeginGroup = True

With NewC.Controls.Add()

.Caption = "サブサブメニューバー1"
.OnAction = "機能1" 'サブサブメニューバー1が選択されたときに実行されるSubプロシージャ名

End With

With NewC.Controls.Add()

.Caption = "サブサブメニューバー2"
.OnAction = "機能2" 'サブサブメニューバー2が選択されたときに実行されるSubプロシージャ名

End With

End With

End Sub
Private Sub Workbook_AddinUninstall()

On Error Resume Next

Dim MBname As String

'削除するメニューバーの名前
MBname = "メニューバー1"

Application.CommandBars("Worksheet Menu Bar").Controls(MBname & "(&C)").Delete

End Sub

 

f:id:DuKicco:20150314225252j:plain

===============================================================

 

補足

アドイン設定基本

http://booyan.lopan.jp/excel_addin/

コード説明

Sub SaveAsAddin (メイン)

コードが書かれているエクセルファイルをアドイン形式で保存後、インストールする。

内容

1.保存先を設定するためにユーザー名を取得する(*1)。ちなみに多くのユーザーに配布する場合、エクセルファイルのシートの一枚に各種情報の記載された名簿を用意し、ユーザー名をもとに各種情報(メールアドレスや部署など)を取得するコードを組み込むことでユーザーに応じた初期設定まで自動化まで可能です。

 

2.保存したいファイルと同名のファイルが存在するか確認する。存在した場合は、修正とみなし以前のアドインをアンインストールする。アンインストールされた場合、ワークブックイベントから自動的にメニューバーの削除が実行される(*2)。

 

3.アドイン形式で保存後、アドインをインストールする。インストールされた場合、ワークブックイベントから自動的にメニューバーの追加が実行される(*3)。

 

 

ちなみに自分の場合、メニューバーをクリックするとフォームが立ち上がるようにしてます。そのフォームの中に簡単な説明とボタンを配置し、ボタンをクリックすると機能が実行されるようにします。フォームごとに同系列の機能をまとめておくことで把握がしやすいですし、フォームは常に表示した状態にしておけるので機能を呼び出すのも楽になります。

Excelマクロ/VBAで始める業務自動化プログラミング入門(12):Excelに入力フォームを作成、コントロールを追加、表示、ボタンでイベント実行 (1/3) - @IT

 

 

以下補助部分

*1:Function GetUserName

内容

ユーザー名を取得する。

 

*2:Private Sub Workbook_AddinUninstall()

ワークブック イベントプロシージャに記載。アンインストール時にメニューバーを削除。

Excel VBA 入門講座 ワークブックのイベントプロシージャ

10.3 アドインのアンインストール時 - Excel VBA Tips

 

*3:Private Sub Workbook_AddinInstall()

内容

ワークブック イベントプロシージャに記載。インストール時にメニューバーを追加。