Excel VBA 実行中にリソース(？)を解放し

どうやら使用可能領域を越えた旨のエラーに悩まされました。
「選択範囲が大きすぎます」
です。

シートにいっぱいもの(関数式)を詰め込みすぎなのか
VBAが走る際のごみが多いのか
何が原因かはわかりません。
(もう調べられません)

次の開発に向けて
同様のことが起きたときに考慮すべき対処をご教示ください。

No.3 ベストアンサー 回答者： DreamyCat 回答日時： 2013/03/31 09:08

　単なるデータだけなら

１回にコピーするセル数自体は20000ほどづつなので
非力なPCでも何ら問題ない程度です。
　しかし、処理回数が増えていくとエラーが起こることから考えると
関係があるかどうかわかりませんが
Application.CutCopyMode = False
が有効ではないかと思います。
　その他でも、使っているかなと思いますがこのあたりも検討してみてください。
Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.CalculateBeforeSave = False


「選択範囲が大きすぎます」と　「VBA」あるいは「エクセル」で
検索してみると同じメッセージが出るトラブルがいろいろあるようです。
　

この回答へのお礼

有り難うございます
学ばせて頂きました

次の機会にも、どうぞ宜しくお願い致します。

お礼日時：2013/04/01 15:17

No.6 回答者： WindFaller 回答日時： 2013/04/02 15:01

一応、#1のお礼で書かれていたものを元に、こちらのサンプルを提示させていただきます。

返事のご様子では、すぐに、どこが注意点なのか、お分かりにならないと思いますが、こちらのコードを見ないでも、ご自分のコードをステップ・モードで調べてみれば、自ずと分かるはずです。それで、今回の問題が解消するかは自信はありませんが、私のコードを見る気があるのでしたら、どう違うか調べてみてください。別の書き方をしてみましたが、もちろんサブルーチンでも構いません。

>当然この質問を元に　本ちゃんのコードが書き換わることは、あり得ませんよ～
もし、コードを書き換えるつもりでないのでしたら、今時点でも、無駄な書き込みをしていますが、大変失礼なことをしたようです。今までの経験によるプライドがおありのようですが、コードをみると、とてもそのようには見えませんでしたので、いくつか、問題点を申し上げました。しかし、こちらとしては、その反応には当惑しています。ただし、あまり初歩的な部分については、気づいてほしいと願っていました。

私には、サブルーチンは、あくまでも、基本的な部分を卒業した方が書くべきものだという考えがあったから指摘しました。物理的な問題だという期待があったのかもしれませんが、今回は、VBAの入門レベルの方がミスする問題だと私は考えています。

これで、私からの書き込みは、終わりにさせていただきます。

'//
'空白が入らないように気をつけること,不安なら、ArSh(i)は、Trim(ArSh(i)にする
Const shNAMES As String = "130331,130401,130402,130403"
Const srcRNG As String = "A1:K11, A1:K12,A1:K13,A1:H14"
Const dstRNG As String = "A1,D1,H1,B1"
Sub Test1()
　Dim i As Long
　Dim ArSh As Variant
　Dim ArSrc As Variant
　Dim ArDst As Variant
　ArSh = Split(shNAMES, ",")
　ArSrc = Split(srcRNG, ",")
　ArDst = Split(dstRNG, ",")
　Application.ScreenUpdating = False
　For i = 0 To UBound(ArSh)
　　With Worksheets(ArSh(i))
　　　.Range(ArSrc(i)).Copy .Range(ArDst(i))
　　　.Copy
　　　Application.Calculation = xlCalculationManual
　　　Application.DisplayAlerts = False
　　　ActiveWorkbook.SaveAs ArSh(i) & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled　'こうあるべきかは不明
　　　Application.DisplayAlerts = True
　　　ActiveWorkbook.Close False
　　　Application.Calculation = xlCalculationAutomatic
　　End With
　Next i
　MsgBox "すべて終了", vbInformation
End Sub

この回答へのお礼

有り難うございます

確かにＶＢＡ初心者であり、
特に今回、伝えることを優先し
ＶＢＥを通さず、「意思が伝われば良い」と、
ぶっきらぼうな描き方をしたと思います。

加えて
お気を和らげようという意思での「語尾の和らげ」が
今回、余り功を成さなかったようでもあります。

全てにおいてお気を害してしまったことを謝罪します。
申し訳ございません。

ご指摘の点は
　定数をその場で指定せず、定数指定場所を設けそこで一元的に設定し、名前で管理する。
　同一処理をサブルーティンやループなどでまとえて一元的に管理する。
　必要な抑制、解放を行う。
など…
でしょうか、

プログラミングは
　・ニーズ
　・データ構造の理解
　・アルゴリズムの理解と選定
　・言語仕様への造詣の深さ
　・開発・管理コスト
　・引き渡し単価が何にあるか

これ位を理解していれば書けるのでしょうか？

この内今回、私は
　・言語仕様への造詣の深さ
だけが欠けているのですが、
この点が正しく伝わらず遺憾に思います。

ところで、
1本幾らで単発、書きっきり、スピードとメンテナンス性重視なら
「ループに纏める手間は費用対効果に合わない」
という選択肢も、あり！！
あっさりとループ導入を切り捨てる判断力も、必要だと思いますよ。

定数を１箇所で纏めて宣言するのは
とても良いことだと思いますが、 ね
でも、これも「絶対だ」とは、限らないですよね。
全ては柔軟な対応こそが、プログラム書きのキモだと思うのですがね？

特に今回、この場では
コードの美しさや、作法を踏まえた描き方を
相談しているのではなく、そこがキモではなく
「何が起こったのか知りたい」
ですよね？　この点のみがキモですよね？
本末転倒していませんか？
ご指摘、失礼ですが全て蛇足ですよ。

済みません
なんかね、
和尚なのに門前の小僧に　説法たれられている気がしてならないのですよ
「もっと広く見るところあるよね？」ていいたくて我慢できないのです。

誰かが作った「形式」のみを「継承」しているだけで「何故そうなのか」が欠落している
そんなように　見えてなりません。

折角心を砕いて助けようとして頂けている方に、
こんな言い方はないですよね。
済みません

でも、指摘点ずれてないですか？

「１＋１＝１」と十分理解しているものに
重ねて「１＋１＝１」と説いても
「へ？」と、云われるだけですよ。

「敢えてこうしているのでは？」と、云う視点も
必要ではないですか？

蛇足ですが
つたないながらも「結構大きめな内容のアプリケーションを１本、単独で書き上げる実力があるもの」
と、私を見る気にはなれなかったのですね、
遺憾ですね。

お礼日時：2013/04/02 20:35

No.5 回答者： WindFaller 回答日時： 2013/04/01 21:29

>頭に真理値表を認識している方が、「付けなくて良いじゃん」と言い得ることが、不可解です。

私は、別に、付けなくてもよいとか、付けなくてはならないとか言うのではなく、その意味が分かって"ByVal"を付けているのですか？と言っているだけです。コードからすると、その使い方を知っているとは思えませんから、不自然に感じたのです。仮に、値渡し・参照渡しを気にして作った所で、Callを使ってしまえば、意味がありません。それは、VBA文法では基本的なことです。ただし、私なら、その部分は、おそらく参照渡しにするかもしれませんね。

VBA自体が不慣れというよりも、エクセルにはエクセルの特徴があります。だから、言語の違いというより、ステップ・マクロで動かしてみれば、どこが問題が浮き彫りになります。それが、今回の原因とは特定するかは分かりませんが、私なら、まず、全体を書き直します。ざっと書いて、それを掲示板で聞いて修正するというスタイルではなく、ご自身で気づかれてほしいものです。そのコードでは、メモリを無駄に消費しているだけなのです。

この回答へのお礼

Byvalの意味は理解しているつもりですよ～
他言語でみっちり値渡しについては学びましたからね～
※ちょっと炎上しかけている感じなので語尾を和らげました。

メモリーが無駄になっている
と云うことですが
このByval分のことなのでしょうか～　？

此の分くらいを節約するなら
そもそも構造化なんかしない方が
よっぽどメモリ節約になるのでは～　？

でもそれを、敢えてやるだけの価値があり、その癖付けこそが重要だ
と、思うこそ　だからこそのこのコードなのですよね～
(※注：僕ってば云っていることがいい加減ですよね、
「なら何故セーブも別ルーチンにしない？」
と、突っ込まれそう…　汗)

あと、このコードは、もう既に手元に　本ちゃんのコードがなく、見れない状態なので、
うろ覚えで書いたものです、
(まあ、100％独力だけで　調べながら書いたものなので、
何処で何をさせようとしたか…　位は覚えてますけどね
そもそもコピーと入力とセーブとメッセージ出力、操作者の誘導と
ループなどの進行制御以外はほぼしてませんけどね)
(※注：エラー回避処理とエラー発生時処理は
組み込む予定だったのですが、ケツカッチンで　…　　涙)

そうそう
当然この質問を元に　本ちゃんのコードが書き換わることは、あり得ませんよ～
質問にも記載したとおり、「未来への布石」ですから。

後、私は殆どのことがシート関数で出来る方ですから
VBAにはさして思い入れが未だありません。
進行制御と入出力がシート関数では辛いので
仕方なくVBAに手を出している。
そんな感じです。
シート関数の方が圧倒的に早いという記述もあるようですし。(真偽は不明)
まあでも、手を出したからには、
「ちゃんと全てのことがVBAだけでも出来るようになりたい。」
とは、思いますがね。

ところで
描き方のスタイルに拘るよりも、構造化プログラミングが叫び始められた頃のように
「同をどう書くか」よりも　「何をどういう品質(スピードと開発性含み)で提供するか」
と、云う　本質を見据えた視点に　立ち返り、拘りたく思うのですが、如何でしょうか？

品質の維持向上
開発の高速化・難易度の管理
更新性・改変性・管理性の向上

凡そ、これ以外の目的のこだわりで
書き手のマスターベーションにならないものって
あるものでしょうか？

にわかに思い付けない自分が力不足なのかな…　？


もう1つ、
Excel開発環境の最も突出した特徴って…
「シートとVBAの双方でプログラミングできる」
じゃないですか～　？

シートでのプログラミングは大凡全て1つのセルあたり数文字～数行で済み、
そのミニマムな規模に比べて圧倒的な
大規模なことが　出来ます、よね？
(※注：済みませんシート関数至上派だったもので
ちょっと意見が偏り気味でしょうね、謝罪します)

これを踏まえるとVBAの書き方なんでこだわる気になりません
刺身に拘らず、その大根のツマにのみ拘っているように見えますよ？
所詮ＢＡＳＩＣでしょ?　…　　←まだなめている(汗)
しかもＶＢですらないサブセットだし…　←更になめきっている(陳謝m(_ _)m)

もっと端的にご指摘頂ければ
あぁ！！、なるほど！！　と、膝をたたく
と、思うのですが。
済みません、今までの言動でもお判りのように鈍才なもので
解りやすくご教示頂ければ幸いです。

P.s.
先ほど気づいたのですが
前段でセーブしていて、その後余り大した処理をしていないので
「演算によるビジーでエラー」
という筋は考えにくいかと…
セーブ前に全演算を　Excelは済ませようとしますよね？
なので自動演算を抑制して
手動演算にしておいて、後で纏めて計算させる
は無しかと…

まあ
手動計算で演算を抑制→自動計算で一気に演算
て、やっちゃうと
進行状況表示が難しそうですから…　ね

操作者による停止が頻発して
「これ、使い物にならない…」と云う、穿った評価がつきまといそう。　…汗＆涙

私の視点は常に「出口(ユーザー・使用者)にのみ向いています」のです。
開発過程とかコードの美しさなどには、未だ興味があら無いのです。　です汗
(敢えてこう書きました、本来は「興味が未だわかないのです」と書くべき…　それこそが普通…
なのは解っていますが、　これらはそれとは違う表現なのです)

開発過程とかコードの美しさなどが
メンテナンス性以上に、ユーザーにとってどんな価値があると…　？
メンテナンス性を追求するなら、コードの美しさ・作法より構造化…　ですよね？
(※：セーブを別ルーチンにしなかったものがよく云うな…？　Noubleよ謝れ）
陳謝m(_ _)m

お礼日時：2013/04/02 02:36

No.4 回答者： WindFaller 回答日時： 2013/03/31 11:35

#1のお礼に書かれた内容について、書き間違い自体は看過しても、その書き間違い自体に、初歩的な所が守られていないようですと、失礼かもしれないけれども、基礎的な知識があやふやになっているようです。

ざっと見ただけでも、どこかで必ずエラーが出そうだなって思えます。
一度、ひとつ、ふたつのシートをコピーして、シュミレートしてみましたか？

そのコードは、書き慣れている人なら、エラーが出る出ないに関わらず、ひと目で、そのコードの考え方自体がヘンだと分かります。

>　改編性を高める
>　コピー動作時のエラーと、指定値演算時のエラーを切り分ける
>　アクティブブックが他のVBA実行によりきり変わる

#1のお礼の、その理由・理屈はごもっともなのですが、エラーを誘発する以前に、コードをみると、そこまでの実力が伴っていないと思います。残念ながら、全体の設計から直さないとダメです。実力が上がるまでは、そのようなサブルーチンを使って、構造化はしないほうがよいです。たぶん、掲示板などをご覧になって書かれてような気がしますが、最近、ここの掲示板のVBAの回答のレベルが低下しているように思います。それを真似てもどうしようもありません。単発では動くけれども、実用には適さないものもあります。

簡単に直せるものとして、細かい部分だけを指摘しておきます。

#3さんのおっしゃっている中では、
Application.Calculation = xlCalculationManual
は、ワークシートに数式を埋め込んでいる部分が多い場合は必要です。

ここの掲示板で同じような内容のコードを見せられていますが、
Callで呼び出せば確かに、明示的にByValキーワードを付けなくても、値渡しで渡りますが、なぜ、そうする理由があるのか良く分かりません。

VBAでは、予約語自体は少ないので、別にエラーが出るわけではありませんが、コードが乱暴です。変数名は、もう少し考えて付けましょう! SheetやNameという変数名は使わないほうがよいです。

原因は想像できるのですが、私はここの常連ではありませんので、熱心にここで回答をし続けることができません。最終的には、コード全体を書きなおさなくてはなりません。しかし、それを直す以前の問題を解消しないと、別の問題が出てきそうなので、個別にひとつずつ直していくしかありません。

この回答への補足

反省のためのみにガラケー片手にNintendo３DSで書きました
PCには私的環境下では一切、一度たりとも乗せてません
(仕事環境ではデバッグしてましたが)

そもそも仕事と同一どころか類似した環境すらありません(２００７がない)

補足日時：2013/04/02 02:46

この回答へのお礼

まず謝罪させてください、
　・質問の内容、意思を伝えることのみを重視し、コード記述を軽視した
　・他言語でプログラミングや思考を習得したが、ＶＢＡ自体には不慣れである
　・ＢＡＳＩＣをなめていた
すみません、使用変数名や構文など、
本ちゃんでないこの時は　とてもとてもいい加減にしました。

>Application.Calculation = xlCalculationManualは、ワークシートに数式を埋め込…
有り難うございます、学びました。

>…確かに、明示的にByValキーワードを付け…
仰ることが「付けなくて良いじゃん」は、ありえない
と、云うことならば同感です。

「真理値表」と、云う概念を、常にソフトウエア技師は　頭で認識しているものと思いますが、
頭に真理値表を認識している方が、「付けなくて良いじゃん」と言い得ることが、不可解です。

「む」「無い」という事は「ゼロ」の概念と並び立つ
この世の深淵に迫る、崇高な原理の１つだと思います。

有り難うございます。

お礼日時：2013/04/01 15:40

No.2 回答者： WindFaller 回答日時： 2013/03/31 03:01

>「選択範囲が大きすぎます」

もしかしたら、PasteSpecial など、Excelのワークシート側のメソッドを多様しているのではないでしょうか？ワークシート側のメソッドを使って広範囲の選択をしたりすると、トラブルが発生するようです。そう言われれば、思い当たるのではないでしょうか。

>シートにいっぱいもの(関数式)を詰め込みすぎなのか
VBE側は、単なる文字列のように見えますが、あれはあれで、常に、ワークシート側のオブジェクトを捉えているようです。(説明すると長くなりますが、VBAは、中間言語として変換されている）だから、Rangeオブジェクトの範囲というよりも、固定の範囲の数を書きすぎても、問題は出てくるようです。(ただし、今回のエラーとは種類が違います）

>同様のことが起きたときに考慮すべき対処をご教示ください。

私個人は経験がないのですが、いつもワークシート側のメソッドは不必要に使わないようにしています。PasteSpecialも、そのひとつです。(オブジェクト・ブラウザでは、「Excel」の範疇に入ります）

もうひとつは、VBAの基本なのですが、1プロシージャのコードは、100行以内に収めるべきで、それを広げるなら、サブプロシージャにして、構造化すべきなのですね。

この回答へのお礼

ありがとうございます。

確かにワークシートのメソッドを多用してます。
て、言うかVBAでしているのは
入力と、ブックの保存と、
ワークシート関数の再展開の
ほぼそれだけで
シートに書き込んだ関数で
全処理を賄っています。
(VBAよりシート上の演算の方が高速らしい…　？)

ただ、そのままだとブックを開いたり、保存したり、
する際に時間がかかりますし、
保存時の容量も巨大ですので、
普段は縮小版にしておいて
使う時に完全体にVBAで展開させてます。
でもこれ…
VBA的には苦手なやり方なのですね！？

ワークシートのメソッドを多用しても
平気にする方法
ありますか？

あと、シートをセルコピーで展開する際の
コピー動作に対する仮想記憶領域不足も懸念されます。

両対処法を
ご教示頂けたら幸いです。

お礼日時：2013/03/31 05:41

No.1 回答者： DreamyCat 回答日時： 2013/03/30 21:50

コードも表のデータ範囲も

コピー選択範囲も
PCの性能も
Officeのバージョンもわからないうえに
確認した内容も書かないのでは
ただの愚痴を書いているだけですね。

Excel　２００７以降のセル範囲は非常に大きいですから
Range("B:X").Copy　などの書き方だと場合によってはそんなことが起こるかもしれません。
確認済みかもしれませんが、
・Application.CutCopyMode = false
・DoEvents
・巨大な範囲の時は何回かに分けて実行するとあまり遅くならない。
・Windows　XPのPC　や　初期のVista PCだとメモリー不足のことが多いです。
・ファイルが壊れかけている。（壊れている）
などについて調べましたか？

＊コードとPCの機種を書けばすぐに解決案が出るでしょう。

この回答へのお礼

ありがとうございます。

私用のPCでないので環境については
済みません、控えさせてください。

コードは少し変わったことしてます。
原文のままではないですが、

Sub MAIN()
Dim Name As String , Address As String , tmpstring As String
…
…
… 'ダイアログボックスで保存パスを指定しtmpstringに入れている行
　　ThisBook.Saveas Filename:=tmpstring Fileformat:=52
　　Name = ThisBook,Name
　　Address = Replace(tmpstring,Name,¨¨)
…
…
　　Call rangecopy(¨sheet1¨,¨A4:I4¨,¨A5:I20004¨)
　　Applecation.DisplayAlerts = fales
　　Workbook(Name).SaveAs Filename:= Name
　　Applecation.DisplavAlerts = True
…
…
…
…
　　Call rangecopy(¨sheet2¨,¨C6:X6¨,¨C7:C20006¨)
　　Applecation.Display.Alerts = fales
　　Worksheet(Name).SaveAs Filename:= Name
　　Applecation.DisplayAlerts = True
…
…
…
　　Call rangecopy(¨sheet3¨,¨B4:I4¨,¨B5:B20004¨) 'この辺りで毎回エラー発生
　　Applecation.DisplayAlerts = fales
　　Workbook(Name).SaveAs Filename := Name
　　Applecation.DisplayAlerts = True
…
…
…
End Sub

Sub rangecopy(Byval sheet As String . Byval domain1 As String . Blval domain2 As String)
　　With Sheet(sheet)
　　　　.Range(domain1).Copy .Range(domain2)
　　End With
End Sub

全くこの通り　と、言うわけではないですが
まあまあ忠実です。

わざわざコピーをコールしているのは
　同動作を1箇所の記述に集約する
　改編性を高める
　コピー動作時のエラーと、指定値演算時のエラーを切り分ける
(※注 :指定値は主に演算で出すので、…)

SavsAsを使っているのは
　アクティブブックが他のVBA実行によりきり変わる…
　と、いう　希薄な障害性への対応

なとといった狙いがあります。

如何でしょうか？
記載を怠り申し訳ありません。
(※注 :一応調べしながら、確認しながら書きましたが、誤記はご容赦ください)

お礼日時：2013/03/31 05:08