VBA 巨大な表を配列に入れるとメモリはどうなるのかという心配について

以前このブログでも紹介したが、セル範囲と2次元配列は相互に変換できる。

thom.hateblo.jp

たとえばこんな風に。

Sub セルから配列へ()
    Dim Arr() As Variant
    Arr = Range("A1:Z100")
End Sub

Sub 配列からセルへ()
    Dim Arr(1 To 100, 1 To 26) As Variant
    Range("A1:Z100") = Arr()
End Sub

しかし、転記する表があまりに巨大だった場合はちょっと心配になる。
本当にメモリに入るんだろうか。大きな表を気軽に配列に入れて大丈夫だろうか。

今回はそんな不安を払拭する検証方法を思いついたので紹介する。

まずはタスクマネージャを起動し、プロセスタブを開く。
（タスクマネージャはCtrl+Shift+Escキーで起動できる）

CPUやメモリ使用量の順に並び替えされていると、プロセスの位置が安定しないので、名前順にして置こう。
そしてEXCELのメモリ使用量が見えるようにしておく。

次に、以下のコードを貼り付けて実行する。

Sub hoge()
    Dim Arr(100000, 100) As String * 10
    Do
        DoEvents
    Loop
End Sub

するとExcelのメモリ使用量が一気に増加する。

ちなみにExcel側の見た目は何も変化しないが、無限ループで延々実行されているので、止めるときは以下のリセットボタンを使用する。

このコードは、10万行×100列の二次元配列で、それぞれに10文字の固定長Stringとなっている。

実行中にパフォーマンスタブで確認したところ、全体のメモリ容量からしたらまだまだ余裕がある。

String * 20にしたら、メモリ使用量もだいたい倍になった。

そしてString * 30にしたら、Excelがクラッシュしてしまった。

これは単純に配列だけを使用した場合なので、実際にはその大きな表自体もメモリを食うことになる。

とはいえ、よほどのことが無い限り、100列10万行のデータを扱うということもないだろうし、100列すべてにおいて各セルの平均文字数10文字というのも多い方だと思う。
また、64ビット環境では各アプリが使用できるメモリ量も多くなっているはずなので、今後はこれよりもっと大きな表でも楽々扱えるようになるかもしれない。

ということで、あまり神経質に考えず、配列転記テクニックはどんどん使って良いという結論に至った。