【Excel VBA】ブックが閉じられた事を検出する方法

先日ちょっとしたツールを作っている時に他のブックが閉じられた事を検出したい場面に遭遇したのですが、WorkbookオブジェクトにもApplicationオブジェクトにも既定のイベントにはそれっぽいのは見当たらない…。

でも無いものは仕方ないですよね。とは言え無いと困ります。

なので作っちゃいました。あった方が便利ですもんね。




既定のイベントでは検出できない

VBAには様々イベントが実装されています。「イベント」とは『あるアクションが実行された時に自動で実行されるプロシージャ』だと思って下さい。例えばブックが開かれた時はWorkbook.Openイベントが自動で実行されます。このようなブックのアクションに関連したイベントはWorkbookオブジェクトやApplicationオブジェクトに実装されているのですが、ブックが閉じられた時に実行されるイベントがないのです。いや、あるのかも知れませんが僕には見つけられませんでしたのでご存知の方がいらっしゃいましたら教えてください!

惜しいイベントとして、閉じられる直前に実行されるWorkbook.BeforeCloseイベントやApplication.WorkbookBeforeCloseイベントがあるのですが、このイベントが発生したからと言って必ずその後にブックが閉じられる訳ではありません。ブックが変更されている場合に閉じる前に出る「~への変更を保存しますか?」の確認で「キャンセル」を選択するとブックは閉じられないためです。

他のブックが閉じられた事を検出するサンプルクラス

今回はブックが閉じられた時に発生する独自イベントを追加したので一連の処理をクラスモジュールに纏めてみました。

'************************************************************************
'*-----------------------------------------------------------------------
'*  Name: WorkbookClosedDetector
'*-----------------------------------------------------------------------
'*  Descriptioin:detect workbook closed
'*-----------------------------------------------------------------------
'*  Copyright: HAYs  http://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

まずこのクラスのインスタンスが生成された時に以下の処理を行います。

  1. WithEventsキーワードでイベントに対応したApplicationオブジェクトの変数Appをインスタンス化する。
  2. 変数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

ブックが閉じられた時に、モジュールの冒頭で定義した独自イベントWorkbookClosedRaiseEventステートメントで発生させています。RaiseEventの使用例はダウンロードサンプルをご覧ください。

まとめ

標準で無い機能は別の機能を組み合わせると実現出来る事が多いです。標準で実装されていない場合は、別の方法(仕様)を探すか、今回のように別の標準機能を組み合わせて実現すればいいんです:)

最後に今回のサンプルを置いておきますので自己責任でご利用くださいまし。

HAYs




コメント

  1. 淸水 優司 より:

    HAYs様
    [WorkbookClosedDetector]の追加BookのClose時のクラス作成ありがとうございます。
    Excel関数練習用のアプリケーションで、例題コピー –>追加ブックで練習–>元に戻る時元のシートの設定が崩れるのでボタン追加で処理をしていました。(カッコ悪い)
    HAYs様のClassでボタンの処理を代行させたところ無事処理できました。
    次期Officeではスクリプト言語に[Python]を採用するようで期待していますが、WorkBookやWorkSheetのオブジェクトは[import WorkBook]などで使用できるのでしょうか?少し心配しています。PythonはHTMLと相性が良いのでこれから[UserForm]はHTMLで作るようになるのかなと思っています。
    長文失礼しました。
    大変ありがとうございました。

    • HAYs HAYs より:

      お役に立てたようで嬉しいです:)
      Pythonはどうなるのでしょうね。今後の動向が楽しみです!