実行速度を速くしよう。

マクロを組むようになると、いろいろな処理が自動でできるようになり、作り上げた喜びもどんどん大きくなっていきます。
それはそれでいいのですが、処理に時間がかかるようだと、使っている時ストレスを感じます。
したがって、『いかに実行速度をあげるか?』を考えてマクロを組むことが重要です。
以下で、その例を紹介します。

(変数の型が,IntegerよりLongの方が,処理速度が速いので,このページも変えました。)

1.画面表示の更新をOFFにする。

 下のマクロは、どちらも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とBのマクロの違いは、
Applicationオブジェクトを使っているかどうかです。

ApplicationオブジェクトのScreenUpdatingで、
画面表示の更新をするかしないかを設定します。
(Falseは、しない。Trueは、する。)

更新をしないとどうなるか?
処理が済んでも、ワークシートの表示は変わりません。

実は、パソコンが処理をするとき、画面表示にずいぶんと時間がかかっているのです。
そこで、画面表示の更新を、最後に1回だけ行って時間の短縮をしているわけです。

2.変数で処理をする。

 A1に1を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 

BとCのマクロの違いは、
Bはセルを使って処理をしているのに対して、
Cは変数を使って処理をしています。

つまり、メモリ(変数)で計算をした方が実行速度が速くなるのです。

3.文字よりも数値を扱う。

 Dim文で行う変数の宣言には、さまざまなデータ型があります。
 以下に、代表的なものを書き出してみます。
 特に考えもなく数値の変数を宣言するときは、整数型で行うのがいいでしょう。
 大抵の場合には、対応できます。

 整数を扱うときは,長整数型(Long)で宣言しましょう。
 32bitのPCの場合,処理単位が32bit単位なので,32bitのLong型はそのまま処理されるのに対して,
 16bitのInteger型,PC内部で一度32bitに変換してから処理を行います。
 また,64bitのPCの場合も,Long型の方が処理が速いです。
 
 さて、長整数型と文字列型の記憶領域を比較してみると、
 長整数型が4バイトしか使わないのに対して、文字列型は最低で10バイト使います。
 (最低というのは、文字が入ってない状態です。)
 2.5倍の差があるので、処理を続けると、時間の差が出てきます。
 したがって、数値に置き換えて処理できるのであれば、数値に置き換えた方が処理速度が速くなります。

データ型  記憶領域  範囲 
ブール型(Boolean)  2バイト 真(True)または偽(False)
整数型(Integer) 2バイト −32,768〜32,767
長整数型(Long)  4バイト −2,147,483,648〜2,147,486,647
文字列型(String)(可変長)  10バイト+文字列の長さ  0〜2GB 
バリアント型(Variant)(数値の場合) 16バイト 倍精度不動小数点型の範囲と同じ 
バリアント型(Variant)(文字列の場合)  22バイト+文字列の長さ 可変長の文字列型の範囲と同じ 

4.条件分岐を整理する。(IF文、Select文)

 様々な処理をするとき、条件によって処理を変えることはよくあります。
 しかし、ここでも実行速度を速くする手段が残されています。
 マクロの組み方を工夫して、処理するときの、判断する条件を少なくするのです。

 下の例で、例えばA=Bという条件一致があまり起こらないような場合、
 (例2)の方が処理速度が速くなります。
 というのは、(例1)では、IF文で5つの条件比較をしているのに対して、
 (例2)では、1つめの条件が一致しなければ、あとの条件比較は省いて次の処理に進めるからです。
 このことが度重なれば、その時間の差は大きくなってきます。

 条件分岐をするときは、一番一致しにくい条件から個別に並べることが重要です。

(例1)
If A = B And B = C And C = D And D = E And E = F Then
  MsgBox "一致"
End If
(例2)
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.マクロに処理させる仕事を減らす。

 今までにいろいろと書いてきましたが、
 最終的には、マクロに処理をさせる仕事を減らすことが一番効果が大きいです。
 3のところで、条件分岐を整理することを書きましたが、それもひとつなのです。
 処理する仕事が少なければ実行速度が速くなるのは、当たり前のことです。

 例えば、電話番号から兄弟関係を検索するケースを考えてみましょう。
 電話番号が一致すれば、兄弟になりますね。
 
 5人の電話番号を比較するとき、ただ順番に比較すると、
 1人目が4人、2人目が3人、3人目が2人比較することになり、9回比較することになります。
 しかし、兄弟がいるところだけ比較したらどうでしょうか?
 処理する回数を減らすことができます。(*1
 さらに、兄弟の数の分だけ一致があったら、処理を中断して次の処理に移ったらどうでしょう。
 さらに、処理速度が速くなっていきます。(*2

 このように、工夫して仕事を減らしてやることが、処理速度の向上につながっていくのです。

 (おまけ)たぶん、比較する文字数も少ない方が、比較が速くできると思います。

*1  兄弟がいるかどうかを、どうやって調べればいいのか?
これは、ワークシートで電話番号の横にCountIf文を入力します。
全体の中で同じ電話番号がいくつあるか数えればいいのです。
そして、2以上の場所だけ比較することになります。

 
*2  上の画像の場合だと、@の処理でBのところで1つ見つかるので、それ以降の比較をやめて、
次のAの比較をしていくことになります。

6.自動再計算を止める。

 関数などがワークシートにあると、マクロでセルに書き込みをしたとき自動で再計算が始まってしまいます。
 これが、けっこう時間を食います。
 そこで、マクロが処理をしている間だけ、自動再計算を止める方法があります。
 余計なことをしないので、スピードが断然速くなります。
 ワークシートに、関数や計算式が入っている場合は、効果的です。

  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列目に1〜1000までを書き込んでいくマクロです。

数字が書き込まれる度に関数や式が再計算をするので、ワークシート上にあるセルの値がリアルタイムで変わっていきます。

そのため、時間が少しかかります。
Sub D()

  Dim R As Long

  Application.Calculation = xlCalculationManual ←@

  For R = 1 To 1000
    Cells(R, 1) = R
  Next R

  Application.Calculation = xlCalculationAutomatic ←A

  MsgBox "終了しました。"

End Sub 
このマクロも、1列目に1〜1000までを書き込んでいくマクロです。
先ほどのマクロと違うところは、@とAが入っていることです。

@では、再計算を手動に設定しています。
そして、1列目に数字を書き込んでいる間は、再計算をしません。
Aでは、処理が終わったので、再計算を自動に戻しました。

前のマクロとスピードを比較すると、こちらの方は一瞬で処理が終わります。 

7.配列を使って,1回で全データを書き込む。

 大量のデータをセルに書き込むとき,1つ1つのデータを順番にセルに書き込んでくと,非常に時間がかかります。
 こいういうときには,配列を使って1回で書き込みましょう。
 エクセルでは,セルにデータを書き込む処理に,けっこう時間がかかります。
 そこで,セルに書き込む回数を減らすことで,処理時間を短縮することができるのです。
 以下では,1000行×100列のセルに入っている1桁の数を,別のセルに書き込むマクロで実験しました。
 ※ここでは,速度計測結果のため,変数の宣言はInteger型のままにしてあります。

Option Explicit
  Dim 配列 As Variant ←@


Sub 乱数() ←A

  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 格納() ←B

 配列 = Range(Cells(1, 1), Cells(1000, 100))

End Sub
※この部分は,実験するための準備です。


@・・・書き込み処理だけの時間を測定したいので,あらかじめデータを格納しておくため,ここで宣言した。
Variant型で宣言すると,セルのデータを格納したときに,自動的に配列で格納してくれる。

A・・・1000行×100列の,1桁乱数を準備するマクロ。

B・・・Aのデータを,前もって配列に格納しておくマクロ。これも,範囲を指定して,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 
@・・・配列から1つずつデータを読み出して,書き込みます。
Sub 一度に()

  Range(Cells(1001, 1), Cells(2000, 100)) = 配列 ←@

End Sub 
@・・・配列を使って 1回でセルに書き込みます。

実験の結果は,次の通りです。それぞれ10回測定し,その平均値を求めてみました。
これを見ると,配列を使った方が圧倒的に速いですね。

一個ずつ 一度に             さて,この「配列を使って1回で書き込む」方法が,どんな場面に応用できるか?
その例を,紹介していきましょう。下のように,セルに○が記入されているとします。
未記入のところに「・」を書き込むとしたら,どうするか? 詳しくは,こちらから。

          
1回 2.201171875 0.15625
2回 2.16796875 0.125
3回 2.21484375 0.138671875
4回 2.16796875 0.125
5回 2.201171875 0.125
6回 2.18359375 0.125
7回 2.16796875 0.125
8回 2.18359375 0.125
9回 2.16796875 0.123046875
10回 2.16796875 0.142578125
平均 2.182421875 0.131054688

8.ワークシート関数を使う

同じ処理をする場合でも,ワークシート関数を使った方が速くなります。

次のマクロは,ランダムに並んだ数値を小さい順番から並び替えをするソートマクロです。
考え方は,「選択ソート」と呼ばれています。

例えば,1000個の数値があった場合,
まず,A1:A1000の最大値を調べ,A1000と入れ替えます。
次に,A1:A999の最大値を調べ,A999と入れ替えます。
これを繰り返すことで,小さい順番に並べ直します。
※ここでは,速度計測結果のため,変数の宣言は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 ←C
  For n = 1000 To 1 Step -1
    '---- 最大値を求める ----
    Mx = 0
    最大値R = 1
    For rr = 1 To n
      If Cells(rr, 1) > Mx Then ←@
        最大値R = rr ←A
        Mx = Cells(rr, 1) ←B
      End If
    Next rr

    '---- 入れ替え ----
    Cells(最大値R, 1) = Cells(n, 1)
    Cells(n, 1) = Mx
  Next n

  MsgBox Timer - t  ←D

End Sub
@ここで,変数に入れてある値と大小関係を比較します。

そして,セルの値の方が大きければ,
Aで,その行位置を記憶し,
Bで,それを最大値として記憶します。


いわゆる,普通に順番に比較して処理していく方法です。



CTimer関数は,午前0時0分0秒からの経過時間を単精度浮動小数点数型の値で返します。
Dで,引き算をすることで,かかった時間を求めています。
 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) '最大値の行を求める ←A

    '---- 入れ替え ----
    Cells(R, 1) = Cells(n, 1)
    Cells(n, 1) = Mx
  Next n

  MsgBox Timer - t

End Sub
@ワークシート関数の,最大値を求めるMAX関数を使います。
マクロの中でワークシート関数を使う時は,最初に,Application.WorksheetFunction. をつけ,Rangeを使います。

Aワークシート関数の,値に一致する位置を求める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") = 数値 ←A

  MsgBox Timer - t

End Sub
@ここで,A1:A100までの数を,数値という配列に格納します。

その後,配列の中で処理をします。
この時に使っている処理の方法は,選択シート1で行っている,初歩的な方法です。
(普通に比較して,処理をしていく方法です。)


A処理が終わったら,Aでセルに一気に書き込みます。



大きな違いは,変数の中で処理をすることです。

3つのマクロを紹介しました。どれも「選択ソート」という考え方を実現したマクロです。
さて,これをデータの数を変えて,時間を計ってみると,下のような結果が得られました。

      データ数: 100個 (単位:秒)  備考
方法 選択ソート3
(変数)
選択ソート2
(関数)
選択ソート1
(普通)
データ数が100個では,
関数で処理すると,普通に処理するよりも
約1.5倍の速度が出ています。
1回目 0 0.03125 0.046875
2回目 0 0.015625 0.03125
3回目 0 0.03125 0.046875
4回目 0 0.015625 0.03125
5回目 0 0.01171875 0.03125
平均 0 0.02109375 0.0375

次に,データ数を10倍にしてみましょう。

      データ数: 1000個 (単位:秒)  備考
方法 選択ソート3
(変数)
選択ソート2
(関数)
選択ソート1
(普通)
データ数が1000個では,
関数の場合,普通に処理するよりも
約17倍の速度が出ています。
1回目 0.046875 0.109375 1.796875
2回目 0.03125 0.09375 1.79296875
3回目 0.02734375 0.109375 1.79296875
4回目 0.02734375 0.09375 1.77734375
5回目 0.03125 0.09375 1.796875
平均 0.0328125 0.1 1.79140625

さらに,データ数を10倍にしてみましょう。

     データ数: 10000個 (単位:秒)  備考
方法 選択ソート3
(変数)
選択ソート2
(関数) 
選択ソート1
(普通)
データ数が10000個になると,
関数の場合,
約100倍の速度が出ています。

また,おもしろいことに,
変数より関数の方が速くなっています。 
1回目 3.41796875 1.57421875  172.4140625
2回目 3.41796875 1.578125  171.0703125
3回目 3.4140625 1.58984375  170.8515625
4回目 3.43359375 1.57421875  171.0234375
5回目 3.4296875 1.59375  170.7265625
平均 3.42265625 1.58203125  171.2171875

さらに,10倍にするとどうでしょうか?

   データ数: 100000個 (単位:秒)  備考
方法 選択ソート3
(変数)
 選択ソート2
(関数)
選択ソート1
(普通) 
データ数が100000個になると,
関数の方が,さらに速くなり,
変数の約5倍ほどの速度に
なりました。       
1回目 397.1796875 82.0703125 
上記の結果から
膨大な時間が
かかると
思われるため,
実験してません。
      
2回目 397.2421875 82.0703125 
3回目 397.109375 81.9921875 
4回目 398.3203125 82.03125 
5回目 397.0625 82.0078125 
平均 397.3828125 82.034375 

この結果を見ると,関数を使った方が,普通に処理するより,処理時間が早くなります。
また,興味深いのは,データ数によって,一番速い処理が関数であったり,変数であったり
することですね。使い分けるのは難しいですが,知っておくと何かの役に立つかもしれません。