Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 7 Professional : Excel 2010)
Variant変数にセル範囲を代入する場合の容量の目安について
投稿日時: 17/12/21 20:17:49
投稿者: yyysss0129

いつもお世話になっております。
 
質問させて頂きます。
 
元ブックに、100シートデータがあり、
1シートの情報量が大体
・5000行*77列
(1列のバイト数が、LenB関数で調べると450バイトほど)
という状況です。
また他に別シートで、300000行×81列、というシートが1枚あります。
(そのシートの1列も、LenBで450バイトほど)
よって、開くときに非常に重いファイルです。
 
上記の元ブックをベースとし
以下の処理を行っております。
 
以下のコードで
処理としては、別のブックを開いて、その別のブックのシートにデータを貼り付けるという作業です。
(100シート→100ブックにはりつける)
 
Sub copyTargetSht_ToAnotherFile(ws As Worksheet, tmp_ws_dta As Worksheet, kahen_endrow As Long, tmp_startrow As Long, tmp_endrow As Long)
 
    Dim tmpVar As Variant
     
    With ws
        tmpVar = .Range(.Cells(6,1), .Cells(kahen_endrow, 77))
     
        With tmp_ws_dta
            .Range(.Cells(tmp_startrow, 1), .Cells(tmp_endrow, 77)) = tmpVar
        End With
    
    End With
End Sub
 
(wsとtmp_ws_dtaはそれぞれ別のブックのワークシートです)
 
 
貼り付け後は、
1シートの作業終了直後に
tmpWb.Close SaveChanges:=True
をする事で、都度、1ブック毎に閉じています。
 
また、この作業と同時に
「縦管理の別ブック」というファイルを開いておりまして、
1シートに100シート分を縦に貼り付ける作業も行っております。
その処理は、上記コードと同様な感じで、
Variant型変数(tmpVar)に、セル範囲を代入する形です。
(上記とは別のsubプロシージャで実行しています:コードの内容はほとんど一緒です)
こちらの縦管理のブックは、100シートを貼り付けた後に、
.Close SaveChanges:=True
で閉じております。
 
この処理をすると、
「メモリが不足しています」
というエラーが出てしまいます。
 
1.途中でエラーが出て止まる場合もある
2.全部、処理が終わった後に、上記のエラーが連続して(5個ほど)
一気にでてくる場合もある。(処理は上手くいっている)
 
情報が足りないかもしれませんが
 
質問1 上記コードに何か不具合がありますでしょうか?
→tmpVarを都度、処理後に初期化(tmpVar =empty)するなど工夫が必要でしょうか?
質問2 そもそも、Excel内の情報量として、元ブックの情報が多すぎるでしょうか?
質問3 5000行×77列のデータを1回でVarinant変数に配列として格納するのは
量が多すぎるでしょうか?
(2500行で2回とか、1500行程度で3回、など、分割するほどの情報量でしょうか?)
 
おそらく、上記エラーは
「Variant変数へのセル範囲のレンジ」が多すぎるからなのか?
と思っているのですが、
例えば、100列位で、LenBで計って1行450バイトだとして、
皆さんは、何行くらい
セル範囲として、Variant変数に代入するのが適正だと思われますか?
 
「メモリが不足しています」はいろんな要因が考えられるようで
理由がよくわかりません。
 
この部分だけでも、クリアになればよいと思って
質問させて頂きました。
 
情報が足りないかもしれませんが、
ご回答頂けますと幸いです。
 
宜しくお願い致します。
 
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 17/12/21 22:57:04
投稿者: もこな2

yyysss0129 さんの引用:
元ブックに、100シートデータがあり、
1シートの情報量が大体
・5000行*77列
(1列のバイト数が、LenB関数で調べると450バイトほど)
という状況です。
また他に別シートで、300000行×81列、というシートが1枚あります。
(そのシートの1列も、LenBで450バイトほど)
よって、開くときに非常に重いファイルです。
確認ですが、77列なり、81列のLenB関数の合計が450バイトほどなんですか?(LenB関数って配列使えたんでしたっけ?)
もし、そうだとしたらですが、個人的には1シートはそんなに重くないような・・・・
1ブックに100シートあるほうがよっぽど問題だろうとおもうんですが(そんな無謀なファイルを扱ったことがないのでよくわからないですけど)
 
そして、提示のあったコードにコピーなり、貼り付けなり、ブック保存なりをしてる部分が見当たらないのですが、どの辺でメモリ不足ってでてしまうんでしょう?
(あっ valieの参照はありますね。 ただ wsが 1つしか渡ってないので 呼び出しもとのプロシージャでループがかってるんでしょうか?)
 
秘匿情報があって簡単にコードを提示するのが難しいのは解るんですが、もうちょっと全体の処理フローが見えた方がアドバイスしやすそうです。
Varian型変数で受け取らないで、Copyメソッド使って貼り付け先を引数としてあたえてやったほうがいいような気がしますけど。。。前後がわからないのでなんとも・・・)

回答
投稿日時: 17/12/21 23:05:28
投稿者: simple

使用可能なメモリ量というものは、
Excelそのものの物理的制約というよりも、
各人のPCの環境(積んでいるメモリーの量や、
他のアプリケーションの使用状況など)に
依存する部分が大きいと思います。
 
ですから、制約がどの程度かということを検討するのは
余り益があるとも思えません。
 
現在、エラーが出ることは間違いないのですから、
処理の分割などの回避方法を色々トライするよりほかない、
と思います。
 
例えば、
> また、この作業と同時に
> 「縦管理の別ブック」というファイルを開いておりまして、
> 1シートに100シート分を縦に貼り付ける作業も行っております。
ということのようですが、
この処理を別に実行できれば、
使用するメモリーは節約できるのではないですか?
 
ブックも、二つに分けても結果が得られる性質の作業なら、
分割することも有益でしょう。
 
-------------
なお、別スレッドで定数の数に注目されていますが、
150個くらいは別にメモリを圧迫するほどのことではないと思います。
それよりも、理解可能性を高めるということに留意したほうが
益があると思います。
 
プロシージャについて言えば、余り大きなものよりも、
機能によって独立した複数のプロシージャに分けた方が分かりやすくなります。
定数もそれと同じで、同時にそれだけの数の定数が必要とも思えませんから、
分割しておくほうがよいのではないですか?
 
そんな印象を受けます。
 
釈迦に説法なら忘れて下さい。

回答
投稿日時: 17/12/22 15:16:12
投稿者: WinArrow
投稿者のウェブサイトに移動

1列のバイト数をLENBで求めていますが、
重い/軽いを判断する数値としては、余りのも無謀(見当違い)です。
Excelの場合、「重い」を云々するときに、
考える要素としては、ファイル容量もありますが、
使われている数式の文字数と量、書式の設定の量など、目に見えないところのデータ量が大きく影響します。
LENBだと数式の場合、結果の桁数しかわかりませんよね?
勿論、マクロコードの量も影響します。
マクロについていえば、
削除したコードの行もファイルの中に残っています。
それも読み込み時にはメモリに展開されます。
 
いろんな要素で出来上がっているので、
目に見えるところだけ取り出して計算しても、意味がない。
そんな計算は、労多くして益なし。

回答
投稿日時: 17/12/22 15:53:17
投稿者: 細雪

エクセルの限界に挑戦中ですか!?
 
 
というわけで、素朴な疑問。
 

yyysss0129 さんの引用:

Sub copyTargetSht_ToAnotherFile(ws As Worksheet, tmp_ws_dta As Worksheet, kahen_endrow As Long, tmp_startrow As Long, tmp_endrow As Long)
  
    Dim tmpVar As Variant
      
    With ws
        tmpVar = .Range(.Cells(6,1), .Cells(kahen_endrow, 77))
      
        With tmp_ws_dta
            .Range(.Cells(tmp_startrow, 1), .Cells(tmp_endrow, 77)) = tmpVar
        End With
     
    End With
End Sub
 
質問1 上記コードに何か不具合がありますでしょうか?
→tmpVarを都度、処理後に初期化(tmpVar =empty)するなど工夫が必要でしょうか?
質問2 そもそも、Excel内の情報量として、元ブックの情報が多すぎるでしょうか?
質問3 5000行×77列のデータを1回でVarinant変数に配列として格納するのは
量が多すぎるでしょうか?
(2500行で2回とか、1500行程度で3回、など、分割するほどの情報量でしょうか?)

 
とりあえず、質問1については細かいことを置いといて、
エラーが起きず、求める結果を得られるなら不具合は無いとしか言いようがないですよ。
 
ただしコレ、配列に代入して転記先に配列から書き出す理由ってなんでしたっけ?
データを加工するのかしないのか、も含めて、
素直にコピー・貼り付けの方が時間は短縮できるとはずです。
  ws.Range(ws.Cells(6,1), ws.Cells(kahen_endrow, 77)).Copy tmp_ws_dta.Cells(tmp_startrow, 1)
                 転記先の「end」を指定する必要もないと思いますが・・↑
加工するにしても、貼り付けた後に纏めて(ワークシート上で)やれば、
配列に格納されたデータを弄るよりはるかに速く、負担も少ないはずです。
 
ちなみに・・・
A1セルに =REPT("A",450)で450バイト、コレをコピー、そのままA1に値貼り付け。
=LENB(A1)で450バイトであることを確認。
これをA1:CV10000(10000行100列=100万セル)の範囲にビッチリコピペ。
シート1枚の状態で保存して、2.8MBほどでした。
単純に1バイト文字の羅列だからそんなに大きくならんわな。
あまり参考にはならんなぁ。まぁいいや♪  と思いつつ、
Sub sample1()
Dim myArr As Variant
  ST = Timer 'タイマースタート
  myArr = Sheets("Sheet2").Range("A1:CV10000") ' ヴァリアント型に代入
  Sheets("Sheet3").Range("A1:CV10000") = myArr ' 書き出し
  Debug.Print Timer - ST ' 経過時間をイミディエイトに書き出し
End Sub
Sub sample2()
  St = Timer
  Sheets("Sheet2").Range("A1:CV10000").Copy Sheets("Sheet3").Range("A1") ' 単純にコピペ
  Debug.Print Timer - St
End Sub
の2通り、同一ブック内での転記ですが、それぞれ20回ずつ試行しました。
sample1 は、平均で10.6秒ほど
sample2 は、平均で1.3秒ほどでした。
 
 
配列に入れて他で何か処理をしているのかもしれませんので、
この辺は一概にどうこう言えるものでもありませんが・・・
色んな意味で失礼かとも思ったのですが、ちょっとだけ気になったもので。
 

回答
投稿日時: 17/12/22 16:07:02
投稿者: もこな2

WinArrowさんに便乗して・・・
 たくさんのシートがあるということですが、シート間の参照があったり、関数が仕込まれていたりしませんか?そしてブックの計算が自動になってませんか?もしそうであれば、これもまた開くときに重くなる要因と思われます。
 
とりあえず、昨晩Copyメソッドを使ってみては〜と発言してしまいましたが、撤回させてください。
 

引用:
Dim tmpVar As Variant
tmpVar = .Range(.Cells(6,1), .Cells(kahen_endrow, 77)).Value
※赤字部分は追加しました。
ということですから、実態はString型の2次元配列ができあがっているように思います。
この場合、ほしいのは結局各セルのValueですから
.Range(.Cells(tmp_startrow, 1), .Cells(tmp_endrow, 77)).Value =
.Range(.Cells(6,1), .Cells(kahen_endrow, 77)) .Value
(↑抜き出したままなので意味が通じなくなっていますがそこはご了知願います)
のように、変数に入れずに直接参照すればよいかと思いました。
 
いずれにせよ、私の場合Variant型の変数を経由してデータをコピーしようとしたことがないので、ご質問に回答することは難しいです。
 
参考までに質問趣旨から外れてしまいますけど、私ならこんな感じで処理するというのを考えてみました。
参考になりましたら幸いです。
 
<前提条件>
メインブック(101シートを持っているブック)構成
  1シート目のA列に、データをコピーしたいBook名がリスト化されている。
  2シート目以降の順番と、1シート名のリスト順は一致している。
  マクロもこのブックに置いてある
貼付先ブック(100個のブック)
 貼付先のシートはどのブックも同じ(たとえば1シート目でOKとか)
 メインブックと同じフォルダに置いてある。(または、置いてあるフォルダが固定されている)
縦方向管理ブック
 貼り付けたいシートは決まっている(たとえば1シート目とか)
 コピー元、コピー先は決まっている
  (2シート目のA列を、縦方向のB列、3シート目のA列を、縦方向のC列・・・とか)
Sub Sample()
'===変数の宣言とか
Dim MyWB As Workbook: Set MyWB = ThisWorkbook
Dim 貼付先WB As Workbook, Copy元SH As Worksheet
Dim 縦方向SH As Worksheet 'メモリ節約のため縦方向ブックを常時開かず、蓄積してから一気に出力
Dim c As Long, i As Long, 貼付先行始 As Long, 貼付先行終 As Long, Copy元行終 As Long

'===主処理
Set 縦方向SH = Worksheets.Add(After:=Sheets(Worksheets.Count))

For c = 2 To ThisWorkbook.Worksheets.Count - 1
    '---副処理1(シートごとに別ブックへコピー)
    Set 貼付先WB = Workbooks.Open _
         (MyWB.Path & "\" & MyWB.Worksheets(1).Cells(c - 1, 1).Value)
    With MyWB.Worksheets(c)
        Set Copy元SH = ThisWorkbook.Worksheets(.Name)
        Copy元行終 = .Cells(.Rows.Count, 6).End(xlUp).Row
        With 貼付先WB.Worksheets(1)
            貼付先行始 = .Cells(.Rows.Count, 6).End(xlUp).Row + 1
            貼付先行終 = 貼付先行始 + (Copy元行終 - 6)
            .Range(.Cells(貼付先行始, 1), .Cells(貼付先行終, 77)).Value = _
                Copy元SH.Range(Copy元SH.Cells(6, 1), Copy元SH.Cells(Copy元行終, 77)).Value
        End With
    End With
    貼付先WB.Close SaveChanges:=True

    '---副処理2(縦方向のコピー)
        '現況コードが不明なので記述省略
        'とりあえずメモリを気にされているのでブックをもう一つ開くよりは
        'メインブックに1シート追加してそこに蓄積してから最後にはき出した方が
        '多少はメモリ節約できるんじゃないかとおもいます(試してないので単なる思いつきです)
Next c

    '---副処理3(縦方向シートの出力)
        '現況コードが不明なので記述省略

'===後処理(オブジェクト解放)
    Set MyWB = Nothing
    Set 貼付先WB = Nothing
    Set Copy元SH = Nothing
    Set 縦方向SH = Nothing

End Sub

 

回答
投稿日時: 17/12/24 12:02:29
投稿者: LMK

既に回答されていますが、
 
元のセル範囲のValueを一旦Variant型の変数に格納し、先のセル範囲のValueに指定する
  ↓
Variant型の変数を介さないで、元のセル範囲のValueを先のセル範囲のValueに直接指定する
 
を試して見られたらどうでしょう。(まあ結果はほとんど変わらないと予想しますが)
いずれにしろそうやってひとつひとつ原因を絞り込む (rule outする) しかありません。
コンピュータや機械を使うのって、そういうことですよね。
その経験の差=スキルの差だと思います。

回答
投稿日時: 17/12/24 18:35:22
投稿者: WinArrow
投稿者のウェブサイトに移動

メモリ内で、変数の場所は、いつも同じとは限らない・・ということを認識した方がよいでしょう。
セル範囲の「値」を格納するVariant型の変数
格納する側のシートのセル範囲が同じではないから、メモリのマッピングは変わる(しかし、使わなくなったメモリは返却されないから、増えます。
でもね、確かめる方法はない・・・・
 
対応策としては、使い終わったメモリは、Eraseで解放することが必要かも・・・
 
もう一つは、LMKさんのおっしゃるように
Variant型の変数を介さないで、元のセル範囲のValueを先のセル範囲のValueに直接指定する
 
 With ws
    tmp_ws_dta.Range(.Cells(tmp_startrow, 1), .Cells(tmp_endrow, 77)) = _
                         .Range(.Cells(6,1), .Cells(kahen_endrow, 77))
 End With

回答
投稿日時: 17/12/24 18:38:51
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません。」コード訂正
 
 With ws
     tmp_ws_dta.Range(.Cells(tmp_startrow, 1), .Cells(tmp_endrow, 77)).Value = _
                          .Range(.Cells(6,1), .Cells(kahen_endrow, 77)).Value
  End With

投稿日時: 17/12/24 22:48:39
投稿者: yyysss0129

皆様
多様なご意見いただきまして、誠にありがとうございます。
 
 
<シートの状況について>
・申し訳ございません。
1列450バイトではなく、1行450バイトの間違えでした。
 
・書式は1シートに対して
3列目から18列目のみ罫線、14列目から18列目に条件付書式(各5000行ほど)
程度で、関数は一切入れておりません。
 
csvを取り込んで、プログラムで演算して、100枚のシートに吐き出す
という感じの処理です。
よって、プログラム内で演算しているので、
シートに関数は一切入れておりません
(データ量が多いので、関数を入れると重くなるので、上記の設計を致しました)
 
<値のバイト数のみで質問した理由>
Variant型の2次元配列で、データ移行をしているため、
Variant型に含まれる値のデータ量が多すぎるのか?
という疑問があったので、質問させて頂きました。
 
一般的に、Variantの記憶領域サイズが、16〜22バイト(数値と文字で変わる)とのことですが、
この記憶領域サイズと、LenBで図ったバイトとの換算方法?がわかりませんでした。
それで、上記の質問をすれば、詳しい方が答えてくださることを期待して、
以上のような質問となりました。
言葉足らずで申し訳ございません。
 
<Copyや、直接値を移行するわけではなく、Variantを使った理由>
私の勘違いかと思いますが
Excelのメソッドよりも、配列(メモリ部分を使うこと)の方が何かと早い
と、WEBで学んだからです。
その考えを、無根拠に(検証なしに)受け入れていたので、
なんでもかんでも配列で処理していました。
細雪様のご指摘どおり、Copyメソッドを使った方が、
数倍早く(3分くらいの処理が、1分で終わりました)
私の「配列信仰」が覆される結果となりました。
 
Excelをアクティブにしない(Selectなど描画に関わる部分を使わない?)
なら、配列よりも、Excelのメソッド(今回でいえばCopy)の方が早いのか
と学びました。ありがとうございました。
 
<まとめ>
と、私の言葉足らずの質問でしたが、皆様、いろいろありがとうございました。
 
以下、それぞれの方にご回答させて頂きます。
 
・もこな2様
サンプルコードまで頂きましてありがとうございます。
もう少し、コードを詳しく書ければよかったのですが、
いろいろ複雑で要点だけの記載となりました。
ありがとうございました。
 
・simple様
処理の分割化につきまして、採用させて頂きました。
ユーザー目線で考えれば、一気にが良かったのですが、
もろもろExcelが限界という感じです。
「処理の分割」「プロシジャの分割」「定数の分割」
の視点、大変参考になりました。
理解可能性という質にもこだわっていければと思います。
ありがとうございました。
 
・WinArrow様
メモリに対する多様な要素を考慮せねばならない点
アドバイスありがとうございました。
メモリの中で、変数の場所はいつも同じとは限らない
とのご指摘、メモリに対する洞察力が増したように思います。
大量データ時代に、VBAで処理を任されるにつけ
メモリなどの基本的な知識がとても必要になると、実感いたしました。
事務からコツコツVBAで進めてきた素人なので、
もろもろ不足を感じております。
ありがとうございました。
 
・LMK様
ruleOutの点、ありがとうございました。
確かに、試して自ら確認するのが一番早いかもしれませんね。
もっと、試行錯誤してみたいと思います。
ありがとうございました。
 
・細雪様
Copyメソッドの実行結果を教えて頂きましてありがとうございました。
エクセルの限界に挑戦したいわけではないのですが、業務で要請されてまして。
 
30万行81列のCSVを一度Excelに取り込んで
そこから、演算して、100枚のシートに吐き出しています。
 
今回、皆さんからお知恵を拝借して
・CSV直で、演算できないものか
・ブックを分割し、別のブックのシートに取り込んだデータを、
メインツールで演算すれば、もっとファイルが軽くなるのか
 
など、改善点が浮かびあがりました。
 
ADOなどで、やってみたりしたのですが、
吐き出す演算の形がとても特殊で、SQLでは、私の技術では形にできなかったこともありまして・・・
 
いずれにしても、30万行81列(1行450バイトほど)
のデータは、今の私にとっては、ハンドリングが大変な量です。
 
もう少し勉強して、上手いやり方を考えたいです。
(Accessは、クライアントサイドからNGが出てしまいまして、
結果、Excelでやることになった事情があります・・・)
ありがとうございました。
 
 
ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
ということで、大変ためになりました。
助かりました。
 
皆様、本当にありがとうございました。
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 17/12/24 22:59:14
投稿者: WinArrow
投稿者のウェブサイトに移動

まだ、バイトにこだわっているようですが、
セルに吐いているデータの内容で、メモリ容量は変わります。
 
文字列:全角文字は1文字2バイト、半角文字もUNICODEなので2バイト
数値:データ型でバイト数が変わります。
例えば、表示形式が「標準」で数値を入力すると
データ型は、Double(8バイト)になります。
 
目に見えた文字(数値を含む)だけの桁数をバイト換算しても無意味です。

回答
投稿日時: 17/12/25 11:22:41
投稿者: もこな2

何となくですが、私が言っている「重い」に対するコメントとyyysss0129さんが気にされている「重さ」にずれがあるのかなぁと思いました。
私が言っている(WinArrowさんの仰っていることも同じではないかと勝手に推測)「重い」は

引用:
(1列のバイト数が、LenB関数で調べると450バイトほど) という状況です。
また他に別シートで、300000行×81列、というシートが1枚あります。 (そのシートの1列も、LenBで450バイトほど) よって、開くときに非常に重いファイルです。
というのを受けて、ファイルとしての重さ(開くときに重い)を論じるならば、ブックに含まれるマクロや、書式、数式の再計算等が含まれるので、セル値だけ見ても判断の材料になりません。
また、1列なり1行のセルごとのLEN関数の合計が450であるなら、データ量としてそんなに重いのだろうか?という感覚を持ちますが、Variant型(String型の2次元配列?)に入れるデータ量として450×5000バイトが多いのかどうかは、繰り返しになりますが自分にはわからないので割愛します。
 
また、
引用:
・書式は1シートに対して3列目から18列目のみ罫線、14列目から18列目に条件付書式(各5000行ほど) 程度で、関数は一切入れておりません。
csvを取り込んで、プログラムで演算して、100枚のシートに吐き出す という感じの処理です。
とのことですが、提示のありましたコードを拝見する限りではコピー先には条件付き書式をコピーしていないですし、条件付き書式を条件に演算処理をするシーンというのが浮かばないので、そもそも条件付き書式は要らないようにも思いますが、それはまぁ別のことですよね。
 
ところで、CSVファイルを取り込んで演算してからブック内の別シートへ出力してさらにそれを別ブックに出力という処理を踏んでおられるのであれば、演算内容にもよるとおもいますが、以下のようにCSVファイルをテキストファイルとして扱う手もあるんじゃないかと思います。
@Open ステートメントで目的のCSVを開く
A 行ごとにSplit関数つかって配列にする
B 配列を使って演算する
C 演算結果を別のテキストファイルに出力する
DCloseステートメントで目的のCSVを閉じる
E作成したテキストファイルを別ブックにそれぞれ取り込ませる
(素人考えなので、既に試されておられましたら、お目汚し失礼しました。)

回答
投稿日時: 17/12/30 10:35:31
投稿者: mattuwan44

んと。遅ればせながら、、、、
 
>「メモリが不足しています」はいろんな要因が考えられるようで理由がよくわかりません。
http://officetanaka.net/Excel/vba/error/execution_error/error_7.htm
http://vbabeginner.net/%E3%82%A8%E3%83%A9%E3%83%BC7%E5%AF%BE%E5%BF%9C%E6%96%B9%E6%B3%95%EF%BC%88%E3%83%A1%E3%83%A2%E3%83%AA%E3%81%8C%E4%B8%8D%E8%B6%B3%E3%81%97%E3%81%A6%E3%81%84%E3%81%BE%E3%81%99%EF%BC%89/
 
個人的には今回の場合は、エクセルが使えるメモリー量を超えてメモリーを
使おうとしているのでは?と想像します。
 
コードが提示されていませんが(ここを提示すべき)、
100シート分の作業を繰り返す部分が気になります。
開いたり閉じたりの命令が矢継ぎ早に速すぎて、動作が命令に追い付かないのでは?
 
20回に1回くらい0.1秒くらい休憩を挟んでみて改善しないでしょうか?
 
http://officetanaka.net/excel/vba/tips/tips116.htm
 
あと、
><Copyや、直接値を移行するわけではなく、Variantを使った理由>
>私の勘違いかと思いますが
>Excelのメソッドよりも、配列(メモリ部分を使うこと)の方が何かと早い
>と、WEBで学んだからです。
>その考えを、無根拠に(検証なしに)受け入れていたので、
>なんでもかんでも配列で処理していました。
>細雪様のご指摘どおり、Copyメソッドを使った方が、
>数倍早く(3分くらいの処理が、1分で終わりました)
>私の「配列信仰」が覆される結果となりました。

これは、誤解ですね。
基本、高級言語でどんなに高速化しても、低級言語の処理には勝てないです。
いろいろな要素が絡んでくるので、一概には言えませんが、
基本、エクセルに任せられるところはエクセルに任せたほうが(=メソッド等の利用)、
処理は速いです。(数式を利用できるところは利用したほうが速い場合もある)
2次元配列に吐き出して有利なのは、たくさんのセルの値を、個別に加工する場合のみです。
個々にセルを読んだり書いたりする部分がかなり処理速度の負担になります。
セルの読み書きをまとめてできる場合はできるだけまとめて行うだけで、
かなり高速化できます。
 
 
 
 

回答
投稿日時: 17/12/30 15:10:52
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません
>基本、高級言語でどんなに高速化しても、低級言語の処理には勝てないです
この意味、よく分かりません。
 
 
また、メモリの話をさせていただきますが、
 
「Variant型変数」
何でも入るから、便利と思っているかもしれませんが、
処理の効率面から考えると、決して効率的であるとは言えないです。
 
常に、データ型を判断する処理が入るので、
その分処理時間が掛かります。また、メモリもたくさん使います。
データ型が決まっているのでしたら、できるだけ、合致したデータ型を使った方が早いです。

回答
投稿日時: 17/12/30 23:31:02
投稿者: かすみの

>100シート分の作業を繰り返す部分が気になります。
経験則ですが、私もこの部分気になります。
上記のようにウェイトを掛ける(適度に止める)か、
保存側のブックは1枚にしておいて、
「クリア→処理→名前を付けて保存」の繰り返しにするとか…
(完全に勘なので改善しない可能性もあり)
 
 
>>基本、高級言語でどんなに高速化しても、低級言語の処理には勝てないです
>この意味、よく分かりません。
本題ではないですが…。
・シート上のセルを操作 →別シートにコピー
・Variant/配列の値を操作→別シートにコピー
であれば、後者の方が速いよ、ということでは?
variant⇔型指定配列のことではないと思います。
 
また、すでに用意されているものは最適化されている可能性があるので、
今回のように、単に渡すだけであれば、既存のものが良い(場合が多い)かなと。
 
まぁ、結局やることによりますが、パフォーマンスに問題がない限りは、
よりシンプルで読みやすいほうで書きたいものです。

トピックに返信