【Excel VBA】ブックが閉じられた事を検出する方法
先日ちょっとしたツールを作っている時に他のブックが閉じられた事を検出したい場面に遭遇したのですが、Workbook オブジェクトにもApplicationオブジェクトにも既定のイベントには他のブックが閉じられた事を検出できるものがなかったので自作してみました。
既定のイベントでは検出できない
VBAには様々イベントが実装されています。イベントとは『あるアクションが実行された時に自動で実行されるプロシージャ』だと思って下さい。例えばブックが開かれた時はWorkbook.Openイベントが自動で実行されます。このようなブックのアクションに関連したイベントはWorkbookオブジェクトやApplicationオブジェクトに実装されているのですが、ブックが閉じられた時に実行されるイベントがないのです。
※あるのかも知れませんが僕には見つけられませんでしたのでご存知の方がいらっしゃいましたら教えてください！
惜しいイベントとして、閉じられる直前に実行されるWorkbook.BeforeCloseイベントやApplication.WorkbookBeforeCloseイベントがあるのですが、このイベントが発生したからと言って必ずその後にブックが閉じられる訳ではありません。ブックが変更されている場合に閉じる前に出る「～への変更を保存しますか？」の確認で「キャンセル」を選択するとブックは閉じられないためです。
他のブックが閉じられた事を検出するサンプルクラス
今回はブックが閉じられた時に発生する独自イベントを追加したので一連の処理をクラスモジュールに纏めてみました。
'************************************************************************
'*-----------------------------------------------------------------------
'* Name： WorkbookClosedDetector
'*-----------------------------------------------------------------------
'* Descriptioin：detect workbook closed
'*-----------------------------------------------------------------------
'* Copyright: HAYs https://dev-clips.com , 2017 All Rights Reserved.
'*-----------------------------------------------------------------------
'* <Update>
'* Date Version Author Memo
'*-----------------------------------------------------------------------
'* 2017.04.24 1.00 HAYs New Release
'************************************************************************
' option
Option Explicit
'************************************************************************
'* event
'************************************************************************
Event WorkbookClosed()
'************************************************************************
'* variable
'************************************************************************
Private WithEvents App As Application
Private cnt As Long
'************************************************************************
'* class event
'************************************************************************
'*-----------------------------------------------------------------------
'* constructor
'*-----------------------------------------------------------------------
Private Sub Class_Initialize()
'initialize
Set App = Application
'save
cnt = Workbooks.Count
End Sub
'************************************************************************
'* Application event
'************************************************************************
'*-----------------------------------------------------------------------
'* NewWorkbook
'*-----------------------------------------------------------------------
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
'save book count
cnt = Workbooks.Count
End Sub
'*-----------------------------------------------------------------------
'* WorkbookOpen
'*-----------------------------------------------------------------------
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'save book count
cnt = Workbooks.Count
End Sub
'*-----------------------------------------------------------------------
'* WorkbookActivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
'check count
If Workbooks.Count < cnt Then
cnt = Workbooks.Count
RaiseEvent WorkbookClosed
End If
End Sub
'*-----------------------------------------------------------------------
'* WorkbookDeactivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
'check count
If Workbooks.Count = 1 Then
cnt = 0
RaiseEvent WorkbookClosed
End If
End Sub
解説
Class_Initializeイベント
'*-----------------------------------------------------------------------
'* constructor
'*-----------------------------------------------------------------------
Private Sub Class_Initialize()
'initialize
Set App = Application
'save
cnt = Workbooks.Count
End Sub
まずこのクラスのインスタンスが生成された時に以下の処理を行います。
WithEventsキーワードでイベントに対応したApplicationオブジェクトの変数Appをインスタンス化する。- 変数
cntに現在のブックの数を保存する。
これで準備はOKです。あとは App で検出するブックがアクティブ/非アクティブになった時に発生する Activate/Deactivate イベントでブックが閉じられた事を検出します。
Application.NewWorkbookイベント
'*-----------------------------------------------------------------------
'* NewWorkbook
'*-----------------------------------------------------------------------
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
'save book count
cnt = Workbooks.Count
End Sub
後述する処理で行われるブックが閉じられた事の判定にはブック数を保存しておく必要があります。そのため新しいブックが作成された場合は変数 cnt を更新しておきます。
Application.WorkbookOpenイベント
'*-----------------------------------------------------------------------
'* WorkbookOpen
'*-----------------------------------------------------------------------
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
'save book count
cnt = Workbooks.Count
End Sub
NewWorkbook と同様に、新しいブックが開かれた時に変数 cnt を更新します。NewWorkbook は新規ブック作成時に発生しますが、WorkbookOpen イベントは既存のブックが開かれた時に発生します。
Application.WorkbookActivateイベント
'*-----------------------------------------------------------------------
'* WorkbookActivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
'check count
If Workbooks.Count < cnt Then
cnt = Workbooks.Count
RaiseEvent WorkbookClosed
End If
End Sub
あるブックが閉じられた場合は他のブックがアクティブになります。この仕組みを利用してブックがアクティブになった時に変数 cnt に保存しておいたブック数と現在のブック数(Workbooks.Count)を比較し、現在のブック数が少なくなっていれば他のブックが閉じられたと判定できます。
Application.WorkbookDeactivateイベント
'*-----------------------------------------------------------------------
'* WorkbookDeactivate
'*-----------------------------------------------------------------------
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
'check count
If Workbooks.Count = 1 Then
cnt = 0
RaiseEvent WorkbookClosed
End If
End Sub
先述したWorkbookActivateイベントでの検出はブックが複数開かれている場合にのみ有効となります。開いているブックが 1 つしかなかった場合はそのブックが閉じられても次にアクティブになる他のブックがないためです。しかし、逆に言えばブックが非アクティブになった時にブック数(Workbooks.Count)が 1 つであれば閉じるアクションが実行されたと判定できる事になります。
因みにWorkbookBeforeCloseイベントもブックを閉じる前にほぼ同じタイミングで発生しますが、先述の通りイベントが発生た後にブックが閉じられない可能性があるためNGです。
WorkbookDeactivate イベントが発生した時点では、ブックが閉じられる事が確定しているだけでまだブックは閉じられていません。このサンプルを使用する際はこの点にご注意ください。RaiseEvent
ブックが閉じられた時に、モジュールの冒頭で定義した独自イベント WorkbookClosed を RaiseEvent ステートメントで発生させています。RaiseEvent の使用例はダウンロードサンプルをご覧ください。
まとめ
標準で無い機能は別の機能を組み合わせると実現出来る事が多いです。標準で実装されていない場合は、別の方法（仕様）を探すか、今回のように別の標準機能を組み合わせて実現しましょう。
最後に今回のサンプルを置いておきますので自己責任でご利用ください。
HAYs
HAYs様
[WorkbookClosedDetector]の追加BookのClose時のクラス作成ありがとうございます。
Excel関数練習用のアプリケーションで、例題コピー –>追加ブックで練習–>元に戻る時元のシートの設定が崩れるのでボタン追加で処理をしていました。(カッコ悪い)
HAYs様のClassでボタンの処理を代行させたところ無事処理できました。
次期Officeではスクリプト言語に[Python]を採用するようで期待していますが、WorkBookやWorkSheetのオブジェクトは[import WorkBook]などで使用できるのでしょうか？少し心配しています。PythonはHTMLと相性が良いのでこれから[UserForm]はHTMLで作るようになるのかなと思っています。
長文失礼しました。
大変ありがとうございました。