[[20220208220916]]　『参照渡しのオブジェクト変数は解放が必要？』（you）　ページの最後に飛ぶ

『参照渡しのオブジェクト変数は解放が必要？』（you）

教えてください。

自分が作ったマクロではないものを色々確認しながらコード整理を行っています。
その際に「幽霊プロジェクト」現象が起きていることが分かりました。

自Bookから他Bookを開きデータを集めるマクロになりますが、
Sub Test(FileName AS Workbook) As String　のような、
参照渡しのために設定されているオブジェクト変数も解放が必要でしょうか？

とりあえず、参照渡し以外で使用されているオブジェクト変数は解放（Nothing）してみたのですがプロジェクトは消えませんでした。
このことから、もしかして解放した方がいいのかなと疑っていますが、何分自分は参照渡しを使ったことがないので知識がなく困っています。

以上、よろしくお願いします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

よく解らんけど、元の方で握っている？
その辺区別した方がいいのでは？
（握り） 2022/02/08(火) 23:35

 正確な状況説明がないと何とも言えないですよ。

 FileNameオブジェクトは何処でどう定義したのですか？(モジュールレベル、プロシージャレベル？）
 そのブックは何処でクローズしたんですか？

 ＞オブジェクト変数は解放（Nothing）してみた
 どこでどうやったんですか？(Callした方？　Callされた方？）

 いずれにしても、参照渡しとか言うものが直接問題を惹起しているとも思えないです。
 なんならByValに変えてみたらどうですか？（多分、何の違いもないと思います）

（半平太） 2022/02/09(水) 00:00

返信ありがとうございます。

ブックＡ・・・マクロ本体のあるブック
ブックＢ・・・情報を集めたいブック
ブックＣ他・・・情報が記載されているブック

１．ブックＡのシート１に設定されているボタンを押すとマクロが動きます。
２．ブックＢを開き、ブックＣ他数十あるブックを開いて様々なデータをコピーします。
３．コピー終了後はブックＢを閉じ、開いているのはブックＡのみとなります。

このながれで、閉じたはずのＢの名前がブックＡのプロジェクトエクスプローラーに残ります。

ブックＡのコードはsheetモジュールに以下のように書かれています。

dim Sheet as worksheet

Sub　作成

  Set Sheet = Workbooks("BookB").Worksheets("Sheet1")
　Call　Test（Sheet）
　***何かの作業***
End Sub

Sub Test(FileName AS Workbook) As String　

  ***何かの作業***
End Sub

Sub Copy()

 dim WSName AS Worksheet
 Set WSName = Workbooks("BookC").Worksheets("Sheet1")
 ***何かの作業***
End Sub

このような感じで書かれていたので、

Sub　作成　’ブックＡのシート１に設定されているボタンを押すと動く

  Set Sheet = Workbooks("BookB").Worksheets("Sheet1")
　Call　Test（Sheet）
　***何かの作業***
  Set Sheet = Nothing ←　追加
End Sub

Sub Test(FileName AS Workbook) As String　

  ***何かの作業***
End Sub

Sub Copy()

 dim WSName AS Worksheet
 Set WSName = Workbooks("BookC").Worksheets("Sheet1")
 ***何かの作業***
 Set WSName = Nothing  ←　追加
End Sub

という感じで修正してみましたが、プロジェクトは残ったままという状態でした。
（you） 2022/02/09(水) 07:58

単に、ブックＢを閉じていないだけのように思います。

＞閉じたはずのＢ

閉じたことをどのように確認されたのでしょうか。
コード中には示されていません。

（わからん） 2022/02/09(水) 08:13

 現象に興味はありますが、再現できないし、今提示されているコードでは手がかりがありません

 ・BookBを開くコードがない/BookBを閉じるコードもない
 ・BookCも開くコードがない/BookCを閉じるコードもない
 ・Sub Copy() は定義されているが、どこからも呼ばれていない

 回答を得るために、コードを切り出す場所が適切に選べていない ということは
 デバッグも的を射ていない場所を見てるのではないかと想像してしまいますね
（´・ω・`） 2022/02/09(水) 08:54

ブックに対して Closeメソッドを使って閉じていないんじゃないですか？
Excel内のオブジェクトは基本的にNothingにする必要はないと思いますよ。
（γ） 2022/02/09(水) 09:07

 全体的にコード間の整合性が取れてないです。

 ＞Sub Test(FileName AS Workbook) As String
 １．As String　なんてついているので、SubじゃなくFunctionですよね。
 ２．FileNameの型はWorkBookなのに、渡しているのはWorksheet型ですよ。
 ３．SheetモジュールにCopyメソッドを書くとコンパイルエラーになります。
 あと、終了直前に「プロシージャレベル」のオブジェクト変数にNothinを入れるのは無駄です。

 以下のサンプルなら再現します。

 Dim WB As Workbook’モジュールレベルのオブジェクト変数

 Sub 作成()
     Dim WbBkNameB As String

     WbBkNameB = ThisWorkbook.Path & "\BookB.XLSX"
   Set WB = Workbooks.Open(WbBkNameB)
   Call Test(WB)

   WB.Close False
   ’Set WB = Nothing　’←ここのコメントを外せば消えます
 End Sub

 Function Test(FileName As Workbook) As String
     Dim WbBkNameC As String
     Dim SheetC1 As Worksheet

     WbBkNameC = ThisWorkbook.Path & "\BookC.XLSX"
     Set SheetC1 = Workbooks.Open(WbBkNameC).Worksheets(1)
     SheetC1.Range("A1").Value = "Trial"
     Call MyCopy(SheetC1)
     SheetC1.Parent.Close False
 End Function

 Sub MyCopy(SheetC1 As Worksheet)
  SheetC1.Range("A1").Copy WB.Sheets(1).Range("E3") 'TrialをBookBのE3に貼付け
 End Sub

 なお、値渡し(byVal）にしても現象は同じです。

（半平太） 2022/02/09(水) 10:37

 肝心の話が抜けていた・・

 要所要所でSet Nothingにしたのに、消えないと言う事が問題でした。

 なので、上のような(消せる）サンプルでは議論できません。

 当該現象が再現するサンプルを提示していただかないことには、
 推測ばかりが先行するので回答側も疲れてしまいます。

（半平太） 2022/02/09(水) 10:47

 >「幽霊プロジェクト」現象

エクセル奇譚|幽霊プロジェクトの怪
http://hp.vector.co.jp/authors/VA016119/kitan01.html#11

 これの話ですよね。

    Option Explicit

    Private wb As Workbook

    Sub Test1()
        Debug.Print " VarPtr", " ObjPtr"
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test1 最初"
        Set wb = Workbooks.Add
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test1 wb Set後"
        Call Test2(wb)
        Call Test3(wb)
        Call Test4
        Stop 'プロジェクトエクスプローラーにwbが残ってる
        Set wb = Nothing
        Stop 'プロジェクトエクスプローラーからwbが消える
        Debug.Print
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test1 wb 解放後"
    End Sub
    Private Sub Test2(ByRef wb2 As Workbook)
        Debug.Print
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test2 wb"
        Debug.Print VarPtr(wb2), ObjPtr(wb2), "Test2 wb2(ByRef)"
    End Sub
    Private Sub Test3(ByVal wb3 As Workbook)
        Debug.Print
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test3 wb"
        Debug.Print VarPtr(wb3), ObjPtr(wb3), "Test3 wb3(ByVal)"
    End Sub
    Sub Test4()
        Debug.Print
        wb.Close False
        Debug.Print VarPtr(wb), ObjPtr(wb), "Test4 wb.Close後"
    End Sub

  ↓実行結果

 VarPtr        ObjPtr
 193223960     0            Test1 最初
 193223960     124973656    Test1 wb Set後

 193223960     124973656    Test2 wb
 193223960     124973656    Test2 wb2(ByRef)

 193223960     124973656    Test3 wb
 1373244       124973656    Test3 wb3(ByVal)

 193223960     124973656    Test4 wb.Close後

 193223960     0            Test1 wb 解放後

（白茶） 2022/02/09(水) 19:15

皆様情報が少ない中、色々ありがとうございます。
まとめて返信になってしまいもうしわけありません。

＞全体的にコード間の整合性が取れてないです。

質問している側が間違った例を挙げてしまい、申し訳ないです。

＞回答を得るために、コードを切り出す場所が適切に選べていないということは
＞デバッグも的を射ていない場所を見てるのではないかと想像してしまいますね

自分が作ったものではないコードを見るのも初めてな上、現象も初めてで、
どこを見ていいかも正直よくわかっていないところです。

＞ブックに対して Closeメソッドを使って閉じていないんじゃないですか？

CloseについてもSubが３つ（いずれもブックの情報を参照渡し設定）あり、
　１．アラートを止めてブックの保存
　２．アラートを止めてブックを保存せずに閉じる
　３．１と２を呼び出す　←　メインである「作成」で呼び出しているコード
というちょっと私にはよくわからない仕様になっています。
確かに「保存して閉じる（ブックB）」と「保存しないで閉じる（ブックC以降）」の２種類の動作は必要ですが、なんで回りくどい事をしているのかな？と思っています。
（私が「参照渡し」や「値渡し」の利点がよくわかっていないからかもしれません）

白茶さんの提示されたコードが現状に近いものになります。
本当は、Test1が終わったら解放されているはずなのに残っているという事は、
やはり何か見落としがあるという事ですね。

コードを載せて皆様のお力を借りたい気持ちはあるのですが、持ち出しNGなのです。
（在宅ではないので、目の前にもコードがない。。。）
ただ、プロジェクトに残っていないのが正しい、という事がわかっただけでも助かりました。

上司には現状の説明と、最悪コード作り直しの提案もしていきたいと思います。
（you） 2022/02/10(木) 07:42

 当たり前な事なので、そんなの分かってるよ！、な場合はスルーしてください。

 ＞最悪コード作り直しの提案もしていきたいと思います。

 どこぞのブックを開いて、そのブックのデータを取得する場合、
 普通にOpenしている方々が多いと感じています。

 そんな場合、読み取り専用で開く事を強くお勧めします。
 基となるデータを失う訳にはいきませんからね。

（tkit） 2022/02/10(木) 09:32

 Windows10 Pro(21H1)、Microsoft Excel 2019 MSO (バージョン 2201 ビルド 16.0.14827.20186) 64 ビット で、
 同じような現象が起きています。
 以下のコードを実行した時、test.xlsxのVBAProjectが残ったままになり、
 実行する度、増えていきます。
 コード実行したブックを含めて、ブックを全て閉じれば消えます。

 Sub test()
    Dim wb As Workbook
    Set wb = Workbooks.Open("D:\test\test.xlsx")
    wb.Close False
    Set wb = Nothing
 End Sub

 test.xlsxのウィンドウを閉じるボタンで閉じた場合は、VBAProjectは消えます。
 Windows10 Excel2016（買い切り版）では起きなかったので、
 いわゆる「おま環」でしょうか・・・。
（三文） 2022/02/11(金) 19:12

 補足です。
 保存前のブックでコードを実行した場合、
 VBAProjectは残りませんでした。
 保存したマクロブックでコードを実行した場合、
 同一フォルダのtest.xlsx、別フォルダのtest.xlsx、ともに現象は再現しました。
（三文） 2022/02/11(金) 19:21

 そんな単純なコードで発生するなら、BVEの作りがお粗末だってことじゃないですかね。 
 表示されたままだ/消えた、なんて騒ぐのが無意味に感じられる。

 表示されたままで何か困るんですかね？
 更新系がバグってるな、くらいでいいんじゃないですか。

（半平太） 2022/02/11(金) 22:58

 実務でコードを実行する時まで、VBEを開いているわけではないでしょうし、
 VBAProjectが残ったままによる弊害も、質問者さんの投稿には記述されていないように見受けます。
 私の環境でもVBAProjectが残ったままの状態で、いくつか別のコードを実行してみましたが、
 特に問題があるようなことはありませんでした。

 あくまでも「コードが原因で起きている現象では無い」と仮定した場合ですが、
 ＞最悪コード作り直しの提案
 というような大事にすることもないのかな、とは思います。
（三文） 2022/02/12(土) 07:26

情報ありがとうございます。
今回の質問のきっかけは、ブックAのマクロを始めると終了までに15分程度かかるため、速度改善できないか確認している最中に「残っているプロジェクト」が気になったからです。
（速度改善のための原因は判明していて、こちらは別途コードを作成して検証中です）

＞どこぞのブックを開いて、そのブックのデータを取得する場合、
＞普通にOpenしている方々が多いと感じています。
＞そんな場合、読み取り専用で開く事を強くお勧めします。
先代の担当者さんもExcelをあまり知らない方だったのか、読み取り専用だと操作できないと思っていた節がありました。
おっしゃる通り、データを失う事はしたくないのでこれも提案しようと考えています。

＞ Windows10 Pro(21H1)、Microsoft Excel 2019 MSO (バージョン 2201 ビルド 16.0.14827.20186) 64 ビットで、
＞同じような現象が起きています。
今の環境はまさにこれです。
ブックAを保存しても残ったままですが、閉じる→再度開くと消えています。
これだと、半平太さんや三文さん言う通り、対策したうえで残るのであれば気にしないのが一番だと思いました。
（弊害については「よくわからない」というのが現状です。）
個人的には、以降のバージョンで解消されると良いなと思うのですが。。。
（you） 2022/02/12(土) 12:05

私の環境は、
Windows10
Microsoft Excel 2019 MSO (バージョン 2201 ビルド 16.0.14827.20028) 32 ビット
とやや近いですが、
（三文）さんの 2022/02/11(金) 19:12 のような事象は再現しませんね。
未保存、保存済みいずれでも発生しませんでした。

（γ） 2022/02/12(土) 12:17

 Microsoftコミュニティでも同様の報告がありました。
https://answers.microsoft.com/ja-jp/msoffice/forum/all/excel%e3%83%90%e3%83%bc%e3%82%b8%e3%83%a7%e3%83%b3/04ec04be-39b0-471b-b428-bce662056371

 ＞youさん
 速度改善の件、原因判明しているとのことですが、コミュニティの方で、
 ＞今まで問題なく動作していたマクロを動作させると使用メモリーが増大し、処理が遅くなる、
 ＞または応答なしとなり処理できなくなります。
 という報告があったことを一応お知らせしておきます。

 ＞γさん
 検証のご報告、ありがとうございました。
（三文） 2022/02/13(日) 15:07

 実害が出る例があるんですね・・

 オブジェクト変数が全く使われてないそのコードで発生するとなると、
 オブジェクト変数のポインターがどうだこうだと言う問題でもなさそう。

 全て「信頼できる場所」で処理しろと言うことなんですかね。

（半平太） 2022/02/13(日) 19:14

 ＞全て「信頼できる場所」で処理しろと言うことなんですかね。
 ですよねぇ。
 一応、Excelでもリンク先の回避策で対応出来ています。

 ＞ 【回避策1】
 ＞Access の [ファイル] > [オプション] > [トラストセンター] > [トラストセンターの設定] > [信頼出来る場所] >[新しい場所の追加] で、Access のデータベースがあるフォルダー（フロントエンド及びバックエンド）を追加します。
 ＞（※「この場所のサブフォルダーも信頼する」にはチェック

 ただ、γさんの報告にもある様に、全てのバージョン 2201ユーザーに起きているわけでも無さそうですし、
 なんだか良く分かりませんね・・・。
（三文） 2022/02/13(日) 19:42

γです。
私は、よく使うフォルダとその配下のすべてのサブフォルダは
「信頼できる場所」に登録してありました。

指定から外してみると、私の
バージョン 2201 ビルド 16.0.14827.20028) 32 ビット
でも幽霊が再現しました。
ご報告まで。

（γ） 2022/02/13(日) 20:44

貴重な情報ありがとうございます。お陰で助かりました。

私のＰＣを確認したら当該バージョンになっていましたので、
トラブルが出る前に対処しました。
２つ前のバージョン 2112 (ビルド 14729.20260)へロールバックして
更新停止とし、修正アナウンス待ちしようと思います。

この位の重大事なら、近いうちに Msdn/Officeサポートチームのフォーラム
にアナウンスが載るのではないかと思います。
https://social.msdn.microsoft.com/Forums/ja-JP/home?forum=officesupportteamja

（AddinBox_角田） 2022/02/13(日) 21:24

三文さん

たびたびの情報、ありがとうございます。
確かに、１度だけ「応答なし」となりそのまま戻ってこないことがありました。
その出来事の数日後にこの現象に気付いたので、影響ありそうですね。

γさんからの「信頼できる場所」は明日にでも設定してみようと思います。
とはいえ、ファイルサーバー上だとこの設定は出来なかったような気がしますので、一旦ローカルで試してみます。
（you） 2022/02/14(月) 22:29

コメント返信：

[ 一覧(最新更新順) ]

[[20220208220916]] 『参照渡しのオブジェクト変数は解放が必要？』（you） ページの最後に飛ぶ

[[20220208220916]]　『参照渡しのオブジェクト変数は解放が必要？』（you）　ページの最後に飛ぶ