Excelのブックを閉じるとVBAの実行が止まる

Question

Office365のExcelで気が付いたのですが、ExcelのVBAでブックを開き、そのブックが閉じるのを監視して、閉じたら別の作業をする流れを作っていました。

具体的には、ブックを開いた後、Win32 API で Sleep させつつ、Workbookのコレクションをチェックして、閉じたことを検知するようなロジックを書いたのですが、ブックを閉じるとVBAが停止してしまいます。

ところが、おなじVBAがExcel201０ではちゃんと動くのです。

この回避策はあるのでしょうか？

簡単な検証コードでも同じ動作になりました。

                               

Option Explicit
’ API定義
Private Declare Sub Sleep Lib "kernel32" (ByVal dwms As Long)

Dim flg As Boolean

' 実行用ボタンのVBA
Sub ボタン1_Click()

    Dim lCount As Long

    lCount = 0
    flg = True

    Do While flg
        DoEvents

        ThisWorkbook.Worksheets(1).Range("A1").Value = Time$
        ThisWorkbook.Worksheets(1).Range("A2").Value = CStr(lCount)

        lCount = lCount + 1
        If lCount = 30 Then
            Workbooks.Add
        End If
        DoEvents

        Sleep (100)
        DoEvents
    Loop
End Sub


’ マクロ中止用ボタンの処理
Sub ボタン2_Click()
    flg = Not True
End Sub

Answer 1

Excelのブックを閉じるさい、Workbook_BeforeClose メソッドが呼ばれるので、そこで閉じようとしていることを検知するのはどうでしょうか？そのメソッド内で Cancel = True として処理を一旦キャンセルさせ、必要な処理が終わったタイミングでブックを閉じる処理を呼び出せばよいかと思います。

Answer 2

>おなじVBAがExcel201０ではちゃんと動くのです
2010が手元にないので動作確認はしていませんが、
2013になるときに、
インスタンスが云々といった変更が行われているからじゃないかと思います。
https://blogs.msdn.microsoft.com/office_client_development_support_blog/2016/12/19/excel2013-changes-to-sdi/

深くは読んでいませんが、後半の説明を参考に
新たなブック（や既存のブック）を開くコードを直せば耐えられるのかもしれません。
※当方のスキルが浅く、無責任な返答です。m(_ _)m

VBAは
「マクロで開いたならマクロで閉じてよぉ」と言っている気がします。

私だったら
次のようなコードを使いマクロで閉じます。

Option Explicit
' API定義
Private Declare Sub Sleep Lib "kernel32" (ByVal dwms As Long)

Dim wb As Workbook
Dim flg As Boolean

 ' 実行用ボタンのVBA
 Sub ボタン1_Click()
　　 Dim lCount As Long
　　
　　 lCount = 0
　　 flg = True

　　 Do While flg
　　　　 DoEvents

　　　　 ThisWorkbook.Worksheets(1).Range("A1").Value = Time$
　　　　 ThisWorkbook.Worksheets(1).Range("A2").Value = CStr(lCount)

　　　　 lCount = lCount + 1
　　　　 If lCount = 30 Then
　　　　　 Set wb = Workbooks.Add
　　　　 End If
　　　　 DoEvents

　　　　 Sleep (100)
　　　　 DoEvents
　　 Loop
 End Sub

' マクロ中止用ボタンの処理
Sub ボタン2_Click()
　　 flg = Not True
End Sub

Sub ボタン3_Click()
　wb.Close
End Sub

Answer 3

kenjinoteさん

この手法だと、新しく開いたWorkbookに、モジュールを仕込む用な感じでやるとできそうですね。
昔、VBEを使ってコードを仕込んだりしたことがあるので、ちょっと考えてみます。

Answer 4

n.hojoさん、確かにこの方法だと動きますね。

ただマクロ側から閉じるというのは利用者に徹底はできないので、

開いたブックを利用者が閉じてしまった時の対策をしないといけなさそうです。

kenjinoteさんのアドバイスと合わせて考えてみます。

2013になったときのSDIになって、これは使いやすくなったと思っていたらこんなところに落とし穴が。。。

Answer 5

ブックが閉じられたかどうか、Do文で監視することに無理があるような・・・

kenjinoteさんの回答と重複しますが、監視側ブックにクラスモジュールを追加して、アプリケーションレベルのイベントをチェックしてはどうでしょうか？

Private WithEvents xl As Application
Private Sub xl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    Cancel = True

    '処理～～

End Sub

Answer 6

いくつかのアイデアをいただいて試したものの、Workbooks.add の場合、新しく作成したBookが閉じる際にVBAの実行が停止するということで、結局はworkbooksではなく、CreateObjectでBookを開く形にしました。

n.hojoさんの資料からいろいろ見ていって、結局のところBookを開くときのプロセスモデルに起因しているようです。