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

  • 質問

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

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

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

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

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

                                   

    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

    2019年3月4日 10:11

すべての返信

  • Excelのブックを閉じるさい、Workbook_BeforeClose メソッドが呼ばれるので、そこで閉じようとしていることを検知するのはどうでしょうか?そのメソッド内で Cancel = True として処理を一旦キャンセルさせ、必要な処理が終わったタイミングでブックを閉じる処理を呼び出せばよいかと思います。
    2019年3月4日 11:35
  • >おなじVBAがExcel2010ではちゃんと動くのです
    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

    2019年3月5日 8:34
  • kenjinoteさん

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

    2019年3月5日 10:01
  • n.hojoさん、確かにこの方法だと動きますね。

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

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

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

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

    2019年3月5日 23:50
  • ブックが閉じられたかどうか、Do文で監視することに無理があるような・・・

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

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

        '処理~~

    End Sub

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

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

    2019年3月20日 4:04