質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.58%

Excel vba でメモリが不足しています。っと表示されて処理が完了できない。

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 5
  • VIEW 16K+

kamikazelight

score 172

最終 --2018.11.16 13:40 追記 --

回避策...
手動、マクロ 問わずに 大量のセルに対し操作の必要があるときは
「カメラ」機能や リンクされた図は使用しない
又は、
一度閉じると使用メモリが元に戻るので
目視、又はpowershell 等を用いて使用メモリが一定量を超えたら
Excelプロセスを再起動してから作業を続行する...

「カメラ」機能や リンクされた図は使用しない のが王道でしょうか
他にも不具合がいっぱいあるようですし
便利な機能なのでとても残念です.....

現在の状況 --2018.11.16 11:35 追記 --

sazi 様 からのコメントで分かったのですが
カメラ機能でなくても
貼り付けオプションの「リンクされた図」でも
同様の症状が出ることが分かりました。

現在の状況 --2018.11.16 11:20 追記 --

検証を重ねた結果
メモリの上昇量には一応上限があることが分かりました。
カメラの範囲の他
入力されている数式の数 計算量?
等に影響されることが分かりました。
Win10 Excel 2013 32bit でも確認しました。

現在の状況 --2018.11.16 10:40 追記 --

原因の特定が出来ました。
Excelの 「カメラ」機能を使用して作成したオブジェクトが存在すると
本文記載の問題が発生するようです。

Win10 Excel 2016 64bit で
新規ブックのみを立ち上げ確認致しました。

そのオブジェクトは

  1. 適当なセル10個ほど選択
  2. 「カメラ」をクリック
  3. 適当なセルクリック
    で配置しました。

出来れば引き続き「カメラ」機能は使いたいのですが
回避方法はないのでしょうか?
また、症状の再現が出来た方、出来なかった方
環境を教えて頂きたいです。
お願い致します。

「カメラ」機能はクイックアクセスツールバーに追加して利用しました。

イメージ説明
イメージ説明

現在の状況 --2018.11.16 09:47 追記 --

いろいろ検証をした結果
コードだけに問題があるわけではなく
Excelファイルが異常をきたしている のか 
特定条件下のでの Excelの挙動が問題になっている可能性が高いことが分かりました。

発生している 症状は

特定のブックを開いている状態で同じプロセス内の任意のブックの任意の
セルの設定(塗りつぶし、ユーザー定義書式 等)を書き換えるだけで
その都度 使用メモリが増加する。

手動で適当なブックの適当なセルを選択してセルの塗りつぶしボタンを連打するだけでも同様に発生

特定のブックは開かず 新規のブックのみ開いて手動でセルの塗りつぶしボタンを連打しても
使用メモリは 増加しますが
おそらく 戻るボタンの 履歴の関係だと思うのですが そちらは一定以上は上がりません。

特定のブックを開いていると下記のコードを実行しただけでメモリ不足のエラーが表示されます。

Sub test()

    Dim i As Integer
    For i = 1 To 10000
        Selection.NumberFormat = Selection.NumberFormat
    Next i
End Sub


他気になる点は
上記コード実行時はExcelのCPU 使用率が10%強 くらいまでしか上がらず
(未保存新規ブックのみ起動の場合は30%くらいまで使用率が上がり、すぐ終わる)
実行速度が極端に低下することです。

現在 状況を再現できないか確認しています。

 前提・実現したいこと

発端は
Excel ブック(ファイル)に問題があるのか頻繁にデータが壊れて
修復しますか?と表示される。
修復を選択すると開けるが セル幅 が全て同じ幅になってしまったりして まともに修復できていない。
そんな状態を何とかしたい...
過去に同様の現象が起きたときはブックを新規作成(シートをコピーしたらだめだった)して作り直したら再発しなくなったので、
そうしようと思ったのですが量が多い...

そのためマクロで書式設定等を移してしまおうと思ったのですが
メモリが不足していますと表示されて途中までしか実行できません。

メモリが不足と表示されてしまう原因が全く分からず
今回はいつこの問題が解決できるかもわからないので諦めて手動で作り直そうかと思うのですが、
今後の対策のためにも原因が知りたいです。

極力コードを削って確認してみたのですが
Get_Set_CellInfo 内の 塗りつぶし や 削ったため下記のコードにはないですが
罫線の設定、ユーザー定義書式の設定などでも同様にメモリ不足と表示されます。

タスクマネージャで見てみたのですが パソコン全体のメモリは余裕があるようでした...

 発生している問題・エラーメッセージ

![イメージ説明

 該当のソースコード

Book1の全ての使用済みセルに対しての処理
処理は結合範囲ごと(セルをひとつずつ確認して結合セル範囲の左上だった時のみ)

Sub SHC()

    Const BaseNm As String = "Book1.xlsx"
    Const TargetNm As String = "Book2.xlsx"

    Dim Base As Workbook
    Set Base = Workbooks(BaseNm)

    Dim Target As Workbook
    Set Target = Workbooks(TargetNm)

    Dim BaseCt As Integer
    BaseCt = Base.Sheets.Count

    Dim i As Integer


    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    Dim Result As Variant

    For i = 1 To BaseCt
    Debug.Print i
        Get_Set_CellJoin Base.Sheets(i), Target.Sheets(i)
    Next i
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic

End Sub

Sub Get_Set_CellInfo(BRng As Range, TRng As Range)

    If BRng.MergeCells Then
        TRng.Merge
    End If

' 塗りつぶし
    TRng.Interior.Pattern = BRng.Interior.Pattern
    If Not BRng.Interior.Pattern = xlNone Then
        TRng.Interior.Color = BRng.Interior.Color
    End If

End Sub


Sub Get_Set_CellJoin(BSh As Worksheet, SSh As Worksheet)
'セルの書式

    Dim Rng As Range
    Set Rng = BSh.UsedRange

    Dim Row As Long
    Row = Rng.Rows.Count

    Dim Col As Long
    Col = Rng.Columns.Count

    Dim R As Long
    Dim C As Long

    Dim TCell As Variant

    For R = 1 To Row
        For C = 1 To Col

            If Rng(R, C).Address(False, False) = Rng(R, C).MergeArea(1, 1).Address(False, False) Then
                Debug.Print Rng(R, C).MergeArea.Address(False, False)
                Get_Set_CellInfo Rng(R, C).MergeArea, SSh.Range(Rng(R, C).MergeArea.Address(False, False))
            End If

        Next C
    Next R

End Sub

 試したこと

For each で回しているのが悪いのかと思いfor に書き直してみた
途中でブックの保存を挟んでみた
Set を使っているものに対しては各プロシージャの最後で set 変数 = nothing をしてみた
パソコンを再起動してみた
ステップインで一つずつステートメントを実行してみた
罫線等の設定でも同様になる

--追記--
mdj 様の「1シート処理するように書き換えてみて、検証するのはどうでしょうか。」の言葉で
目的のブック以外に同様の操作をした場合に同じ現象が起きるかどうかの検証をしていないことに気づいたので、
容量が多い別のファイル(数式は殆ど入っていない)で 検証をした結果 そちらでは同様の問題は発生しませんでした。

--追記--
プログラムエラーとは別に 新規作成から作り直していたExcelブックの件で、
7時間くらいかけてなんとか 元のブックと見分けがつかないくらいまで作ったのですが
2018.11.15 現在 壊れているのを確認しました....

仮定していた「長年(拡張子が .xls だったころから)使ってきたので
ブックのどこかがおかしくなっていて壊れやすくなっている」という可能性が低くなりました。

「代わりに特定条件下で壊れやすくなっている」可能性が 高くなったので
プログラムエラーの件と合わせて再現可能か確認してみます。

他のブックとの違いといえば 他のブックへの参照 と 他のブックからの参照 があり
尚且つ そのデータを使ったグラフがある事くらいだと思うのですが...

--追記--
見かけそっくりに作り直した方のデータ(一度壊れて再び修復中のためグラフがない あと他ブックへのリンクが切れているかも...)に対しては
使用メモリが増え続ける現象は起きませんでした。

--追記--
メモリがパンクする前に中断した場合 そのExcelプロセスを閉じるまで(ブックを閉じても)メモリはそのままでした。
開きなおすと 増加してしまった使用メモリは元に戻っていました。

問題のブックのセル設定の読み込みのみ(variant 変数への代入)のみ行った場合は使用メモリの増加は発生しませんでした。

問題のブックを開いた状態で 同じプロセス上の関係のないブックのセルの設定を書き換えて見たら 使用メモリの増加が発生しました。

どうやら 問題のブックを開いている状態で同じプロセス内のブックのセルの設定を書き換えるだけで
使用メモリが増加してしまうようです。

--追記-- 
問題のブックを開いている場合
手動で適当なブックの適当なセルを選択してセルの塗りつぶしボタンを連打するだけでも
使用メモリが増加することが 確認できました....

問題のブックは開かず 新規のブックのみ開いて同様の内容を行っても
使用メモリは 増加しますが
一定以上は上がりません。

そちらはおそらく履歴(戻るボタンで戻るための分)で増えただけだと思われます。

 補足情報(FW/ツールのバージョンなど)

Win10
Excel 2016 64bit

Book1の情報
拡張子 xlsx
ファイルサイズ 112 KB
各シートのUsedRange
シートインデックス = 1 : 行数 = 57 : 列数 = 54
シートインデックス = 2 : 行数 = 36 : 列数 = 73
シートインデックス = 3 : 行数 = 34 : 列数 = 73
シートインデックス = 4 : 行数 = 34 : 列数 = 73
シートインデックス = 5 : 行数 = 57 : 列数 = 11
シートインデックス = 6 : 行数 = 40 : 列数 = 22
シートインデックス = 7 : 行数 = 31 : 列数 = 15
シートインデックス = 8 : 行数 = 20 : 列数 = 18

グラフ1(折れ線グラフ)
数式多数 使用 (他のブックへの参照と他のブックからの参照多々あり)

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

+1

他人の書いたコードは読みにくいので、自分流に直してしまいました^^;
やりたいことはこういうことかな?
動作確認はしてません。意を汲み取っていただけると幸いです。

Sub SHC()
    Dim wbOld As Workbook
    Dim wbNew As Workbook
    Dim ws As Worksheet
    Dim rngTarget As Range

    Set wbOld = Workbooks("Book2")
    Set wbNew = Workbooks("Book1")

    For Each ws In wbOld.Worksheets
        Set rngTarget = Get_CellJoin(ws, wbNew.Sheets(ws.Index))
        rngTarget.Interior.Color = ws.Range(rngTarget.Areas(1).Address).Interior.Color
    Next
End Sub

'結合セルの取得
Function Get_CellJoin(wsFrom As Worksheet, wsTo As Worksheet) As Range
    Dim c As Range
    Dim rng As Range

    For Each c In wsFrom.UsedRange
        If c.MergeCells Then
            If c.Address = c(1).Address Then
                wsTo.Range(c.MergeArea.Address).Merge
                If rng Is Nothing Then
                    Set rng = c
                Else
                    Set rng = Union(rng, c)
                End If
            End If
        End If
    Next
    Set Get_CellJoin = rng
End Function

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/16 09:15

    回答ありがとうございます。

    set rng = c と set rng = union(rng,c) の
    "c" を "wsTo.Range(c.MergeArea.Address)" に変更して、
    結合セル以外も 色を設定するように変更しても
    塗りつぶしの色が複数あると 対応出来ないようです。
    やろうとしていたのは コピーを利用せずに 見た目を そろえることです。
    記載のコードはその一部です。

    しかし、現在コードは問題になっていません。
    私も最初はコードにだけに致命的な問題があるものだと思っていたのですが
    検証を進めていくうちに Excelファイルが異常をきたしている のか 特定条件下のでの Excelの挙動が問題になっている可能性が高いことが分かりました。

    キャンセル

  • 2018/11/16 10:54

    原因の特定が出来ました。
    質問文に追記しました。
    再現されますでしょうか?

    キャンセル

checkベストアンサー

0

重くて開けなくなる事象に遭遇したのは、「名前の定義が多すぎる」、「数式が多すぎる」、「オブジェクト(図形など)が多すぎる」というのがありました。
名前の定義はコピーすると増えてしまいがちで、一番多かったですね。
メニュー→数式→名前の管理
で確認できますので、不要なものがあれば削除すると良いかと思います。

その他
Office アプリケーションを修復する
破損したブックを修復する

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/16 13:14

    自分はリンク図で、処理速度の大幅ダウンになったことがありますね。
    1つでも、です。
    リンクを一旦切り(図の数式をクリアだったかな?)、
    後で再設定する事で対応しましたが、
    メモリ不足等も引き起こすとは。。。

    キャンセル

  • 2018/11/16 13:15

    数式を手動にしても関係なかったので、
    常にバックグラウンドでリンク処理が動いてるのだと思います。

    キャンセル

  • 2018/11/16 13:24 編集

    そうですね。
    たった一つのオブジェクトが
    Excelプロセス全体に影響を与える....
    なんて 驚きです。
    しかも、すぐに症状(エラー)は出ずに潜伏し続け、大量のセル処理が発生したときのみ 発症する....
    怖ぃです。

    キャンセル

0

全然関係ないかもしれない、なかったらごめんなさい……

「ファイルが壊れたのか開くのに超時間がかかり、超待ってやっと開いてもなんか表示がおかしくなった」
と相談されてお預かりしたファイルが
コピペを繰り返したのが原因で、条件付き書式が数千個になってしまっていたことがありました。
VBEから条件付き書式を全クリアしたら動くようになりました。
ActiveSheet.Range.FormatConditions.Delete()
だったかしら……。
差し支えなければなんかそんな感じで、書式とか罫線とか、VBEから全部削除してみたら状況が変わるかもしれません。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/14 16:14

    重くなっていってダメになるファイルの対策として私がよくやっているのは
    ファイルを作ったときに、書式とか名前とか結合とかを一気に設定するようなモジュールを書いておきます。
    で、重くなってきたら一旦書式とか名前とか全部消してしまって
    (シートには値と数式だけ残る)
    それから最初に作っておいたモジュールで書式とかを全部設定し直す、というものです。

    いまもうできてるファイルなのでアレですけど
    値と数式以外は一旦全部諦めて消しちゃって
    改めて設定し直してはいかがですか。
    肥っちゃって具合の悪くなったファイルは、何が悪いとか触りまわるより、潔くやり直すほうが手間がかからないように私は思います。

    キャンセル

  • 2018/11/14 16:25

    sumire_cl 様
    前提・実現したいこと に書いてありますが
    作り直しは現在進行中です

    キャンセル

  • 2018/11/14 16:32 編集

    mdj 様
    1シートではないですが ステップインでの実行は確認しました。
    結果はじわじわ 使用メモリが増えていき ある程度のところで エラーでます。
    「1シート処理するように書き換えてみて、検証するのはどうでしょうか。」の言葉で
     気づいたのですが
    目的のブック以外に同様の操作をした場合に同じ現象が起きるかどうかの検証をしていませんでした。
    容量が多い別のファイル(数式は殆ど入っていない)で 検証をした結果
    同様の問題は発生しませんでした。
    そのファイルが特別おかしいのか、なにか条件がそろわないとこの問題は発生しないようです...
    再現が可能かどうか検証してみます。

    キャンセル

0

えっと、最初に、
メモリー不足というのは、パソコンの限界ではなくて、
エクセルが使えるメモリーを使いきったという事だと思います。
エクセル自体が使えるメモリーの量が制限されているようです。

あと、ファイルの拡張子はいま、なにになっているのでしょうか?

長く運用されていると、どんどん余分なものが増えてしまいますが、
新規ブックに値を転記して、セルの書式を設定されているのですよね?

その時に、個々のセルをループしながら、個々のセルを塗潰したりしては、いけません。
1つのセルを塗潰しても、1列全体を選択して塗潰しても、
セルの書式設定の数としては1回で数えるようです。
なので、まとめて出来るセルの書式は一括でセルの書式設定をしましょう。

2010なら100万くらいは大丈夫なはずですが、
10列×10万行以上を個々に設定したら多分制限を越えます。
旧バージョンのファイル形式だと、5000とか10000くらいかと思います。
ご注意を。
詳しくは検索したら見つかると思いますけど???

ホントはどういう風にしたいのかを書いてもらえますか?
なにか代替案を思いつくかも知れません。
コードを動かして見る時間が無いので、
日本語で「こうしたい!」というのも書いていただけるとありがたいかもです。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/11/15 19:24

    回避策としては、巡回しながら随時設定するのではなく、セル範囲を変数に記録しておき、後で一括で設定するようにしたらいいと思います。
    提示のマクロが何をしたくて書いているのか解りませんが(読んでませんが)、その説明をしていただくと、(シート上のセルの状況も合わせて)なにか実験して回避できる方法を探れるとは思うのですが。

    キャンセル

  • 2018/11/15 20:22 編集

    削除

    キャンセル

  • 2018/11/16 10:49

    原因の特定が出来ました。
    質問文に追記しました。
    再現されますでしょうか?

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.58%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る