VBAのあれこれ(雑記 #Excel

雑記なので正確じゃないかも

`Excel.WorkBook` オブジェクトを `Close` しては `Set Nothing` するな

Set Nothing お作法はVBAの世界では当たり前なのかも知らんが、Exit Sub や End Sub の直前で関数ローカルのオブジェクトをNothingする意味はほぼない。
(対象が関数のローカル変数ではなく、モジュール共用とかグローバルとかなら話は別だが...んなキモいことしないで(懇願))

Application や WorkBook などは Quit や Close を呼び出して閉じないと、マクロ終了後もプロセスが残ったりするのは、そう。
だからエラートラップしてラベルに飛んでこれらを呼ぶ。わかる。

が、直後にその関数を抜けるならば、Set Nothing は(ローカル変数ならば)明らかに不要だ。
勝手に適当なタイミングでやってくれるのだから。

`workbook.Close`を二度呼ぶと...

ところで、次の文はエラーになる。

こう書くとエラーになる

Dim xBook As Excel.Workbook
Set xBook = Application.Workbooks.Open
Call xBook.Close(SaveChanges:=False)
Call xBook.Close(SaveChanges:=False) ' ! Close 済みの Workbook オブジェクトに対して再度 Close を呼び出す

これを回避したくて、Closeできる保証が欲しいのか、Set Nothing してそれを判定する輩がいたんじゃねえか

`Set Nothing` お作法

これがよく見かける記述だ

よく見かけるキモい記述

On Error GoTo errHandle

' bla-bla-bla

If なにかしらの終了条件 Then
  Call xBook.Close(SaveChanges:=False)
  Set xBook = Nothing
  Exit Sub
End If

' bla-bla-bla

errHandle:
If (xBook Is Nothing) Then
  Call xBook.Close(SaveChanges:=False)
  Set xBook = Nothing
End If

うむ。まあif がふえるのはしゃーない。
が、そのNothingはいらねえ。

`Set Nothing` はいらない

こうしろ

On Error GoTo errHandle

' bla-bla-bla

If なにかしらの終了条件 Then
  Call xBook.Close(SaveChanges:=False)
  Exit Sub
End If

' bla-bla-bla

errHandle:
If TypeName(xBook) = "Workbook" Then
  Call xBook.Close(SaveChanges:=False)
End If

雑な解説

Excel.Workbook オブジェクトの一生

Workbook オブジェクトの一生を追うと以下のようになる

' xBook : Nothing
' TypeName(xBook) : "Nothing"

Dim xBook As Excel.Workbook
' xBook : Nothing
' TypeName(xBook) : "Nothing"

Set xBook = Application.Workbooks.Add
' xBook : (返り値 新規作成されたブック)
' TypeName(xBook) : "Workbook"

Call xBook.Close(SaveChanges:=False)
' xBook : (メンバを持たないオブジェクト)
' TypeName(xBook) : "Object"

' ためしに Set Nothing
Set xBook = Nothing
' xBook : Nothing
' TypeName(xBook) : "Nothing"

まあオブジェクト持ったまま抜けるのイヤってのもあるのかも知らんが。
コード量は少ないほうがよいぞ

Excel.Application オブジェクトの一生

Application の場合は少々事情が異なる

オブジェクトを設定→Quit→SetNothing

' ap : Nothing
' TypeName(ap) : "Nothing"

Dim ap As Excel.Application
' ap : Nothing
' TypeName(ap) : "Nothing"

Set ap = New Excel.Application
' ap : (新規の Application オブジェクト)
' TypeName(ap) : "Application"
' タスクマネージャーで新しいエクセルが起動しているのが確認できる

ap.Quit
' ap : (作られていた Application オブジェクト。 メンバを維持している)
' TypeName(ap) : "Application" のまま
' タスクマネージャーでエクセルが未だ起動しているのが確認できる

' ためしに Set Nothing
Set ap = Nothing
' タスクマネージャーでエクセルが終了することが確認できる

Application オブジェクトは、 Nothing が設定されるまでオブジェクトを維持する。

が、まあ、Exit Sub することで、
(その関数以外が該当するオブジェクトへの参照を保持していないことが保証されている場合に限り)
ガベージコレクタ(って言っていいの？)が働いて、
オブジェクトが開放される

オブジェクトを設定→Quit→いきなりExitSub

Dim ap As Excel.Application
Set ap = New Excel.Application
ap.Quit
Exit Sub ' または End Sub
' タスクマネージャーでエクセルが終了することが確認できる

実のところ、Applicationオブジェクト以外に登場人物がいない場合、
Quitする必要すらなかったりする。

オブジェクトを設定→いきなりSetNothing

Dim ap As Excel.Application
Set ap = New Excel.Application
Set ap = Nothing
' タスクマネージャーでエクセルが終了することが確認できる

オブジェクトを設定→いきなりExitSub

Dim ap As Excel.Application
Set ap = New Excel.Application
Exit Sub ' または End Sub
' タスクマネージャーでエクセルが終了することが確認できる

しかし、これは大変よろしくない。

`Excel.Application`の、実用上のお話

実用上は、ブックを開いて作業するために Application オブジェクトを作成するからである。
(まあ、 Excel のマクロの範囲で Application オブジェクトを作る場合自体、やたら重いから途中で中断できるためにとか、そういう理由がないとないんだが。別の Office アプリとか VBSから Excelファイル作るとかでも使うけど)

そして、ブックを開いている Application オブジェクトを
ブックを開いたままSet Nothing すると、プロセスが生き残ってしまう。

オブジェクトを設定→ブックを開く→いきなりSetNothing

Dim ap As Excel.Application
Set ap = New Excel.Application
ap.Workbooks.Add
Exit Sub ' または End Sub または Set Nothing
' タスクマネージャーでエクセルが終了 **しない** ことが確認できる

用途はわからないけど、Workbook オブジェクトのプロパティに Application がある。
コイツが、Workbookオブジェクトが、自分を開いている Application への参照を保持するために、残ってしまうのだろう。

正確に言えば、ブックを Close せずに処理を終了すると、 Application は残ってしまうということで...
なおかつ、上記の処理では、AddしたWorkbookオブジェクトは、どこの変数にも保存していないけど生存してるわけで...

オブジェクトを設定→ブックを開く→いきなりQuit→いきなりSetNothing

Dim ap As Excel.Application
Set ap = New Excel.Application
ap.Workbooks.Add
ap.Quit
Exit Sub ' または End Sub または Set Nothing
' タスクマネージャーでエクセルが終了することが確認できる

Quitしとけば、終了後にプロセスも終了する

ただし、Add で生成されたWorkbook に対して、何かしらの変更を加えた場合は、
表示はされないが「変更あるけど保存します？」の状態になって生き残り続ける。

Dim ap As Excel.Application
Set ap = New Excel.Application
Dim bk As Excel.Workbook
Set bk = ap.Workbooks.Add
bk.Sheets(1).Cells(1,1) = "何かしらの変更"
ap.Quit
Exit Sub ' または End Sub または Set Nothing
' タスクマネージャーでエクセルが終了 **しない** ことが確認できる

Application.Workbooks と workbook.Close

ウォッチに「ap.Workbooks.Count」を追加して、眺めながら実行してみよう。

Sub x()
  Dim ap As Excel.Application
  Set ap = New Excel.Application
  ' ap.Workbooks.Count : 0

  Dim bk As Excel.Workbook
  Set bk = ap.Workbooks.Add
  ' ap.Workbooks.Count : 1

  Call bk.Close(False)
  ' ap.Workbooks.Count : 0

  ap.Quit

  Set ap = Nothing
End Sub

application.Workbooks.Count > 0のときに、
保持している workbook をぜーんぶ Close してから
Application.Quit すればよかとよ。

ちなみに、Set bk = Nothing をいきなり呼び出しても、bk.Application(=ap) が保持するのよね。
Closeは呼び出しが必須。

ちなみに以下ではエラーは発生しない

Quitを多重呼び出し

Dim ap As Excel.Application
Set ap = New Excel.Application
ap.Quit
ap.Quit ' Quit二回呼んだ! でもエラーはおきない。
Exit Sub ' または End Sub または Set Nothing

このため、処理の最後で記述する Quit のために If 文を書く必要は、あまりない。
何度呼び出してもいいぞ。(一回呼び出せばいいけど)

結論としては

場合にもよるかも知らんが大筋は以下

Application オブジェクト(以下 application) を確実に開放するために、 Quitはしたほうがよい。
- ただし、application が保持する Workbook オブジェクト(以下 workbook)がすべて閉じられていることが保証されている場合に限り、Set Nothing や、スコープ外への脱出によりいきなり開放されてもプロセスは死んでくれる。
Quitする際には、application が保持する workbook を閉じる処理が同時に発生する。
- このとき、残念なことに保持されている workbook では、変更を保持するか確認する処理が、場合によっては表示外で行われる
- このため、 workbook を保持したままの application を Quit すると、その後スコープ外へ出ようとも"元"application のプロセスが維持されてしまう。
application.Workbooks がすべて閉じられていればよいので、予め workbook を閉じることを徹底していればよい。
- workbook を閉じるには Close を使用する。
  - メッセージ出さずに、保存しないで閉じるなら、Close(SaveChanges:=False)
  - メッセージ出さずに、保存してから閉じるなら、
    - 先に Save とか SaveAs とかしてから Close(SaveChanges:=False)
    - あるいは、Close(SaveChanges:=True,FileName:="ブック名")
  - 保存の是非を問う必要があるなら、
    - application.Visible = True した上で Close(SaveChanges:=True)(既定動作)を呼び出す。
    - 保存の是非を問うということは、必要かどうか人の目で判断が必要ということだと思われるので、そもそも閉じない。(Quitもしない)でapplication.Visible = Trueを確実に呼び出して終了する。
  - Closeしたことの確認には、TypeName(workbook) <> "Workbook"を使用する。
    - Set Nothing する必要はない。速度差は出ないだろうけど、行数が増える。
- application.Workbooks がすべて閉じられていることの確認には、application.Workbooks.Count = 0 を使用する。

そんなところです。

Codeでうｐしろ

ほい

Sub Sample()
  Dim xap As Excel.Application
  Set xap = New Excel.Application
  Dim xbk As Excel.Workbook
  Set xbk = xap.Workbooks.Add

  ' bla-bla-bla

errorExit:
  If TypeName(xbk) = "Workbook" Then
    Call xbk.Close(False) ' 保存せずに閉じる場合
  End If

  If TypeName(xap) = "Application" Then
    Do Until xap.Workbooks.Count = 0
      Call xap.Workbooks(1).Close(False) ' 保存せずに閉じる場合
    Loop
    xap.Quit
  End If
End Sub

VBAのあれこれ(雑記

Excel.WorkBook オブジェクトを Close しては Set Nothing するな

workbook.Closeを二度呼ぶと...

Set Nothing お作法

Set Nothing はいらない