[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『EndSubでメモリ不足になる』（傘）
マクロの終了時にメモリ不足でエクセルが落ちるので困っています。
問題が起きているマクロは
Sub ※1 別のブックを開いて1×20セルのデータを書き込んで閉じる ※2 シートの1つをMoveメソッドで新規ブックに分けて保存 ※3 ユーザーフォームを表示して終了するかしないか選択 End Sub
というのが大まかな流れです。
これがEndSubまで行くとメモリ不足のエラーが出て強制終了します。(体感8割くらい)
※3の後にブレークポイントを入れてステップ実行したところ
EndSubの直前までは問題なく動作しますがEndSubが実行されるとエラーになります。
ブックを閉じる操作が処理に追いつかないことがあるのでClose後にDoEventsを入れると良いという記事を読んだので
各※の間に入れてみたのですがだめでした。
メモリ不足の対策もしくは原因の心当たりが何かあれば教えてください。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
＞γ さん
すみません、やはり実コードがあったほうがいいですよね。
申し訳ありませんが会社で作成しているファイルで今は手元にないので
週明けにあらためて追記します。
（傘） 2022/03/11(金) 20:55
Sub 確認シート_申請確定()
If ActiveSheet.Name <> "確認" Then Beep: Exit Sub Dim anlNum As Long
Application.EnableEvents = False Application.DisplayAlerts = False Application.ScreenUpdating = False
'※1 Call データベース書き込み
anlNum = 9999 '本来は※1内で取得
Worksheets("申請シート").Visible = True
Worksheets("申請シート").Select
Call 個別ブック作成(anlNum)
Application.EnableEvevts = True Application.DisplayAlerts = True Application.ScreenUpdating = True
End Sub
Sub 個別ブック作成(ByVal anlNum As Long)
Dim fName As String, saveName As String Dim shp As Shape
With ThisWorkbook.Worksheets("確認")
.Unprotect
.EnableSelection = xlNoRestrictions
.Range("B4") = anlNum
For Each shp In .Shapes
shp.Delete
Next shp
.Range("O4:T7").Locked = False
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=False
End With
fName = Format(DateAdd("m", -3, Now), "yy") & "-" & Format(anlNum, "0000")
saveName = ThisWorkbook.Path & "\データベース\分析票\" & fName & ".xlsm"
ChDir ThisWorkbook.Path
Worksheets("確認").Name = fName
Worksheets(fName).Move
ActiveWorkbook.SaveAs fileName:=saveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Workbooks(fName).Close DoEvents
End Sub （傘） 2022/03/14(月) 09:43
＞そんなに重いファイルではないと思うんですが…
自分でそう思っているだけですよね。
極端にに減らしてみてください。
エクセルのシートコピー、移動はかなりヘボいです。
（perhaps） 2022/03/14(月) 10:17
デバッグ方法のアドバイスを。
エラーの原因となる箇所を絞り込むため、 それぞれ処理のブロックをコメントアウトして 実行していく。
例えば、Withで囲っているところ等
結果不再現であれば、複合的なものでしょう。
もし、やられていたら、その結果も欲しいですね。
（tkit） 2022/03/14(月) 10:26
タスクマネージャーでエクセルのメモリの使用量を見てみましたが
大丈夫なときは130MBほど使用してても問題なく動いているのに
ダメなときは100MB程度でエラーになるので何も参考になりませんでした。
このファイルは自分個人が使うために作っているわけではないため
エクセルが落ちるようなエラーが起きるのは非常に困るので
今思い付く解決策としては
・あらかじめ確認シートをMoveメソッドで分離しておいて、確認シートで実行するのは保存して閉じるだけのマクロに変える
・確認シート分離の代わりに別ブックを開いて確認シートとして使用し、それを名前をつけて保存する
くらいなのですが、他に何か方法はあるでしょうか？
（傘） 2022/03/14(月) 11:53
また、移動するシートオブジェクトにマクロの記述がある場合、
思わぬ動作となることも考えられますし、セルに記述している数式や、
他ブックの参照など、いろいろ考えられますね。
１つづつ潰していくことがデバッグの定石です。
（tkit） 2022/03/14(月) 12:01
■１
↓最初のほう要らないような気がします。
.EnableSelection = xlNoRestrictions .EnableSelection = xlUnlockedCells
■２
↓何の目的があるのですか？
ChDir ThisWorkbook.Path
■３
↓マクロ付きブックじゃないとダメなんですか？
FileFormat:=xlOpenXMLWorkbookMacroEnabled
■４
↓"選択"したい理由は何ですか？
Worksheets("申請シート").Visible = True
Worksheets("申請シート").Select
■５
ひな形的なものを考えていらっしゃるなら、MoveよりCopyのほうが安心ではないでしょうか？
Worksheets(fName).Move
さらに↓はあらかじめひな形に設定しておけば済む話だったりしませんか？
.Range("O4:T7").Locked = False
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=False
■６
ということを踏まえると↓のようでもいいのではないかとおもいます。
興味があれば【ステップ実行】して研究の上、使えそうな部分をご自身のコードに組み込んでみてください。
Sub 研究用()
If ActiveSheet.Name <> "確認" Then
Beep
Exit Sub
End If
Dim anlNum As Long
Stop 'ブレークポイントの代わり
Application.EnableEvents = False
'Call データベース書き込み
anlNum = 9999 '本来は↑で取得
ThisWorkbook.Worksheets("確認").Copy
With Workbooks(Workbooks.Count)
.Worksheets(1).Unprotect 'いらないかも
.Worksheets(1).Range("O4:T7").Locked = False 'いらないかも
.Worksheets(1).EnableSelection = xlUnlockedCells 'いらないかも
.Worksheets(1).Protect DrawingObjects:=False 'いらないかも
.Worksheets(1).Shapes.SelectAll
Selection.Delete
.Worksheets(1).Name = Format(DateAdd("m", -3, Now), "yy") & "-" & Format(anlNum, "0000")
.SaveAs Filename:=ThisWorkbook.Path & "\データベース\分析票\" & .Worksheets(1).Name
.Close
End With
Application.EnableEvents = True
End Sub
（もこな２ ） 2022/03/14(月) 12:05
ここでいう「メモリ不足」って、 別に「ExcelがRAMを使い切っちゃってる」って意味ではないですからね。 タスクマネージャーと睨めっこしても何も得られないと思いますよ。
ニュアンス的には「一気に動かそうとする部品の数が多すぎます」みたいな感じで、 例えば「スパークラインてんこもり」のシート上でオートフィルタで絞り込みした時とか マクロや物理メモリとは関係なく発生する場合もあります。
エラーが出るだけまだマシな方で、以前、 条件付き書式を設定した(複雑な)セル範囲をClearContentsしたら そこでマクロが終わる(エラーではなく終了)なんて現象に遭遇した事もありました。 (↑これも確かシートの保護が絡んでたと記憶してます)
構成部品が多くなればなるほど、よく分からない動きってのは出てくる様ですね。
一度発生するとステータスバーの情報等、色々と動作がアテにならなくなるので 私の場合は、素直にExcelを再起動する様にしてます。
上記の例だと、 「スパークラインてんこもり」の時点で不可避なので、 「フィルタ使う時は覚悟する事!」って運用で誤魔化してますし、 ClearContentsの方は .Value = Empty で処理すれば終了する現象は発生しませんでした。
「これ」という定番の対策は無いと思った方が良いですよ。
>Moveメソッドだけにしてもエラー
と言うので思い付くのは、
非表示の名前定義が山ほど潜んでたのが直接の原因で、
その原因は過去に一時的に存在した外部リンク・グラフ・描画オブジェクトの無効な名前定義が
シートのコピー等により増殖していって、とうとう許容量に達した
・・・とか、過去から原因を引きずってる可能性もあります。
この手の問題はもう、いろいろと試してみるしかないと思いますね。
（白茶） 2022/03/14(月) 14:49
そもそも"確認"シートは"申請シート"に入力した内容を、ひな形の非表示シート"元シート"をマクロでコピー・転記して作成するのですが
"確認"シート作成後に何故かCPUの使用率が60％くらいまで上がったままになるのを確認しました。
アクティブシートを"申請シート"に変えたらCPUの使用率は下がったので
非表示の"元シート"コピー→コピーしたシートを表示して名前を"確認"に変更→"申請シート"非表示→データ転記 という形だったのを
"元シート"コピー→名前変更して転記(アクティブシートは"申請シート"のまま)→転機が終わってから"確認"シートを表示→"申請シート"非表示 としたらCPU使用率が上がらなくなりました。
まだ解決したと断言はできませんが、"確認"シート作成の流れを上の形にしてからはまだメモリ不足のエラーは出ていないので
もしかするとこのCPU使用率の上昇が負荷になってエラーを引き起こしてたのかもしれません。
（傘） 2022/03/14(月) 15:03
白茶さん
＞別に「Excelが〜
PCのメモリ容量＝エクセルが使えるメモリ量 ではないことは知っていましたが
エラーになるときとならないときで使用量に傾向があったりしないかを期待して確認していました。
結局メモリ使用量は何も関係なく、代わりにCPU使用率の異常に気づけはしたので結果オーライではあったのですが。
（傘） 2022/03/14(月) 16:19
名前の定義が異常に多い。
セルのスタイルが異常に多い。
セルの書式設定を個々のセルで設定している。
シート上の図形を頻繁に書いたり消したりしている。
ファイルが壊れている。
パソコンそのもののエクセルを動かせるメモリがそもそも足りない。
長く使っているファイルだと、不要に書いたり消したりを繰り返していると、
ファイルが壊れることがあるような気がします。
新規ファイルにセルの値とマクロコードだけをコピペして、
新しく作り直すと、エラーにならないような気がします。
めんどくさかったら、新規ブックにシートまるごとコピーして、
新しく名前を付けて保存で上手く行くかもです。
（まっつわん） 2022/03/14(月) 17:02
続報という訳でもありませんが、エラーが発生しなくなったマクロにセルのロックをするコードを追加したら
またメモリ不足のエラーが出るようになりました。
これについてはセルロック以外で対応するつもりなので問題はないのですが
白茶さんの話にもあるように、エクセルにとってセルのロックはかなりリソースを食う操作なのかもしれませんね。
既存のコードのセルロックも別のやり方を検討してみます。
動作確認のため何十回もシートをコピーして消してを繰り返しているので
新規ファイルへの引っ越しもやってみようと思います。
（傘） 2022/03/15(火) 09:27
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.