Excelのメモリ(配列)の上限は2Gではないのか

質問

こんにちは、
現在ExcelのVBAで大量に配列を必要なマクロを作成しています。

その為、計算量が増えるとどうしても、
メモリ不足というエラーが発生してしまい困っています。

そこで、現在使っているExcelがどれだけの配列とメモリを使用できるのか
下記コードを使用してテストしてみました。

そうすると、下記のような値の時メモリ不足というエラーが発生してマクロが終了しました
(下図参照)
・メモリ(プライベートワーキングセット) 　：1249716 K (約1G？)
・String型配列数(各要素"01,02,03,04,05,06")　：約28000000(2800万個)

私の知識では、32bitアプリケーションのメモリの上限は2Gだと聞いています。
ですが、実際にはその半分しか使われていません。
そこで質問となるのですが

・32bitアプリケーションの上限が2Gと言われているのはプラベートワーキングセットの値のことではないのか？

・32bit版Excelを使用して、これ以上のメモリ(配列)を使用することは可能か

・可能であれば、その方法はどんな方法か？

以上のことについてお聞きしたいと思っております。
上のどれか一つでもいいです。知っていることがあれば教えてください。

補足となりますが、テストしたPCの簡単な環境を下に記載して置きます。
どのPCでも上記結果とほぼ変わりはありませんでした。

PC1
Windows7 32bit　メモリ　4G Excel2013(32bit)

PC2
Windows7 64bit　メモリ 8G Excel2010(32bit)

以下は使用したプログラムコードです

---------------------------------------------------------------

Public Sub 配列上限取得計算()
On Error GoTo ErrEnd
Dim i As Long
Const kankaku As Long = 1000000

Dim Moji As String
Moji = "01,02,03,04"

Dim ans() As String
ReDim ans(1 To kankaku) As String
i = 1
Do
If i Mod kankaku = 0 Then
ReDim Preserve ans(1 To i + kankaku) As String
End If

ans(i) = Moji
i = i + 1
Loop
Erase ans
Exit Sub
ErrEnd:
MsgBox Err.Description & vbCrLf & "これ以上の配列を設定できません。" & vbCrLf & "上限は" & i & "です。"
Erase ans
Err.Clear

End Sub

回答No.3

WindFaller · ベストアンサー

#2の回答者です。
>何百万どころか数千万単位で必要です。
>やっていることは、50個あるサンプルで複数の実験を行います。

そういう理由で、配列を使うという所に疑問を感じます。

>50個あるうち6個の組み合わせを考えた場合、
>15890700(1589万700個)通りの文字列を一度配列に格納し、それから各条件に合うかどうか各要素ごとに確認していく作業をしています

時々、ここの掲示板でも、年に一度ぐらい、似たような話は出会いますが、果たして一覧を配列の中にすべて収める必要があるのでしょうか？その都度、組み合わせていって、必要なものを取り出せば済む話だと思うのです。まさか、出来上がってみなければ分からないというような話ではないと思います。もちろん、人間の判断を要するものだとしても、1500万件もの量を、個人でこなしきれるものではないとは思います。

何十年と、こうした掲示板を見ている私でも、組み合わせをすべて配列に入れるという話は、初めてです。もちろん、#1の人の書いた、配列の分散という方法もあるはずだとは思うのですが、今度は、本体のExcel側が果たして要求に応えるか分かりません。

ただし、Excelのアドインの"Solver"の開発元の会社のツールで、組み合わせを解決するという話は聞いたことがあります。ただ、10数万円もするアプリですので、容易には手が出ません。その代わり、VB6時代で、いくつかのアルゴリズムは公開されているはずです。

#1のお礼欄
>64bitOfficeはまだ一般的でなく、また、32bitOfficeと同時にインストールはできなかったと記憶しております。

64bit Officeは、現在の最新バージョン(2013)でも、Microsoft 側は、使用をお勧めしていません。

お礼

いわれてみればそうでした。
今まで、条件が増えていくに付き、プログラムを別々に追加していったので、気づけませんでした。

よくよく考えてみると、組み合わせを列挙する時に、それぞれの条件に合わないものをはじけばよかったんですね・・・

まぁ、プログラムをかなり書き直さないといけないですが、ちゃんと考えないでプログラムを追加していった自分が悪かったと思って諦めます。

回答No.4

RandenSai

回答No.1です。

私も回答No.3で提案されている、「その都度、組み合わせていって、必要なものを取り出せば済む話」ということに完全同意します。

今の処理では全ての組み合わせを生成して配列に入れ、後から順番に比較しているようですが、なんでそんな非効率なことをするのか、組み合わせの一つを生成したらその場で比較し、一致しなければ次という流れにすれば済むではないかと思えてなりません。

比較が一回ではないとしても、ループで合致する情報を探すのであれば、比較元が配列の中身だろうと都度生成した物だろうと、速度的には気にするほどの違いはないはずです。二回目以降の比較は一回目で見つけた物だけ相手にしているのなら、その一回目で見つけたものだけを入れる配列を用意すれば良いでしょう。

どうしても全部を配列に入れて処理することが避けられないなら、その多量のデータを前処理する部分だけをマクロではなくてVBの別ソフトに追い出し、Excelで扱える大きさにしてから処理することも検討されてはいかがでしょうか。VBAからVBの移植だったら難しくないし、それにVBなら純粋にメモリ限界まで配列が作れます。x64プロジェクトとすれば2GBの制約も取り払われます。

お礼

>>組み合わせの一つを生成したらその場で比較し、一致しなければ次という流れにすれば済むではないかと思えてなりません。

途中でとある条件の時に、出来上がった配列と別のデータ(これれも配列)の同士を行うようにしてたんですが、よくよく考えると、それぞれの要素ごとに、別のデータとの比較をすればよかったんですね。

うかつでした。

>>その多量のデータを前処理する部分だけをマクロではなくてVBの別ソフトに追い出し、Excelで扱える大きさにしてから処理することも検討されてはいかがでしょうか。

なるほどVBで作成するという手もありましたか。
その方法も考えてみます。
ありがとうございました。

回答No.2

WindFaller

こんにちは。

String変数のメモリの理論上の上限が、2GBであるけれども、Microsoft 側では、配列の上限は、使用する保管メモリのバイト数ではなく、要素の合計数と言っています。（これは、過去の話だったかもしれません。）

http://msdn.microsoft.com/ja-jp/library/b388cb5s(v=vs.90).aspx

その他では、「オートメーションを利用して、Excelに渡す配列の制限」（これも昔の話）
http://support.microsoft.com/kb/177991/ja

検索してみると、かなりの数の書き込みで、同じテーマの内容が出てきますが、明確な回答を見れません。
いろんな人が、あれこれ言っていますが、そこから、Microsoft で、このような文章が出てきました。

http://msdn.microsoft.com/en-us/library/b388cb5s%28VS.80%29.aspx

Maximum size

The article is a bit vague about the maximum size of the array. I have 32-bit machine with 3.5GB of memory and

32bit マシーンで3.5GBのメモリを搭載しているが、

Dim arrDailyMatrix() As Double
ReDim arrDailyMatrix(1 To 233, 1 To 10, 0 To 10000)

メモリの限界を越えたと出てきました。メモリの限界の正確の大きさは知ることかできませんが、3次元での最大のサイズの調整は可能だという話でしかありませんが、この数字は、各掲示板によっても違うようです。人によっては、500MBを越えないとも書いているようです。

他の掲示板で書かれている「201185*151」の要素数に関しては、そこまでは届かないようです。

Moji="01,02,03,04",　11 byte*2(Unicode) =22 byte　（ただし、String型が持つ本来のメモリ数は入れていません。）

この文字列に対して、ご質問に書かれている要素数の限界値と、この要素数とも、だいたいの数字は、合っているようです。ただし、アプリ内で使用されているメモリにも影響を受けるようです。

さて、現実の問題として、理論値は別として、果たして、何百万もの要素数を持った配列を作る必要性があるのか、というところに行き着いてしまうと思います。格納は簡単には出来たけれども、その後の処理で、シーケンシャルで扱えば、かなりの手間を掛けてしまうことにもなります。このままでは、それを、どう扱うかという実際の作業を考えなければ、単なる理論値だけの話に終わってしまうと思います。

お礼

>>検索してみると、かなりの数の書き込みで、同じテーマの内容が出てきますが、明確な回答を見れません。

そうなんですよね。私も調べてみましたが、解決策はあまり見つかっていません。唯一3Gスイッチというのが使えそうであるんで試してみ見る価値ありそうですが。

>>さて、現実の問題として、理論値は別として、果たして、何百万もの要素数を持った配列を作る必要性があるのか

何百万どころか数千万単位で必要です。
やっていることは、50個あるサンプルで複数の実験を行います。
1つのサンプルにつき複数の実験値（データ）があるのですが
50個あるサンプルから数個の組み合わせを取り、さまざまな条件によってその組みわせを絞っていくというものですが、

50個あるうち6個の組み合わせを考えた場合、
15890700(1589万700個)通りの文字列を一度配列に格納し、それから各条件に合うかどうか各要素ごとに確認していく作業をしています。

まだ、メモリを削減できそうな予知はあるのですが、プログラムを抜本から書き換えないといけないところが多く、工数がかかり大変です。なので、なんとか上限を増やせないか試行錯誤していました。

配列は要素を削除するなんてことが上手くできないので、条件に合わない要素を削るときに、いったん別の配列にコピーしてしまっているのも悪いかもしれません。

いっそのこと配列をコレクションに書き換えた方がいいのでしょうか・・

回答No.1

RandenSai

32bitアプリのメモリ上限である2GBというのは、そのアプリ全体での話であり、もっと言うとWin32プロセスが利用できる限界値でもあります。つまり配列だけで2GB使ってしまったら、Excel本体やVBAを実行するためのメモリがなくなってしまいます。それでは困るから、通常この程度残しておけば大丈夫であろう…というメモリ残量を下回りそうな事態になった時、メモリ不足の警告を出します。

仮想記憶があるだろうと思われるかも知れませんが、仮想記憶で対応できるのは、他にも2GB要求するプロセスが登場したような時です。この時には、仮想記憶が使っていない方のメモリをディスクにページアウトして、メモリを工面してくれます。しかしそれはあくまで2GBの要求が複数来ても、順番に割り当てて対応できるというだけであって、1プロセス＝2GBという数字は変わりません。4GBの物理メモリ空間で、2GBのユーザーメモリ空間を3つ以上持たせることを可能にする仕組みです。

なので2GBで足りない仕事をさせるのなら、32bitアプリでは無理で、64bitアプリを持ってくる必要があります。ただし、アプリが利用可能なメモリ容量の限界と、実際にアプリ内でユーザーに開放しているメモリ容量は全く個別の問題であって、アプリの実装次第で変わってしまいます。なのでもしかしたら、今は巨大配列一個でだめだけど、それが10個作れるようになった、でも一個の大きさは変わらない、みたいな可能性もあります。

お礼

なるほど。確かに配列だけで2Gいってしまったら、ソフト自体が動かなくなる可能性もありますからね。

でも、空き容量がまだたくさんあるのなら、それを使ってほしい気もします。(まだ1Gほど余裕あるのに・・・)

>>なので2GBで足りない仕事をさせるのなら、32bitアプリでは無理で、64bitアプリを持ってくる必要があります。

64bitOfficeはまだ一般的でなく、また、32bitOfficeと同時にインストールはできなかったと記憶しております。

本当は64bit版インストールできればそれがいいのでしょうが、実際にマクロを使う人はエクセルにあまり慣れてない人なので、あまり推奨はできません・・・

>>アプリが利用可能なメモリ容量の限界と、実際にアプリ内でユーザーに開放しているメモリ容量は全く個別の問題で・・・

そうなのですか。ということはユーザーが配列として使えるのは1GB分ぐらいということですね・・・