メモリの消費量について

質問

エクセルのマクロを使っているのですが、例えば100×2 という計算をしようとした場合
　range("A1") = 100
　range("B1") = 2
としたとき range("C3") に答えを出すときの計算式は下記(1)、(2)ではどちらの方が効率がいいのでしょうか？
(1)
　range("C3") = range("A1") * range("B1")
(2)
　dim a as byte, b as byte
　　　a = range("A1")
　　　b = range("B1")
　　　range("C3") = a * b

　変数はむやみに使用するとメモリの無駄使いになると聞いたのですが(1)では変数を使用していない分、このような計算を連続で行うと処理が早くなるのでしょうか？
　変数を使った方がモジュールは書きやすいのですが、マクロを実行したときの処理速度等を考えると変数は極力使用しない方がいいのでしょうか？

回答No.4

noname#221368 · ベストアンサー

＞データ型が同じ場合、宣言セクションでは変数よりできる限り定数を使用した方が処理速度は早くなる？
　以下、Option Explicit 以降は、同一モジュール内にあるとして、書きます。ここでモジュールとは、標準モジュール，ユーザーフォーム，ワークシートなどを全部含みます。
　まずモジュールの Declare部での宣言（補足のような場合）と、プロシージャ内での宣言（例：sub パターン1() 内で、Dim Ａ as Byte とする場合）の違いです。
　Declare部で宣言された変数（定数）は、モジュールレベル変数（定数）と言われ、メモリのヒープ領域に確保されます。一方、プロシージャ内で宣言された変数（定数は不可）は、スタック領域に確保されます。
　ヒープとスタックの違いは、その生存期間にあり、ヒープ変数はApplicationの終了まで残ります（Excel終了時まで）。スタック変数は、プロシージャの開始から終了までです。
　よってスタック変数を多用すると、一時的にメモリを無駄使いする可能性があり、またプロシージャの開始・終了の度に、スタック変数の領域確保，値セット，破棄の動作が入るので（オーバーヘッドがかかるので）、動作も多少無駄になります。これが最初のご質問の意図ではないかと思います。
　しかし現在では、ほとんど気にする必要がありません。逆に現在では、ヒープ変数（モジュールレベル変数）の多用は、プログラムの可読性（読み易さ）を下げるという理由から、スタック変数（プロシージャレベルの変数）の使用が推奨されています。
　補足にあるモジュールレベル変数と定数との違いですが、この場合はどちらもヒープ領域にあるので、ReadOnly として確保されたかどうかの違いであり、動作にはほとんど影響を与えません。

＞作業を100回、200回と繰り返すとやはり処理速度に違いが生じる？
　別の原因と思われます。まず、Excelを表示したまま計算を行わせているとしたら、画面更新にメモリが消費され、仮想メモリを使い出して（ディスクとのやりとりが起こって）、次第に遅くなるという事が考えられます。最もメモリを消費するのは、じつは画面への表示です。これには、
　　・Application.Visible = False
　　・Application.ScreenUpdated = False
などで対処できます。ヘルプで調べてみて下さい（使用上の注意も含めて）。
　次に考えられるのは、Cellは直接書き込んでいる事です。以下のコードを試してみて下さい。

Public Sub test1()

　A = 1
　B = 2

　For i = 1 To 20000
　　Cells(i, A) = i * 1
　　Cells(i, B) = i * 2
　Next i

End Sub

Public Sub test2()

　A = 3
　B = 4
　Dim Vales(1 To 20000, 3 To 4) As Long

　For i = 1 To 20000
　　Vales(i, A) = i * 1
　　Vales(i, B) = i * 2
　Next i

　Range(Cells(1, 3), Cells(20000, 4)).Value = Vales
End Sub

　test2については、
　　Dim Values as Variant
　　Values = Range(範囲).Value
で逆も出来ます。

お礼

Application.ScreenUpdated = False　で画面更新を非表示にはしていたのですが、それでも処理速度が遅くて困っていました。
　test2の方がtest1に比べて圧倒的に速いですね。参考書を見直したところ多次元配列の項目で「セルにデータを入力したり、セルからデータを取り出したりするには、非常に時間がかかる作業です。」と書いてありました。Cellsではなく配列変数に書き直してみます。詳しいご解説どうもありがとうございました！

回答No.3

noname#79209

直接の回答ではありませんが、
　　　a = range("A1")
　　　b = range("B1")
　　　range("C3") = a * b
というようなことにVBAを使うべきではありません。
A1とB1にデータをセットする部分はまだ良いとしても
VBAで計算させて結果をC3にセットは好ましくないのでは？
C3に「=A1*B1」とすべきだと思います。
説明のために簡単な計算式にしているのでしょうが、
計算式が複雑で、ワークシートの計算式で対応できないなら、
VBAはユーザー定義関数にしておき、C3には
「ユーザー定義関数名(A1,B1)」としたほうがよいと思います。

補足

ご回答ありがとうございます。必ずしも計算結果をC3にセットする訳ではないので、御察しのとおり簡単な計算式で質問させて頂きました。「ユーザー定義関数名」を代入したほうが処理速度が速くなるなら一度検討してみます。

回答No.2

noname#221368

＞変数はむやみに使用するとメモリの無駄使いになる
　これは本当ですが、「今は昔」の話です。このような事が問題になっていたのは、メモリが 640KB（いいですか、キロですよ）しかなく、しかもそれを 10分割して使わざる得なかったような、昔々の話です。
　現在では、とびきりの大規模計算でも行わない限り、数値計算に関するメモリの心配は不要です。むしろ実行速度の方を気にします。

＞range("C3") = range("A1") * range("B1") と range("C3") = a * b
　#1さんが仰っているように、range("A1")などにも隠れた変数（組み込みの変数）が割り当てられていますので、a，bを宣言しても、その点では同列です。
　速度ですが、Range型のrange("A1")などと、Byte型のa，bを比較すれば、a，bの方が高速に動作します。理由は、Range型のObjectには、Excelの全ライブラリーがいっしょにくっついて動作するような事になるからです。ですがこの差は、1000個以上のCellの読み書きを行って、やっと体感できる程度です。
　もう一言いえば、Byte型よりIntegerの方が速く動きます。理由は、Integer型はCPUのレジスターが直接扱える型だからです。LongやBiteを使うと、多倍長や半倍長の変換が入り、多少遅くなりますが、これも普通は体感できるものではありません。

　大量のCellの読み書きが必要になったら、またご質問下さい。

補足

ご回答ありがとうございます。マクロの繰り返し作業を長く使用していると止まるのではないかというほど処理速度が遅くるので、少しでも処理速度を早くしたくて質問させて頂きました。Byte型は2バイト、Integer型は4バイトのメモリをそれぞれ使用することから極力Byte型を使うようにしていたのですが、メモリの使用量は処理速度にはあまり影響しないみたいですね。
　新たな質問ですが、データ型が同じ場合、宣言セクションでは変数よりできる限り定数を使用した方が処理速度は早くなるのでしょうか？
　例えば以下のような場合です
Option Explicit
　Public i as Integer
　Public A as byte, B as byte
　Const C as byte = 1, D as byte = 2
------------
　sub パターン1()
　　変数の場合’
　　　A = 1
　　　B = 2
　　　for i = 1 to 5000
　　　　cells(i,A) = i * 1
　　　　cells(i,B) = i * 2
　　　next
　end sub
------------
sub パターン2()
　　定数の場合’
　　　for i = 1 to 5000
　　　　cells(i,C) = i * 1
　　　　cells(i,D) = i * 2
　　　next
　end sub
-----------
　なおモジュールは多数あり、全てのモジュールでA,B,Dを使っており、C,Dは全てのモジュールで定数として宣言しています。
　このような作業を100回、200回と繰り返すとやはり処理速度に違いが生じるのでしょうか？

回答No.1

gatyan

変数に代入しなくても、内部的に変数を使っているはずなので、あまり変わらない気がします

変数に入れない場合はその都度、自動で変数の生成・開放が行われるはずなので、後でも使う事がわかっているなら、変数に入れておいて使う方が多少は早くなると思います
特に、スピードを考えるなら、ループ内で変化しないオブジェクトは変数に入れて使う方が早くなります（参照する他のワークシートオブジェクトとか）

お礼

ご回答ありがとうございます。連続で使用したときにどうしても処理速度が遅ってくるので、ループで使用している箇所を見直して変数に入れられるところは入れてみます。