マクロを組むようになると、いろいろな処理が自動でできるようになり、作り上げた喜びもどんどん大きくなっていきます。
それはそれでいいのですが、処理に時間がかかるようだと、使っている時ストレスを感じます。
したがって、『いかに実行速度をあげるか？』を考えてマクロを組むことが重要です。
以下で、その例を紹介します。
（変数の型が，IntegerよりLongの方が，処理速度が速いので，このページも変えました。）
下のマクロは、どちらもA1に1を10000回足していきます。
そして、処理が終了したら、メッセージを表示するようにしています。
ところが、このマクロを実行させてみると、明らかに「B」のマクロの方が処理速度が速いのです。
それは、どうしてなのでしょうか？
| Sub A() Dim n As Long Cells(1, 1) = "" For n = 1 To 10000 Cells(1, 1) = Cells(1, 1) + 1 Next n MsgBox "終了！" End Sub |
| Sub B() Dim n As Long Cells(1, 1) = "" Application.ScreenUpdating = False For n = 1 To 10000 Cells(1, 1) = Cells(1, 1) + 1 Next n Application.ScreenUpdating = True MsgBox "終了！" End Sub |
AとＢのマクロの違いは、 Applicationオブジェクトを使っているかどうかです。 ApplicationオブジェクトのScreenUpdatingで、 画面表示の更新をするかしないかを設定します。 （Falseは、しない。Trueは、する。） 更新をしないとどうなるか？ 処理が済んでも、ワークシートの表示は変わりません。 実は、パソコンが処理をするとき、画面表示にずいぶんと時間がかかっているのです。 そこで、画面表示の更新を、最後に1回だけ行って時間の短縮をしているわけです。 |
A1に１を10000回足していく処理を、少しやり方を変えてみます。
実際に実行させてみると、さきほどの「B」のマクロより「C」のマクロの方が処理速度が速くなります。
これは、なぜでしょうか？
| Sub B() Dim n As Long Cells(1, 1) = "" Application.ScreenUpdating = False For n = 1 To 10000 Cells(1, 1) = Cells(1, 1) + 1 Next n Application.ScreenUpdating = True MsgBox "終了！" End Sub |
| Sub C() Dim n As Long Dim 合計 As Long For n = 1 To 10000 合計 = 合計 + 1 Next MsgBox "終了！" End Sub |
ＢとＣのマクロの違いは、 Ｂはセルを使って処理をしているのに対して、 Ｃは変数を使って処理をしています。 つまり、メモリ（変数）で計算をした方が実行速度が速くなるのです。 |
Dim文で行う変数の宣言には、さまざまなデータ型があります。
以下に、代表的なものを書き出してみます。
特に考えもなく数値の変数を宣言するときは、整数型で行うのがいいでしょう。
大抵の場合には、対応できます。
整数を扱うときは，長整数型(Long)で宣言しましょう。
32bitのPCの場合，処理単位が32bit単位なので，32bitのLong型はそのまま処理されるのに対して，
16bitのInteger型，PC内部で一度32bitに変換してから処理を行います。
また，64bitのPCの場合も，Long型の方が処理が速いです。
さて、長整数型と文字列型の記憶領域を比較してみると、
長整数型が4バイトしか使わないのに対して、文字列型は最低で10バイト使います。
（最低というのは、文字が入ってない状態です。）
2.5倍の差があるので、処理を続けると、時間の差が出てきます。
したがって、数値に置き換えて処理できるのであれば、数値に置き換えた方が処理速度が速くなります。
| データ型 | 記憶領域 | 範囲 |
| ブール型(Boolean) | 2バイト | 真(True)または偽(False) |
| 整数型(Integer) | 2バイト | －３２，７６８～３２，７６７ |
| 長整数型(Long) | 4バイト | －2,147,483,648～2,147,486,647 |
| 文字列型(String)（可変長） | 10バイト＋文字列の長さ | ０～２ＧＢ |
| バリアント型(Variant)（数値の場合） | 16バイト | 倍精度不動小数点型の範囲と同じ |
| バリアント型(Variant)（文字列の場合） | 22バイト＋文字列の長さ | 可変長の文字列型の範囲と同じ |
様々な処理をするとき、条件によって処理を変えることはよくあります。
しかし、ここでも実行速度を速くする手段が残されています。
マクロの組み方を工夫して、処理するときの、判断する条件を少なくするのです。
下の例で、例えばＡ＝Ｂという条件一致があまり起こらないような場合、
（例２）の方が処理速度が速くなります。
というのは、（例１）では、ＩＦ文で５つの条件比較をしているのに対して、
（例２）では、１つめの条件が一致しなければ、あとの条件比較は省いて次の処理に進めるからです。
このことが度重なれば、その時間の差は大きくなってきます。
条件分岐をするときは、一番一致しにくい条件から個別に並べることが重要です。
| （例１） If A = B And B = C And C = D And D = E And E = F Then MsgBox "一致" End If |
| （例２） If A = B Then If B = C Then If C = D Then If D = E Then If E = F Then MsgBox "一致" End If End If End If End If End If |
今までにいろいろと書いてきましたが、
最終的には、マクロに処理をさせる仕事を減らすことが一番効果が大きいです。
３のところで、条件分岐を整理することを書きましたが、それもひとつなのです。
処理する仕事が少なければ実行速度が速くなるのは、当たり前のことです。
例えば、電話番号から兄弟関係を検索するケースを考えてみましょう。
電話番号が一致すれば、兄弟になりますね。
5人の電話番号を比較するとき、ただ順番に比較すると、
1人目が4人、2人目が3人、3人目が2人比較することになり、9回比較することになります。
しかし、兄弟がいるところだけ比較したらどうでしょうか？
処理する回数を減らすことができます。（＊１）
さらに、兄弟の数の分だけ一致があったら、処理を中断して次の処理に移ったらどうでしょう。
さらに、処理速度が速くなっていきます。（＊２）
このように、工夫して仕事を減らしてやることが、処理速度の向上につながっていくのです。
（おまけ）たぶん、比較する文字数も少ない方が、比較が速くできると思います。
| ＊１ | 兄弟がいるかどうかを、どうやって調べればいいのか？ これは、ワークシートで電話番号の横にCountIf文を入力します。 全体の中で同じ電話番号がいくつあるか数えればいいのです。 そして、2以上の場所だけ比較することになります。  |
| ＊２ | 上の画像の場合だと、①の処理で③のところで1つ見つかるので、それ以降の比較をやめて、 次の②の比較をしていくことになります。 |
関数などがワークシートにあると、マクロでセルに書き込みをしたとき自動で再計算が始まってしまいます。
これが、けっこう時間を食います。
そこで、マクロが処理をしている間だけ、自動再計算を止める方法があります。
余計なことをしないので、スピードが断然速くなります。
ワークシートに、関数や計算式が入っている場合は、効果的です。
 |
2列目には、SUM関数が入っており、1行目からその行までの合計を計算しています。 3列目には、 2列目の数値から1列目の数値を引く式が入っています。 そして、1列目は、マクロによって、1行目から1000行目まで、順番に数字を書き込んでいきます。 |
| Sub D() Dim R As Long For R = 1 To 1000 Cells(R, 1) = R Next R MsgBox "終了しました。" End Sub |
このマクロは、1列目に１～１０００までを書き込んでいくマクロです。 数字が書き込まれる度に関数や式が再計算をするので、ワークシート上にあるセルの値がリアルタイムで変わっていきます。 そのため、時間が少しかかります。 |
| Sub D() Dim R As Long Application.Calculation = xlCalculationManual ←① For R = 1 To 1000 Cells(R, 1) = R Next R Application.Calculation = xlCalculationAutomatic ←② MsgBox "終了しました。" End Sub |
このマクロも、1列目に１～１０００までを書き込んでいくマクロです。 先ほどのマクロと違うところは、①と②が入っていることです。 ①では、再計算を手動に設定しています。 そして、1列目に数字を書き込んでいる間は、再計算をしません。 ②では、処理が終わったので、再計算を自動に戻しました。 前のマクロとスピードを比較すると、こちらの方は一瞬で処理が終わります。 |
大量のデータをセルに書き込むとき，１つ１つのデータを順番にセルに書き込んでくと，非常に時間がかかります。
こいういうときには，配列を使って1回で書き込みましょう。
エクセルでは，セルにデータを書き込む処理に，けっこう時間がかかります。
そこで，セルに書き込む回数を減らすことで，処理時間を短縮することができるのです。
以下では，1000行×100列のセルに入っている1桁の数を，別のセルに書き込むマクロで実験しました。
※ここでは，速度計測結果のため，変数の宣言はInteger型のままにしてあります。
| Option Explicit Dim 配列 As Variant ←① Sub 乱数() ←② Dim R As Integer Dim C As Integer Randomize Range(Cells(1, 1), Cells(2000, 100)) = "" For R = 1 To 1000 For C = 1 To 100 Cells(R, C) = Int(Rnd() * 10) Next C Next R End Sub Sub 格納() ←③ 配列 = Range(Cells(1, 1), Cells(1000, 100)) End Sub |
※この部分は，実験するための準備です。 ①・・・書き込み処理だけの時間を測定したいので，あらかじめデータを格納しておくため，ここで宣言した。 Variant型で宣言すると，セルのデータを格納したときに，自動的に配列で格納してくれる。 ②・・・1000行×100列の，1桁乱数を準備するマクロ。 ③・・・②のデータを，前もって配列に格納しておくマクロ。これも，範囲を指定して，1回の処理で配列に格納しています。 |
| Sub 一個ずつ() Dim R As Integer Dim C As Integer For R = 1 To 1000 For C = 1 To 100 Cells(R + 1000, C) = 配列(R, C) ←① Next C Next R End Sub |
①・・・配列から１つずつデータを読み出して，書き込みます。 |
| Sub 一度に() Range(Cells(1001, 1), Cells(2000, 100)) = 配列 ←① End Sub |
①・・・配列を使って 1回でセルに書き込みます。 |
実験の結果は，次の通りです。それぞれ１０回測定し，その平均値を求めてみました。
これを見ると，配列を使った方が圧倒的に速いですね。
| 一個ずつ | 一度に | さて，この「配列を使って１回で書き込む」方法が，どんな場面に応用できるか？ その例を，紹介していきましょう。下のように，セルに○が記入されているとします。 未記入のところに「・」を書き込むとしたら，どうするか？ 詳しくは，こちらから。  |
||
| １回 | 2.201171875 | 0.15625 | ||
| ２回 | 2.16796875 | 0.125 | ||
| ３回 | 2.21484375 | 0.138671875 | ||
| ４回 | 2.16796875 | 0.125 | ||
| ５回 | 2.201171875 | 0.125 | ||
| ６回 | 2.18359375 | 0.125 | ||
| ７回 | 2.16796875 | 0.125 | ||
| ８回 | 2.18359375 | 0.125 | ||
| ９回 | 2.16796875 | 0.123046875 | ||
| １０回 | 2.16796875 | 0.142578125 | ||
| 平均 | 2.182421875 | 0.131054688 |
同じ処理をする場合でも，ワークシート関数を使った方が速くなります。
次のマクロは，ランダムに並んだ数値を小さい順番から並び替えをするソートマクロです。
考え方は，「選択ソート」と呼ばれています。
例えば，１０００個の数値があった場合，
まず，Ａ１：Ａ１０００の最大値を調べ，Ａ１０００と入れ替えます。
次に，Ａ１：Ａ９９９の最大値を調べ，Ａ９９９と入れ替えます。
これを繰り返すことで，小さい順番に並べ直します。
※ここでは，速度計測結果のため，変数の宣言はInteger型のままにしてあります。
| Sub 選択ソート1() Dim n As Integer Dim Mx As Integer Dim t As Single Dim rr As Integer Dim 最大値R As Integer t = Timer ←④ For n = 1000 To 1 Step -1 '---- 最大値を求める ---- Mx = 0 最大値R = 1 For rr = 1 To n If Cells(rr, 1) > Mx Then ←① 最大値R = rr ←② Mx = Cells(rr, 1) ←③ End If Next rr '---- 入れ替え ---- Cells(最大値R, 1) = Cells(n, 1) Cells(n, 1) = Mx Next n MsgBox Timer - t ←⑤ End Sub |
①ここで，変数に入れてある値と大小関係を比較します。 そして，セルの値の方が大きければ， ②で，その行位置を記憶し， ③で，それを最大値として記憶します。 いわゆる，普通に順番に比較して処理していく方法です。 ④Timer関数は，午前０時０分０秒からの経過時間を単精度浮動小数点数型の値で返します。 ⑤で，引き算をすることで，かかった時間を求めています。 |
| Sub 選択ソート2() Dim R As Integer Dim n As Integer Dim tmp As Integer Dim Mx As Integer Dim t As Single t = Timer For n = 1000 To 1 Step -1 Mx = Application.WorksheetFunction.Max(Range("a1:a" & n)) '最大値を求める ←① R = Application.WorksheetFunction.Match(Mx, Range("a1:a" & n), 0) '最大値の行を求める ←② '---- 入れ替え ---- Cells(R, 1) = Cells(n, 1) Cells(n, 1) = Mx Next n MsgBox Timer - t End Sub |
①ワークシート関数の，最大値を求めるＭＡＸ関数を使います。 マクロの中でワークシート関数を使う時は，最初に，Application.WorksheetFunction. をつけ，Rangeを使います。 ②ワークシート関数の，値に一致する位置を求めるMatch関数を使います。 どちらも範囲が変わるので，"a1:a" という文字列と，nという数値を結合して，範囲を作っています。 ここでは，順番に比較していく代わりに，関数を使って求めています。 |
| Sub 選択ソート3() Dim n As Integer Dim tmp As Integer Dim Mx As Integer Dim t As Single Dim rr As Integer Dim 最大値R As Integer Dim 数値 As Variant t = Timer 数値 = Range("a1:a1000") ←① For n = 1000 To 1 Step -1 '---- 最大値を求める ---- Mx = 数値(1, 1) 最大値R = 1 For rr = 1 To n If 数値(rr, 1) > Mx Then 最大値R = rr Mx = 数値(rr, 1) End If Next rr '---- 入れ替え ---- 数値(最大値R, 1) = 数値(n, 1) 数値(n, 1) = Mx Next n Range("a1:a1000") = 数値 ←② MsgBox Timer - t End Sub |
①ここで，Ａ１：Ａ１００までの数を，数値という配列に格納します。 その後，配列の中で処理をします。 この時に使っている処理の方法は，選択シート1で行っている，初歩的な方法です。 （普通に比較して，処理をしていく方法です。） ②処理が終わったら，②でセルに一気に書き込みます。 大きな違いは，変数の中で処理をすることです。 |
３つのマクロを紹介しました。どれも「選択ソート」という考え方を実現したマクロです。
さて，これをデータの数を変えて，時間を計ってみると，下のような結果が得られました。
| データ数： １００個 （単位：秒） | 備考 | |||
| 方法 | 選択ソート３ （変数） |
選択ソート２ （関数） |
選択ソート１ （普通） |
データ数が１００個では， 関数で処理すると，普通に処理するよりも 約１．５倍の速度が出ています。 |
| １回目 | 0 | 0.03125 | 0.046875 | |
| ２回目 | 0 | 0.015625 | 0.03125 | |
| ３回目 | 0 | 0.03125 | 0.046875 | |
| ４回目 | 0 | 0.015625 | 0.03125 | |
| ５回目 | 0 | 0.01171875 | 0.03125 | |
| 平均 | 0 | 0.02109375 | 0.0375 | |
次に，データ数を１０倍にしてみましょう。
| データ数： １０００個 （単位：秒） | 備考 | |||
| 方法 | 選択ソート３ （変数） |
選択ソート２ （関数） |
選択ソート１ （普通） |
データ数が１０００個では， 関数の場合，普通に処理するよりも 約１７倍の速度が出ています。 |
| １回目 | 0.046875 | 0.109375 | 1.796875 | |
| ２回目 | 0.03125 | 0.09375 | 1.79296875 | |
| ３回目 | 0.02734375 | 0.109375 | 1.79296875 | |
| ４回目 | 0.02734375 | 0.09375 | 1.77734375 | |
| ５回目 | 0.03125 | 0.09375 | 1.796875 | |
| 平均 | 0.0328125 | 0.1 | 1.79140625 | |
さらに，データ数を１０倍にしてみましょう。
| データ数： １００００個 （単位：秒） | 備考 | |||
| 方法 | 選択ソート３ （変数） |
選択ソート２ （関数） |
選択ソート１ （普通） |
データ数が１００００個になると， 関数の場合， 約１００倍の速度が出ています。 また，おもしろいことに， 変数より関数の方が速くなっています。 |
| １回目 | 3.41796875 | 1.57421875 | 172.4140625 | |
| ２回目 | 3.41796875 | 1.578125 | 171.0703125 | |
| ３回目 | 3.4140625 | 1.58984375 | 170.8515625 | |
| ４回目 | 3.43359375 | 1.57421875 | 171.0234375 | |
| ５回目 | 3.4296875 | 1.59375 | 170.7265625 | |
| 平均 | 3.42265625 | 1.58203125 | 171.2171875 | |
さらに，１０倍にするとどうでしょうか？
| データ数： １０００００個 （単位：秒） | 備考 | |||
| 方法 | 選択ソート３ （変数） |
選択ソート２ （関数） |
選択ソート１ （普通） |
データ数が１０００００個になると， 関数の方が，さらに速くなり， 変数の約５倍ほどの速度に なりました。 |
| １回目 | 397.1796875 | 82.0703125 | 上記の結果から 膨大な時間が かかると 思われるため， 実験してません。 |
|
| ２回目 | 397.2421875 | 82.0703125 | ||
| ３回目 | 397.109375 | 81.9921875 | ||
| ４回目 | 398.3203125 | 82.03125 | ||
| ５回目 | 397.0625 | 82.0078125 | ||
| 平均 | 397.3828125 | 82.034375 | ||
この結果を見ると，関数を使った方が，普通に処理するより，処理時間が早くなります。
また，興味深いのは，データ数によって，一番速い処理が関数であったり，変数であったり
することですね。使い分けるのは難しいですが，知っておくと何かの役に立つかもしれません。