Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(指定なし : Excel 2013)
subプロシージャに渡す参照渡し変数の解放について
投稿日時: 18/05/28 22:21:11
投稿者: ピバくん

皆様初めまして。
vbaは初心者です。
 
繰り返し処理をsubプロシージャを使っています。
 
ワークシート変数と、他の変数を引数として参照渡ししていますが、subプロシージャ終了時に
set オブジェクト変数 = nothing でメモリを解放したほうが良いのでしょうか?
 
初歩的な質問で恐縮ですが、どなたかご教授よろしくお願いします<m(__)m>
 

回答
投稿日時: 18/05/28 22:39:47
投稿者: WinArrow
投稿者のウェブサイトに移動

抽象的な質問では、判断できないところがあります。
 
具体的例を挙げてみてください。
パターンが複数あるのでしたら、具体例は、できれば多くあった方がよいと思います。

回答
投稿日時: 18/05/28 23:00:58
投稿者: simple

ご指摘のとおりです。
 
あと、
「Set a = Nothing」のお話
http://www.moug.net/tech/exvba/0150027.html
も参考にしてください。
 
# 私自身は、少なくともWorksheetやRangeについて = Nothingとしたことは一度もないです。

投稿日時: 18/05/28 23:06:18
投稿者: ピバくん

失礼いたしました。
検索元シート(20件位)頭から順に
抽出元ファイルに検索をかけて、検索条件ごとに新規ファイルをつくり、該当箇所をコピーしています。
貼り付け処理はイベントプロシージャ内で2回ほど使ってます。呼び出される回数は20回位です。
 
sub 貼り付け処理(検索条件 as string,新規ブック as worksheet,抽出元 as worksheet)
  抽出元ワークシートを検索し、新規ブックに該当箇所を貼り付ける。
end sub
 
このマクロのつくり方がいいかわかりませんが、冗長にならないようにしました。
何かご指摘ありましたら、よろしくお願いします。
 

投稿日時: 18/05/28 23:10:02
投稿者: ピバくん

WinArrow様、simple様、参考サイトとアドバイス有難うございます。
早速勉強させて頂きます。

回答
投稿日時: 18/05/29 01:25:51
投稿者: LMK

こんばんは
 
巨大なデータの入った配列変数は使い終わったらすぐ解放しますが、オブジェクト変数はそれ自体単なる参照であって重いものではないので、解放する実益はないと理解しています。
 
ましてや「End Sub」の直前に解放しても一瞬早まるだけで無意味でしょう。

回答
投稿日時: 18/05/29 08:25:49
投稿者: WinArrow
投稿者のウェブサイトに移動

まだまだ、抽象的な部分があります。
 

ピバくん さんの引用:
失礼いたしました。
検索元シート(20件位)頭から順に
抽出元ファイルに検索をかけて、検索条件ごとに新規ファイルをつくり、該当箇所をコピーしています。
貼り付け処理はイベントプロシージャ内で2回ほど使ってます。呼び出される回数は20回位です。
 
sub 貼り付け処理(検索条件 as string,新規ブック as worksheet,抽出元 as worksheet)
  抽出元ワークシートを検索し、新規ブックに該当箇所を貼り付ける。
end sub
 
このマクロのつくり方がいいかわかりませんが、冗長にならないようにしました。
何かご指摘ありましたら、よろしくお願いします。
 

 
>検索元シート
とは、検索条件が入力されているシートのことですね?
 
>抽出元ファイル
は、抽出元シートのことではないでしょうか?
 
>新規ブック as worksheet
は、データ型の指定がおかしいです。Workbookの方がよいと思います。
 
>貼り付け処理はイベントプロシージャ内で2回ほど使ってます。呼び出される回数は20回位です。
イベントプロシージャ内
って何?
 
>このマクロのつくり方がいいかわかりませんが
これは、コードを作ってくださいというお願いですか?
 
ストーリーがよくわかりませんが、
オートフィルタ、若しくは、フィルタオプションを使用するような処理ではないか
と推測できます。
 
 
 

回答
投稿日時: 18/05/29 08:28:29
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
肝心な、オブジェクト変数の解放
は、どれを対象と考えているんですか?
私見・・・新規ブックと思いますが・・・

回答
投稿日時: 18/05/29 08:35:02
投稿者: mattuwan44

>subプロシージャ終了時に
>set オブジェクト変数 = nothing でメモリを解放したほうが良いのでしょうか?

 
素人の雑感&思い付きです。
 
複雑な物や、エクセルの操作以外が主目的のマクロを作ったことはほぼないのですが、
マクロの終りに、
set オブジェクト変数 = nothing
としなくて不都合が起きたことは一度もないです。
むしろ、絶対にNothingにしようとすると、
最後絶対その行を実行するように書かないといけないので、
コードがすごく窮屈になります。
つまり、途中で Exit sub と書けなくなるので
(書いてもいいけど実行されなくてもいいなら書かなくていいじゃん!)、すごく窮屈になります。
なので、個人的には「書かない。」ことに決めました。
 
逆に、
広域に変数を宣言した場合とかは、
例えば、「ブックを閉じた。」「シートを削除した。」「セルを削除した。」
というようなときに、
 
Public gWB as workbook
 
sub test()
  set gwb = workbooks(1)
  wb.close false
end sub
 
sub メイン()
  test
  if not gwb is nothing then
     msgbox gwb.name
  else
     msgbox "変数が空です。"
  end if
end sub
 
Nothingにしておかないと不都合な場面が出てきますので、
そういう場合のみ、セットで変数をNothingにしなければいけないなーと感じてます。
 
他にも不都合な(エラーが出る等の)場面があったと思いますが、
僕みたいに素人に毛が生えた程度のプログラマーが出会う場面ではないなと思ったので、
覚えてません。
 
まぁ、知識は知識として、経験も加味しながら取捨選択してみたらいいと思います。
参考になれば。

回答
投稿日時: 18/05/29 09:42:42
投稿者: sk

引用:
ワークシート変数と、他の変数を引数として参照渡ししていますが、subプロシージャ終了時に
set オブジェクト変数 = nothing でメモリを解放したほうが良いのでしょうか?

(標準モジュール)
--------------------------------------------------------------------
Sub Main()
 
    Dim ws As Excel.Worksheet
     
    Set ws = ThisWorkbook.Worksheets(1)
     
    Call SetNothing(ws)
     
    MsgBox ws.Name '←ここで実行時エラー 91
 
    Set ws = Nothing
 
End Sub
 
Private Sub SetNothing(MySheet As Excel.Worksheet)
 
    Set MySheet = Nothing
 
End Sub
--------------------------------------------------------------------
 
みたいなことが起こり得るのでは。
 
また、Function プロシージャにおける戻り値のような形で
Sub プロシージャの ByRef 引数を用いるようなケースでは
当然 Nothing を渡す意味がありません。

回答
投稿日時: 18/05/29 11:38:37
投稿者: mattuwan44

>何かご指摘ありましたら、よろしくお願いします。
必要な物をコピーするという考えもありますが、
不要なものを削除するという考え方も出来そうです。
あと、新規ブックはサブルーチンに任せてもいいかも?
 
Sub メイン()
    Dim rngName As Range
    Dim c As Range
    Dim wsOld As Worksheet
     
    Set rngName = Workbooks(1).Sheets(1).Range("A2:A21")
    Set wsOld = Workbooks(2).Sheets(1)
     
    For Each c In rngName
        MakeNew_WithKey c.Value, wsOld
    Next
End Sub
 
Sub MakeNew_WithKey(ByVal sKey As String, ByVal pwsOld As Worksheet)
    pwsOld.Copy
    With Workbooks(Workbooks.Count).Sheets(1).UsedRange
        .AutoFilter Field:=1, Criteria1:="<>" & sKey
        .Offset(1).Delete
        .AutoFilter
        .Worksheet.Parent.Close SaveChanges:=True, Filename:=ThisWorkbook.Path & "\" & sKey & ".xlsx"
    End With
End Sub
 
こういう時のオブジェクト変数のNothingかぁ、なくてもいいと思うけど、気分次第かな^^;
必要な場合があるかも知れんけど。。。それはまたその時に対応するとして。。。。^^;

投稿日時: 18/05/29 23:46:23
投稿者: ピバくん

MK様、WinArrow様、mattuwan44様、sk様、貴重な意見有難うございます。
まだ理解しきれない部分はありますが、一つ一つ吟味していきたいと思っております。
 
コマンドボタンを押すイベントプロシージャで
20件程の検索用シート毎に、新規ブックを作成し、抽出元ファイルに検索をかけて、必要な部分を新規ブックにコピーペーストする処理です。
 
イベントプロシージャ内で、処理が重複している抽出元ファイルに検索をかけて新規ブックに該当箇所をコピペする処理をsubプロシージャで行ってます。
 
sub 繰り返し処理(検索用ワークシート変数、抽出元ワークシート変数、検索毎新規ブック変数)
 
 
検索用シートの検索項目にループ処理をかけてますが、
新規ブック毎(20件程)は同じオブジャクト変数を使っており、
(新規ブック作成し、新規用オブジェクト変数に代入)
新規ブックファイルを開きにくい現象が出てきてます。
イベントプロシージャ内の新規ファイル用オブジェクト変数を開放するコードを付け加えた方がよさそうな気がしてます。説明が足りない部分があるかとは思いますが、皆様の意見大変参考になります。
 

回答
投稿日時: 18/05/30 07:24:48
投稿者: simple

具体的なコードを提示されれば、皆さんから更に意見がもらえると思います。

回答
投稿日時: 18/05/30 08:53:56
投稿者: mattuwan44

>新規ブック毎(20件程)は同じオブジャクト変数を使っており、
>(新規ブック作成し、新規用オブジェクト変数に代入)
>新規ブックファイルを開きにくい現象が出てきてます。
変数の状態でファイルを開きにくいということは余り考えにくいので、
単にファイルが重くなってだけかと?
ファイルサイズをみて、変に大きすぎるとかだったら、
不要な名前の定義やスタイルが溜まってきてないかとか、
チェックしてスリム化したらいいと思います。
 
また、「同じオブジェクト変数」というのがどういう事なのか想像しにくいので、
実際のコードを包み隠さず提示してみてはいかがでしょうか?
ご自分ではデバッグできない、または判断出来ないなら、
一般論を論じていても解決には至らないと思います。

回答
投稿日時: 18/05/30 13:22:48
投稿者: 半平太

>イベントプロシージャ内の新規ファイル用オブジェクト変数を
>開放するコードを付け加えた方がよさそうな気がしてます。
 
なら、実際にやってみてください。その方が手っ取り早いじゃないですか。(※)
 
そして、その効果をここで披露して頂けませんか? 貴重な体験談になります。
 
Set Nothingに如何ほどの神通力があるかについては、2派に分かれています。
人に聞いても、決定的な結論を得る事は出来ません。
 
(※)以下の前提下です。
1.用済みになったブックは、こまめにCloseしてある。
 
2.Nothingをセットしたら、バグるなんてフローではないこと。

投稿日時: 18/05/30 21:24:10
投稿者: ピバくん

imple様、mattuwan44様、半平太様、アドバイス有難うございます。
 
新規ブック作成、新規ブック用オブジェクト変数が使い終わった際にNotingを代入してみたのですが、
ファイルを開くのは相変わらず遅かったです。
PGで使用以外のファイルも遅いので、単なるスペックの問題かもしれないです・・・お世話おかけいたしました。
 
>変数の状態でファイルを開きにくいということは余り考えにくいので、
>単にファイルが重くなってだけかと?
 
>1.用済みになったブックは、こまめにCloseしてある。
 
10件程あるマクロで作成した新規ブックは開きっぱなしなので、PC処理能力とか、環境の原因かもしれないです。
エクセルのバージョンは2013年、マクロで作成してるブックの拡張子は、.xls(97〜2003?)
ファイルを開くときに、メッセージが出てきます。(メッセージ内容は正確に覚えてません。)
 
コードは長くなりそうなので、時間があるときにUPします。

回答
投稿日時: 18/05/30 22:22:02
投稿者: simple

変数を Nothingとするより、ファイルをこまめにcloseするのが先決です。

投稿日時: 18/05/31 23:20:26
投稿者: ピバくん

simple様
 
アドバイスありがとうございます。
以前から疑問だった変数の解放が、皆様の意見で大変参考になりました。
今後に活かしたいと思います。<m(__)m>