Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


9227 / 13644 ツリー ←次へ | 前へ→

【28395】メモリ使用量 EXCEL_VBA中の下 05/9/5(月) 14:34 質問[未読]
【28396】Re:メモリ使用量 ちくたく 05/9/5(月) 14:53 発言[未読]
【28400】Re:メモリ使用量 小僧 05/9/5(月) 16:16 発言[未読]
【28401】Re:メモリ使用量 EXCEL_VBA中の下 05/9/5(月) 16:37 発言[未読]
【28403】Re:メモリ使用量 小僧 05/9/5(月) 17:39 発言[未読]
【28415】Re:メモリ使用量 ichinose 05/9/6(火) 7:59 発言[未読]
【28427】Re:メモリ使用量 EXCEL_VBA中の下 05/9/6(火) 14:41 発言[未読]
【28447】Re:メモリ使用量 実験1 ichinose 05/9/6(火) 20:28 発言[未読]
【28448】Re:メモリ使用量 実験2 ichinose 05/9/6(火) 20:49 発言[未読]
【28451】Re:メモリ使用量 補足 ichinose 05/9/7(水) 6:56 発言[未読]
【28452】Re:メモリ使用量 補足2 ichinose 05/9/7(水) 8:06 発言[未読]
【28453】Re:メモリ使用量 補足2 ちくたく 05/9/7(水) 8:47 回答[未読]
【28488】Re:メモリ使用量 補足2 よろずや 05/9/7(水) 22:33 発言[未読]
【28499】Re:メモリ使用量 補足2 ichinose 05/9/8(木) 8:32 発言[未読]
【28537】Re:メモリ使用量 補足2 よろずや 05/9/8(木) 19:29 発言[未読]
【28541】Re:メモリ使用量 補足2 小僧 05/9/8(木) 22:18 質問[未読]
【28542】Re:メモリ使用量 補足2 よろずや 05/9/8(木) 22:49 発言[未読]
【28544】Re:メモリ使用量 補足2 小僧 05/9/9(金) 0:12 質問[未読]
【28545】Re:メモリ使用量 補足2 ichinose 05/9/9(金) 1:36 発言[未読]
【28555】Re:メモリ使用量 補足2 小僧 05/9/9(金) 10:39 質問[未読]
【28570】Re:メモリ使用量 補足2 よろずや 05/9/9(金) 12:59 発言[未読]
【28597】Re:メモリ使用量 補足2 ichinose 05/9/9(金) 17:20 発言[未読]
【28598】Re:メモリ使用量 補足2 小僧 05/9/9(金) 17:47 お礼[未読]
【28402】Re:メモリ使用量 ちくたく 05/9/5(月) 16:51 発言[未読]

【28395】メモリ使用量
質問  EXCEL_VBA中の下  - 05/9/5(月) 14:34 -

引用なし
パスワード
   マクロを何度も実行していて、処理が重くなって気が付いたのですが、
「タスクマネージャー」→「プロセス」で確認したところ、
EXCEL.EXEのプロセスが、かなりメモリを使用していることが判明しました。

処理が重くなってきたのはこのせいでしょうか?
マクロの記述で問題があるからメモリが増えてしまうのでしょうか?
メモリ開放?のような命令があり、それを行えば解消される問題なのでしょうか?

使用すればするほど重くなるので困っています。
よろしくお願いします。

【28396】Re:メモリ使用量
発言  ちくたく  - 05/9/5(月) 14:53 -

引用なし
パスワード
   こんにちは。
あまり詳しくありませんので、回答ではありません。

普通に考えたら、プロシジャーレベルで宣言した変数とかは、
End Subまで到達した時点でメモリ上からクリアされると思います。
そういう仕様でなかったら、Excelは阿呆なソフトだと思います。

さて、問題はインスタンスを生成する場合だと思います。
なんとなくですが、
Dim illApp As New Illustrator.Applicationとか、
CreateObjectした場合には、メモリ上に展開されたまま(?)
のような気がします。
こういうことをたくさんしてたらメモリはやられる気がします。

また、SetステートメントでObjectを生成した場合も、
もしかしたら、メモリ上に残るのかもしれません。
まぁ、この場合は、最後に、
Set myVal = Nothing
してやればいいのでしょうが。

問題がなければコードを呈示して頂けたら、
もっといい突っ込みが、識者の皆様から入るかもしれません。

【28400】Re:メモリ使用量
発言  小僧  - 05/9/5(月) 16:16 -

引用なし
パスワード
   ▼EXCEL_VBA中の下 さん、ちくたく さん:
こんにちは。

Access に関してですが、Set xx = Nothing のメモリ解放について
検証されているサイトがあります。

http://www.tsware.jp/labo/labo_19.htm


>Set myVal = Nothing
>してやればいいのでしょうが。

としても、myVal の参照が切れるだけで
メモリの解放とは別物だった気がします。


>問題がなければコードを呈示して頂けたら、
>もっといい突っ込みが、識者の皆様から入るかもしれません。

コード次第では解決できる問題かもしれないですね。

【28401】Re:メモリ使用量
発言  EXCEL_VBA中の下  - 05/9/5(月) 16:37 -

引用なし
パスワード
   ▼ちくたくさん、小僧 さん:
よろしくお願いします。

>http://www.tsware.jp/labo/labo_19.htm
 読みましたがよくわかりませんでした・・。すみません。

以下マクロを記述いたします。
質問「 【28269】とある条件のレコードだけ合計が必要な場合 」からの延長です。
細かい部分はこちらも参考してください。

素人なのでいろいろ突っ込む部分があるかと思います。
今回の質問外でも、
 「ここは、こうした方が見易い」
 「この方が効率的」
 「この方が美しい」
などありましたら、ご指摘お願いします。

【module1】
Sub MAKE_UA()
  Dim rs As Object
  Dim mysql As String
  Dim IN_DATA_SHEET_NM As String
  Dim OT_Folder_NM As String
  Dim OT_File_NM As String
  
  Application.Cursor = xlWait
  Application.StatusBar = "しばらくお待ちください"
  Application.ScreenUpdating = False
  Sheets("MAKE_DATA").Activate
  ActiveSheet.Range("A1:IV65535").Select
  Selection.Clear
  Sheets("WORK").Activate
  ActiveSheet.Range("A1:IV65535").Select
  Selection.Clear
  Sheets("設定").Activate
  IN_DATA_SHEET_NM = ActiveSheet.Range("C2")
  OT_Folder_NM = ActiveSheet.Range("C3")
  If Right(OT_Folder_NM, 1) <> "\" Then
    OT_Folder_NM = OT_Folder_NM & "\"
  End If
  OT_File_NM = ActiveSheet.Range("C4")
  
  If Open_ADO_Excel(ThisWorkbook.FullName) = 0 Then
    mysql = "Select [FLG],[YYYY],[MM],[CODE_A],[CODE_B],[CODE_C],[CODE_D],[KIN-SEI] " & _
        " From [" & IN_DATA_SHEET_NM & "$] " & _
        " Where [CODE_D] <> '03' " & _
        "  AND [CODE_D] <> '04' " & _
        "Union All " & _
        "Select [FLG],[YYYY],[MM],[CODE_A],[CODE_B],[CODE_C],[CODE_D],Sum([KIN-SEI]) " & _
        " From [" & IN_DATA_SHEET_NM & "$] " & _
        " Where [CODE_D] = '03' " & _
        "  Or [CODE_D] = '04' " & _
        " Group By [FLG],[YYYY],[MM],[CODE_A],[CODE_B],[CODE_C],[CODE_D] " & _
        " Order By [YYYY],[MM],[CODE_A],[CODE_B],[CODE_C],[CODE_D] "
    If Get_Exec_SQL(mysql, rs) = 0 Then
      With Worksheets("WORK")
        .Cells.ClearContents
        .Range("A2").CopyFromRecordset rs
        .Range("A1:H1").Value = Array("UA", "YYYY", "MM", "CODE_A", "CODE_B", "CODE_C", "CODE_D", "KIN-SEI")
      End With
      Call rs_Close(rs)
      Call Close_ADO
    Else
      Call Close_ADO
      GoTo ERR_RTN
    End If
  Else
      GoTo ERR_RTN
  End If
  Sheets("WORK").Activate
  ActiveSheet.Range("B2:H2").Select
  ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Sheets("MAKE_DATA").Activate
  ActiveSheet.Range("A1").Select
  ActiveSheet.Paste
  ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
'ファイル出力
  Application.DisplayAlerts = False
  Sheets("MAKE_DATA").Activate
  Sheets("MAKE_DATA").Copy
  ActiveWorkbook.SaveAs Filename:=OT_Folder_NM & OT_File_NM & ".CSV", FileFormat:=xlCSV, CreateBackup:=False
  ActiveWindow.Close
  Application.DisplayAlerts = True
  Sheets("設定").Activate
  Application.ScreenUpdating = True
  Application.StatusBar = False
  Application.Cursor = xlDefault
  MsgBox OT_Folder_NM & OT_File_NM & ".CSVを作成しました。"
  
  Exit Sub
  
ERR_RTN:
  Application.DisplayAlerts = False
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
  Application.StatusBar = False
  Application.Cursor = xlDefault
  MsgBox "作成に失敗しました。"
End Sub
'======================================================

【module2】

Public cn As Object
'======================================================
Function Open_ADO_Excel(Book_Fullname As String) As Long
  On Error Resume Next
  link_opt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & Book_Fullname & ";" & _
       "Extended Properties=Excel 8.0;"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open link_opt
  Open_ADO_Excel = Err.Number
  On Error GoTo 0

End Function
'======================================================
Sub Close_ADO()
  On Error Resume Next
  Set cn = Nothing
  cn.Close
  On Error GoTo 0
End Sub
'======================================================
Function Get_Exec_SQL(SQL_Str, rs As Object) As Long
  On Error Resume Next
  Set rs = cn.Execute(SQL_Str)
  Get_Exec_SQL = Err.Number
  If Err.Number <> 0 Then
    MsgBox Err.Number & "::" & Err.Description
    End If
  On Error GoTo 0
End Function
'======================================================
Sub rs_Close(rs As Object)
  On Error Resume Next
  Set rs = Nothing
  rs.Close
  On Error GoTo 0
End Sub

【28402】Re:メモリ使用量
発言  ちくたく  - 05/9/5(月) 16:51 -

引用なし
パスワード
   小僧 さん
こんにちは。

>Access に関してですが、Set xx = Nothing のメモリ解放について
>検証されているサイトがあります。
>http://www.tsware.jp/labo/labo_19.htm

なるほど、Nothingじゃだめなのですね。

Dim myObj As Object
Set myObj = Range("A1")
Set myObj = Nothing

みたいにすると、ローカルウインドウ上では、
解放されたように、見えたので、
今までずっとメモリ上からなくなってると思っていました。
勉強ですね。

【28403】Re:メモリ使用量
発言  小僧  - 05/9/5(月) 17:39 -

引用なし
パスワード
   ▼EXCEL_VBA中の下 さん:
こんにちは。

とりあえず冒頭の部分だけ…

>  Sheets("MAKE_DATA").Activate
>  ActiveSheet.Range("A1:IV65535").Select
>  Selection.Clear
>  Sheets("WORK").Activate
>  ActiveSheet.Range("A1:IV65535").Select
>  Selection.Clear
>  Sheets("設定").Activate
>  IN_DATA_SHEET_NM = ActiveSheet.Range("C2")
>  OT_Folder_NM = ActiveSheet.Range("C3")
>  If Right(OT_Folder_NM, 1) <> "\" Then
>    OT_Folder_NM = OT_Folder_NM & "\"
>  End If
>  OT_File_NM = ActiveSheet.Range("C4")
>
  Sheets("MAKE_DATA").Cells.ClearContents
  Sheets("WORK").Cells.ClearContents
    
  With Sheets("設定")
    IN_DATA_SHEET_NM = .Range("C2")
    OT_Folder_NM = .Range("C3")
    OT_File_NM = .Range("C4")
 
    If Right(OT_Folder_NM, 1) <> "\" Then
      OT_Folder_NM = OT_Folder_NM & "\"
    End If
  End With

「Select」「Activate」等の処理は重くなる…と聞いた気がします。
省略できるのであれば、できるだけ使わない方が良いかと重います。

ADO を呼び出しているのである程度時間が掛かってしまう事もありますが、
シート1枚をコピーして、CSV に Save している所も
もうちょっと簡潔にできると思われます。

※ 識者の方、よろしくお願いします。

【28415】Re:メモリ使用量
発言  ichinose  - 05/9/6(火) 7:59 -

引用なし
パスワード
   EXCEL_VBA中の下 さん:
ちくたくさん、小僧 さん:
おはようございます。

まず、気になったのは、

現在、正常動作しているブックを

1起動(開いた)した直後からマクロの「処理が重くなって」いるということなのか?

2起動後、何回かマクロを繰り返すうちに「処理が重くなって」いるということなのか?
(↑つまり、最初は、何も感じない程度の処理がなされている)


ということです。

1の場合は、マクロを含んだブックを再度新規ブックに作成して試してみてください。

2の場合は、私が気になったのは、

>Sheets("MAKE_DATA").Activate
>Sheets("MAKE_DATA").Copy

シートコピーではいくつかのバグが発見されています。
(直接原因に相当するサイトは探せませんでしたが・・・)
新規ブックを作成し、値の移行という操作で

Sheets("MAKE_DATA").Copy

の操作に相当する処理を行ってみてはどうでしょうか?

【28427】Re:メモリ使用量
発言  EXCEL_VBA中の下  - 05/9/6(火) 14:41 -

引用なし
パスワード
   ▼ichinose さん:
メモリ量を主要点で計測?してみました。
(マクロをブレイクポイントで止めながら)

1回目
 処理前              :82,268K
 OPEN_ADO前            :82,260K
 シートコピー前 (ADOクローズ後):86,312K
 終了               :87,112K

2回目
 処理前              :94,416K
 OPEN_ADO前            :94,480K
 シートコピー前 (ADOクローズ後):98,456K
 終了               :98,672K

でした。(1回目と2回目の間で、何度か未計測の実行あり)

どれほど参考になるか分かりませんが、
SQL実行後が一番増えてますね。
2回目SQL処理中で、一瞬98,888Kになりました。
(Nothingのセットで下がったのでしょうか?)
SQLで増えるのはしょうがないとは思いますが、
処理後に元に戻らないのでしょうか。(それに近い値まで)
増える一方では・・・。(^^;

【28447】Re:メモリ使用量 実験1
発言  ichinose  - 05/9/6(火) 20:28 -

引用なし
パスワード
   ▼EXCEL_VBA中の下 さん:
こんばんは。
私もやってみました。

実験 1

新規ブックのSheet1のセルA1から
    A   B    C    D
 1 大分類 中分類  小分類   金額
 2 服   紳士服  紳士服   500
 3 服   婦人服  婦人服   400
 4 服   子供服  子供服   300
 5 本   マンガ  男子向   100
 6 本   マンガ  女子向   150
 7 飲み物 ジュース オレンジ  110
 8 飲み物 ジュース アップル  115
 9 飲み物 ジュース パイン   120
10 飲み物 お茶系  麦茶    100
11 飲み物 お茶系  ウーロン茶 100

というEXCEL_VBA中の下 さんがご提示されたサンプルデータを
使用します。

Sheet2という名前のシートも用意してください


このブックの標準モジュールに

'===========================================================
Sub main()
  Dim flno As Long
  Dim idx As Long
  flno = FreeFile()
  Open ThisWorkbook.Path & "\memlog.csv" For Output As #flno
  Print #flno, "NO,使用メモリー,空きメモリー,トータル"
  For idx = 1 To 100
   Call test
   DoEvents
   With Application
     Print #flno, idx & "," & .MemoryUsed & "," & .MemoryFree & "," & .MemoryTotal
     End With
   Next idx
  Close #flno
End Sub
'========================================================================
Sub test()
  Dim rs As Object
  Dim mysql As String
  If open_ado_excel(ThisWorkbook.FullName) = 0 Then
    mysql = "select [大分類],[中分類],sum([金額]) from [sheet1$] group by [大分類],[中分類]"
    If get_exec_sql(mysql, rs) = 0 Then
     With Worksheets("sheet2")
      .Cells.ClearContents
      .Range("a2").CopyFromRecordset rs
      .Range("a1:c1").Value = Array("大分類", "中分類", "金額")
      End With
     End If
    Call rs_close(rs)
    End If
  Call close_ado
End Sub


別の標準モジュールに
'===============================================================
Public cn As Object
'======================================================
Function open_ado_excel(book_fullname As String) As Long
  On Error Resume Next
  link_opt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & book_fullname & ";" & _
       "Extended Properties=Excel 8.0;"
  Set cn = CreateObject("ADODB.Connection")
  cn.Open link_opt
  If Err.Number <> 0 Then
    MsgBox Err.Number & ":::" & Err.Description
    End If
  open_ado_excel = Err.Number
  On Error GoTo 0
End Function
'======================================================
Sub close_ado()
  On Error Resume Next
  cn.Close
  Set cn = Nothing
  On Error GoTo 0
End Sub
'======================================================
Function get_exec_sql(sql_str, rs As Object) As Long
  On Error Resume Next
  Set rs = cn.Execute(sql_str)
  If Err.Number <> 0 Then
    MsgBox Err.Number & ":::" & Err.Description
    End If
  get_exec_sql = Err.Number
  On Error GoTo 0
End Function
'======================================================
Sub rs_close(rs As Object)
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  On Error GoTo 0
End Sub

と細かいところをちょっと更新しました。

これを適当なブック名を付けて保存した後、
プロシジャーmainを実行してください。
ブックと同じフォルダにmemlog.csvというログファイルを
作成します。
mainの実行後、呼び出してください。
cvsファイルになっていますからExcelで読み込めるはずです。

ログファイルを見ると使用メモリーが増えています。

これでは、困りますよねえ!!
さあ、どうしよう・・・・。

【28448】Re:メモリ使用量 実験2
発言  ichinose  - 05/9/6(火) 20:49 -

引用なし
パスワード
   今度は、ブックをデータとマクロに分けてみました。

実験 2

>新規ブックのSheet1のセルA1から
>    A   B    C    D
> 1 大分類 中分類  小分類   金額
> 2 服   紳士服  紳士服   500
> 3 服   婦人服  婦人服   400
> 4 服   子供服  子供服   300
> 5 本   マンガ  男子向   100
> 6 本   マンガ  女子向   150
> 7 飲み物 ジュース オレンジ  110
> 8 飲み物 ジュース アップル  115
> 9 飲み物 ジュース パイン   120
>10 飲み物 お茶系  麦茶    100
>11 飲み物 お茶系  ウーロン茶 100
>
>というEXCEL_VBA中の下 さんがご提示されたサンプルデータを
>使用します。
>
今度は、上記データのみを名前を付けて保存します。
(ブック名は、excelsampwithado.xlsとでもしましょう)

保存後、このブックは閉じてください。


別の新規ブックを作成し、
このブックには、以下のマクロだけを登録します。

>
>
>このブックの標準モジュールに
>
>'===========================================================
>Sub main()
>  Dim flno As Long
>  Dim idx As Long
>  flno = FreeFile()
>  Open ThisWorkbook.Path & "\memlog.csv" For Output As #flno
>  Print #flno, "NO,使用メモリー,空きメモリー,トータル"
>  For idx = 1 To 100
>   Call test
>   DoEvents
>   With Application
>     Print #flno, idx & "," & .MemoryUsed & "," & .MemoryFree & "," & .MemoryTotal
>     End With
>   Next idx
>  Close #flno
>End Sub
>'========================================================================
>Sub test()
>  Dim rs As Object
>  Dim mysql As String
  If open_ado_excel(ThisWorkbook.Path & "\excelsampwithado.xls") = 0 Then
>    mysql = "select [大分類],[中分類],sum([金額]) from [sheet1$] group by [大分類],[中分類]"
>    If get_exec_sql(mysql, rs) = 0 Then
>     With Worksheets("sheet2")
>      .Cells.ClearContents
>      .Range("a2").CopyFromRecordset rs
>      .Range("a1:c1").Value = Array("大分類", "中分類", "金額")
>      End With
>     End If
>    Call rs_close(rs)
>    End If
>  Call close_ado
>End Sub
>
>
>別の標準モジュールに
>'===============================================================
>Public cn As Object
>'======================================================
>Function open_ado_excel(book_fullname As String) As Long
>  On Error Resume Next
>  link_opt = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>       "Data Source=" & book_fullname & ";" & _
>       "Extended Properties=Excel 8.0;"
>  Set cn = CreateObject("ADODB.Connection")
>  cn.Open link_opt
>  If Err.Number <> 0 Then
>    MsgBox Err.Number & ":::" & Err.Description
>    End If
>  open_ado_excel = Err.Number
>  On Error GoTo 0
>End Function
>'======================================================
>Sub close_ado()
>  On Error Resume Next
>  cn.Close
>  Set cn = Nothing
>  On Error GoTo 0
>End Sub
>'======================================================
>Function get_exec_sql(sql_str, rs As Object) As Long
>  On Error Resume Next
>  Set rs = cn.Execute(sql_str)
>  If Err.Number <> 0 Then
>    MsgBox Err.Number & ":::" & Err.Description
>    End If
>  get_exec_sql = Err.Number
>  On Error GoTo 0
>End Function
>'======================================================
>Sub rs_close(rs As Object)
>  On Error Resume Next
>  rs.Close
>  Set rs = Nothing
>  On Error GoTo 0
>End Sub


これを適当なブック名を付けてexcelsampwithado.xlsと同じフォルダに保存した後、
プロシジャーmainを実行してください。

>ブックと同じフォルダにmemlog.csvというログファイルを
>作成します。
>mainの実行後、呼び出してください。
>cvsファイルになっていますからExcelで読み込めるはずです。

ログファイルのメモリが増えていないことが確認できましたか?

どうやら、Excelで開いているブックに対してADOで
処理を行うとメモリーの増加という現象が見られます。
(上記のようにマクロとデータのブックを分けても
データブック「excelsampwithado.xls」を開いた状態で
mainを実行してもメモリーの増加は見られます)。

・マクロと対象データが入っているブックを分けること。

・ADOで処理している間は対象ブックを開かない

なんて事を注意して仕様を考えてみてはいかがですか?

【28451】Re:メモリ使用量 補足
発言  ichinose  - 05/9/7(水) 6:56 -

引用なし
パスワード
   おはようございます。

前投稿の 実験1、実験2は

Win2000+Excel2002(SP-3)

及び、

Win98&Excel2000(SP-3)

で行った結果です(二つとも同じ結果でした)。


今朝、

Win98&Excel2000(SP-1)でも
同じく実験1、実験2を行ったところ、

実験1の結果が上記二つと違い、
ログファイルのメモリが増えていないことを確認しました。


尚、ADOのバージョンは全て2.5でした。

ADOのバージョンやその他のExcelによっても
何か変化があるかもしれません。

皆さん、

それぞれの環境で確認してみて、よかったら、結果を
投稿して下さい。


以上、補足でした。

【28452】Re:メモリ使用量 補足2
発言  ichinose  - 05/9/7(水) 8:06 -

引用なし
パスワード
   本家サイトにそれらしいものがありましたが、

http://support.microsoft.com/default.aspx?scid=KB;JA;319998

私には、何を言っているのか半分ぐらいしか理解できませんでしたが・・・。
というか、途中で読むのを止めました(日本語になってないんだもん)。

一応、参考資料ということで・・・。

【28453】Re:メモリ使用量 補足2
回答  ちくたく  - 05/9/7(水) 8:47 -

引用なし
パスワード
   おはようございます。

特に、ADOを通したときに、メモリの以上使用が
見られるのですかね。

>http://support.microsoft.com/default.aspx?scid=KB;JA;319998
>私には、何を言っているのか半分ぐらいしか理解できませんでしたが・・・。
>というか、途中で読むのを止めました(日本語になってないんだもん)。

マイクロソフトの英和訳では、よくありますね、
不明な日本語、不明な日本語。。。
ディストリビュータの、能力の皆無さに、驚きます。

>一応、参考資料ということで・・・。
>・マクロと対象データが入っているブックを分けること。
>・ADOで処理している間は対象ブックを開かない

不思議な現象ですね。
まぁ、原因が明確で、再現性がとれるので、よかったです。
お疲れ様です。

【28488】Re:メモリ使用量 補足2
発言  よろずや  - 05/9/7(水) 22:33 -

引用なし
パスワード
   実験に当たって、オブジェクトのメモリサイズばかり気にしてますが、
何かの機能を呼び出せばその機能を実現するためのDLLが呼び出されます。
(既にメモリ上にあればそれを使います)
さらに、DLLがワークエリアとして使うメモリもあります。
(必要量の数倍の領域を順繰りに使ったり、使用済みの領域を返却することもあります)
それらの挙動は複雑ですので数回の実行では本当の事は分かりません。

【28499】Re:メモリ使用量 補足2
発言  ichinose  - 05/9/8(木) 8:32 -

引用なし
パスワード
   ▼よろずや さん:
おはようございます。久しぶりですね!!

>実験に当たって、オブジェクトのメモリサイズばかり気にしてますが、
>何かの機能を呼び出せばその機能を実現するためのDLLが呼び出されます。
>(既にメモリ上にあればそれを使います)
>さらに、DLLがワークエリアとして使うメモリもあります。
>(必要量の数倍の領域を順繰りに使ったり、使用済みの領域を返却することもあります)
>それらの挙動は複雑ですので数回の実行では本当の事は分かりません。
↑、これは、
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=28448;id=excel

の「実験2だけでは、ADOの繰り返し処理が確実に安全か否かは
わからない」

ということでしょうか?
それはそうだと思いますし、私も思い当たるふしもあります。

例えば、Workbook.Open()で
繰り返し、同一のブックを呼び出し、閉じるを行った場合、

Sub main()
  Dim flno As Long
  Dim idx As Long
  Dim rbk As Workbook
  flno = FreeFile()
  Open ThisWorkbook.Path & "\memlog.csv" For Output As #flno
  Print #flno, "NO,使用メモリー,空きメモリー,トータル"
  For idx = 1 To 5000
   Set rbk = Workbooks.Open(ThisWorkbook.Path & "\readbk.xls")
   rbk.Close False
   With Application
     Print #flno, idx & "," & .MemoryUsed & "," & .MemoryFree & "," & .MemoryTotal
     End With
   Next idx
  Close #flno
End Sub

これ実は、ログファイルの結果を見ると使用メモリーは増えていません。
が繰り返し処理の動作が目視でわかるくらい鈍くなり、
最後はメモリー不足というエラーを起こします。
(Win98 & Excel2000(SP-3)でしか試していませんが)
よって、よろずや さんがおっしゃっている

「挙動は複雑ですので数回の実行では本当の事は分かりません」

とおりだと思います。

(ちなみにこれ、ループ中にDoevents入れることで回避できると思っていましたが、
今朝はテストできませんでした。)


「実験2だけでは、ADOの繰り返し処理が確実に安全か否かは
わからない」
は更なる実験や経験が必要かもしれません。

でも、実験2でもメモリー管理に問題があるとしたら、
ExcelでADO(Excelブック)の使用に???が付いてしまいますが・・。


ただ、私が投稿した実験1と実験2とでは、
使用メモリの動向は明らかに違っています
(Excel2002 Sp-3 Excel2002sp-3において)。

EXCEL_VBA中の下さんが「処理が重くなって」
とおっしゃった大きな原因ではないかという仮説は成り立ちます。

後は、前述のように

>・マクロと対象データが入っているブックを分けること。

>・ADOで処理している間は対象ブックを開かない

等の仕様で
EXCEL_VBA中の下さんが「処理が重くなって」
という現象が回避できるかということだと思います。

そして実験2については、私ももう少し考えてもます。

ご指摘、ありがとうございます。

【28537】Re:メモリ使用量 補足2
発言  よろずや  - 05/9/8(木) 19:29 -

引用なし
パスワード
   >>実験に当たって、オブジェクトのメモリサイズばかり気にしてますが、
>>何かの機能を呼び出せばその機能を実現するためのDLLが呼び出されます。
>>(既にメモリ上にあればそれを使います)
>>さらに、DLLがワークエリアとして使うメモリもあります。
>>(必要量の数倍の領域を順繰りに使ったり、使用済みの領域を返却することもあります)
>>それらの挙動は複雑ですので数回の実行では本当の事は分かりません。

↑これは、↓こちらのサイトの批判だったりします。(^.^)

>Access に関してですが、Set xx = Nothing のメモリ解放について
>検証されているサイトがあります。
>
>http://www.tsware.jp/labo/labo_19.htm

【28541】Re:メモリ使用量 補足2
質問  小僧  - 05/9/8(木) 22:18 -

引用なし
パスワード
   ▼よろずや さん:
こんばんは。

>↑これは、↓こちらのサイトの批判だったりします。(^.^)
>
>>http://www.tsware.jp/labo/labo_19.htm

あちらのサイトを紹介した人間として言わせて頂きますね。

別にDLLを呼ぼうがワークエリアを使おうが知った事じゃありません。
どの命令がメモリを解放しているかという話ではなく、

Set XX = Nothing

という命令はメモリを解放する訳ではない、というための紹介だったのですが。


あちらの結論のところに

>DatabaseやRecordsetオブジェクトでは "Nothing"キーワード の使用は
>メモリ消費の観点からは無意味。

とありますが、よろずやさんの意見(実証された結果なのでしょうか)では

>>それらの挙動は複雑ですので

という事で場合によっては

Set XX = Nothing

でメモリが解放される事もあるというお話なのですか?

【28542】Re:メモリ使用量 補足2
発言  よろずや  - 05/9/8(木) 22:49 -

引用なし
パスワード
   >という事で場合によっては
>
>Set XX = Nothing
>
>でメモリが解放される事もあるというお話なのですか?

そんなのとイテマセン。

一回や二回の実行で結論を出すのはいかがなものか、と言ってるのです。
> リブート直後の1回目では、<1>、<2>、<3>と徐々にメモリが減っていくのが
> 分かります。DatabaseやRecordsetの変数にオブジェクトが代入されることに
>よってメモリが消費されていくことを示しています。
これは、オブジェクトじゃなくてDLLだと言ってます。

【28544】Re:メモリ使用量 補足2
質問  小僧  - 05/9/9(金) 0:12 -

引用なし
パスワード
   ▼よろずや さん:
こんばんは。

>そんなのとイテマセン。
>一回や二回の実行で結論を出すのはいかがなものか、と言ってるのです。

いや、思いっきり話がずれているのですが…。

では何回か実験をすれば

Set XX = Nothing

のタイミングでメモリが解放されるのか、という話なのですが…。

(何回か前に使った変数への参照が切れるのと
たまたまタイミングが合った、というのではなくですよ。)

>> リブート直後の1回目では、<1>、<2>、<3>と徐々にメモリが減っていくのが
>> 分かります。DatabaseやRecordsetの変数にオブジェクトが代入されることに
>>よってメモリが消費されていくことを示しています。
>は、オブジェクトじゃなくてDLLだと言ってます。

という話ではなく…。

というのもここの掲示板での話だけでなく、他の方(職場の同僚など)にも

「Set xx = Nothing で メモリを解放している訳ではない」

と言い切ってしまっております。

もしその事が事実でなければ、今まで教えた方に(出来る限りではありますが)
お詫びしなければ…と思っているのですが。

当方の検証でも(といっても30回前後だったでしょうか)
何回実験しようが、メモリの解放と Nothing は関係ないと思うのですが…。

【28545】Re:メモリ使用量 補足2
発言  ichinose  - 05/9/9(金) 1:36 -

引用なし
パスワード
   ▼小僧 さん、▼よろずや さん:
こんばんは。

http://www.tsware.jp/labo/labo_19.htm

のサイト、私も読ませていただきました。
このサイトに記述されていること

「オブジェクトは適用範囲外になると自動的に解放されている」

というのは本来なら、こうあるべきだと思いますけどね。

ただ、
sub test()
 dim aa as object
 set aa=createobject("excel.appliaction")



 set aa=nothing
end sub
の Set aa=nothing

をしなかったためのトラブルがあったという投稿を私は
何回か目にした事はあります。
自分自身では確たる経験はありません。


>
>では何回か実験をすれば
>
>Set XX = Nothing
>
>のタイミングでメモリが解放されるのか、という話なのですが…。
>
>
>というのもここの掲示板での話だけでなく、他の方(職場の同僚など)にも
>
>「Set xx = Nothing で メモリを解放している訳ではない」
>
>と言い切ってしまっております。

オブジェクトとは、データとプログラムをカプセル化したものですよね?

だとしたら、

>「Set xx = Nothing で メモリを解放している訳ではない」

これは、必ずしも正しくはないと思いますよ!!

例えば、
クラスモジュール(class1)で

'==================================================
Public a As Variant
'===================================================================
Private Sub Class_Initialize()
  a = Array(String(1024, "a"), String(1024, "a"), String(1024, "a"), String(1024, "a"))
End Sub
'===================================================================
Private Sub Class_Terminate()
  Erase a
End Sub


なんてコードを書いた場合、

dim cls as new class1


インスタンスの作成で Initializeイベントが発生し、
変数aには、array関数でデータが設定されます。

Set cls=nothing

でTerminateイベントが実行され、aはEmptyになります。

この場合

Set cls=nothing が参照の初期化のみだとは思えませんが、

いかがですか?

当然ですが、DBやRecordsetは中でどんなイベントが動くのかは

わかりませんけどね!!

【28555】Re:メモリ使用量 補足2
質問  小僧  - 05/9/9(金) 10:39 -

引用なし
パスワード
   ▼みなさま:
おはようございます。

時間を見つけて色々検証している最中ですが、どうしても

Set XX = Nothing

のタイミングで使用メモリが回復する事がないんですよね…。

>ただ、
>sub test()
> dim aa as object
> set aa=createobject("excel.appliaction")
>・
>・
>・
> set aa=nothing
>end sub
>の Set aa=nothing
>
>をしなかったためのトラブルがあったという投稿を私は
>何回か目にした事はあります。


Access等 でのこの手のオートメーションのトラブルは FAQ だと思うのですが、
大概は Nothing に原因があるのではなく、
Set した変数の扱い方に問題がある場合か、上位オブジェクトからの参照

(ExcelVBA を使われている方には当たり前だと思うのですが、
Application - Workbook - Sheet - Cell という使い方)

ができていない場合が多いのではないかと思います。

Sub test1()
On Error GoTo errEXE
Dim objWrd As Object
Dim objDoc As Object

  Set objWrd = CreateObject("Word.Application")
    'objWrd.Visible = True
  Set objDoc = objWrd.Documents.Open("C:\Test.doc")
    objDoc.Characters(1) = "Hello"
    objDoc.Characters(6) = "World"
    objDoc.Characters(999) = "Miss"
    objDoc.Close
    objWrd.Quit
errEXE:
  Set objDoc = Nothing
  Set objWrd = Nothing
End Sub

「C:\Test.doc」というワードファイルを新規で作って上のコードを実行すると
「Miss」の行でエラーになると思います。

On Error で飛ばされた先で「Nothing」があり objWrd は解放されるから大丈夫
かと思いきや、2回目に実行すると読み取り専用になっているかと思われます。

※ 'objWrd.Visible = True のコメントを外すと理由が解ると思います。

CreateObject をした際の後始末に Nothing をしないのがトラブルの原因なのではなく、
Set した変数の扱い方がまずいからトラブルになるのではないでしょうか。


>例えば、
>クラスモジュール(class1)で

こちらも色々やってみたのですが、Nothing する以前の問題で、
Class1 を呼び出した際に使用メモリが増えていますでしょうか?


当方は VB を扱った事もあまりなくこういった分野には全く詳しくないのですが、

メモリ使用量の数値を見ている限りでは、
オブジェクト(よろずやさんの解説によると DLL ?)を呼び出した変数に

Nothing がセットされてからある程度の時間(これが何とも曖昧なんですよね…)

が経つとメモリが回復しているように見えるのですが…。

(という事で Set XX = Nothing でメモリを解放しているわけではない、と言いたいのですが(笑))

【28570】Re:メモリ使用量 補足2
発言  よろずや  - 05/9/9(金) 12:59 -

引用なし
パスワード
   >メモリ使用量の数値を見ている限りでは、
>オブジェクト(よろずやさんの解説によると DLL ?)を呼び出した変数に
初めてその機能を使うときにDLLが呼ばれます。
これは、オブジェクトとは別勘定になります。
>Nothing がセットされてからある程度の時間(これが何とも曖昧なんですよね…)
>
>が経つとメモリが回復しているように見えるのですが…。
>
>(という事で Set XX = Nothing でメモリを解放しているわけではない、と言いたいのですが(笑))
はい、Set XX = Nothing でメモリを解放しているわけではありません。
でも、それをやらないとメモリは開放されません。
それに、メモリを開放したと言っても未使用としてリザーブされてますので、
OSにいつ返却するのかは、実装依存です。

【28597】Re:メモリ使用量 補足2
発言  ichinose  - 05/9/9(金) 17:20 -

引用なし
パスワード
   みなさん、こんにちは。

>
>時間を見つけて色々検証している最中ですが、どうしても
>
>Set XX = Nothing
>
>のタイミングで使用メモリが回復する事がないんですよね…。
>
>当方は VB を扱った事もあまりなくこういった分野には全く詳しくないのですが、

私もExcelのメモリー管理がわかっているわけではありません。
いえ、Windowsのそれについてもです・・・。

私の主張は
あくまでも

>オブジェクトとは、データとプログラムをカプセル化したものですよね?

>だとしたら、

>>「Set xx = Nothing で メモリを解放している訳ではない」

>これは、必ずしも正しくはないと思いますよ!!

これなんです。
つまり、オブジェクトの作り方(クラスの中身)によっては

Set aa=Nothing

で発生するイベントに何らかの処理が施されていれば
メモリが解放されることもありえるということです。

私も色々とクラスに変数を入れて試しましたが、

例えば、こんなクラス

'=================================
Public myarray As Variant
'=================================
Sub set_data()
  Dim temp() As Variant
  For idx = 1 To 10000
   ReDim Preserve temp(1 To idx)
   temp(idx) = String(1000, "a")
   Next
  myarray = temp()
  Erase temp()
End Sub
'==================================
Private Sub Class_Terminate()
  On Error Resume Next
  Debug.Print "term message1 " & TypeName(myarray)
End Sub

但し、Application.MemoryUsedに反映されません。
これは、変数のありかとApplication.MemoryUsedで計測されるメモリとで
に何か違いがあるのでしょうかねえ??
わかりません。


でクラスの内容を変えました。


以下の仕様で試してみて下さい。


クラスモジュール(Class1)では、インスタンスの作成で
予め作成されているブック(clssbk.xls)を呼び出す事を考えます。
適当にデータを入れたブックをclssbk.xlsとして保存しておいてください。


クラスモジュール Class1は、

'==================================================
Public readbk As Workbook
'=================================================
Private Sub Class_Initialize()
  Set readbk = Workbooks.Open(ThisWorkbook.Path & "\clssbk.xls")
End Sub
'=============================================================
Private Sub Class_Terminate()
  On Error Resume Next
  readbk.Close False
  Set readbk = Nothing
End Sub


標準モジュールに
'===============================================================
Dim cls As Class1
'==============================================================
Sub test()
  With Application
   Debug.Print "chk1--" & .MemoryUsed & "---" & .MemoryFree & "---" & .MemoryTotal
   End With
  Set cls = New Class1
  DoEvents
  With Application
   Debug.Print "chk2--" & .MemoryUsed & "---" & .MemoryFree & "---" & .MemoryTotal
   End With
  Set cls = Nothing
  DoEvents
  With Application
   Debug.Print "chk3--" & .MemoryUsed & "---" & .MemoryFree & "---" & .MemoryTotal
   End With
End Sub

として、「clssbk.xls」と同じ場所に保存して下さい。

保存後、プロシジャーtestを実行してみて下さい。
今度は、使用メモリに違いあると思います。

つまり、

Set Cls=Nothing

によるメモリ解放が認められます。

というように
オブジェクトの中身によって、

Set xx = Nothing の影響は未知数である


というのが私の青年の主張でした・・。

【28598】Re:メモリ使用量 補足2
お礼  小僧  - 05/9/9(金) 17:47 -

引用なし
パスワード
   ▼ichinose さん、みなさま:
こんにちは。

おぉ、すごい、使用メモリが減ってる…

と思いきやブックがもう1個上がってるぞ(笑)…。

自分としては、

Private Sub Class_Terminate()
  On Error Resume Next
  With Application
    Debug.Print "chk2.5-" & .MemoryUsed
  End With
  readbk.Close False
  Set readbk = Nothing
End Sub

これで Chk2 と Chk2.5 の値が違うのであれば一番納得いくんですけどねぇ。


とはいえ、ichinose さんのサンプルコードから
自分なりに考えさせられる事もありますし、

(Set XX = Nothing からイベントを発生させるという考え方)

良い勉強になりました。ありがとうございます。

9227 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free