では、さっそく今回は高速化ランキングの続き、第4位の発表です。


--------------------------------------------------------------------
【今日のテーマ】　セル指定の高速化
--------------------------------------------------------------------


エクセルでデータの読み書きをする際には、必ずどのセルから（又はどのセ
ルへ）ということを指定する必要があるわけですが、通常これを「マクロの
記録」で記録した通りに

Range("A1").Select

という構文をそのまま使っている方も多々見うけます。
これも、少々の工夫で劇的にスピードアップが図れる一因のひとつですので
今回は「セル指定の高速化」ということで、その辺についてご紹介していき
たいと思います。



今回のベンチマークでは、単純に縦に並んだ5万件のデータを1次元配列に放
り込むという処理をサンプルにしました。


まず、通常多く用いられている「マクロの記録」で記録できる上記の構文を
用いたサンプル１の例ですが、

サンプル１
―――――――――――――――――――――――――＋
    For i = 1 To 50000
        Range("B" & CStr(i)).Select
        a(i) = ActiveCell.Value
    Next i
―――――――――――――――――――――――――＋<

このプログラムの実行速度の計測結果は、14.7秒でした。



同様に、一般的によく用いられているOffset(相対移動)を使った以下の
サンプル２の場合では、

サンプル２
―――――――――――――――――――――――――＋
    Range("B1").Select
    For i = 1 To 50000
        a(i) = ActiveCell.Value
        ActiveCell.Offset(1, 0).Activate
    Next i
―――――――――――――――――――――――――＋

13.6秒という結果です。


この結果では、セルのアクセスにはOffset(相対移動)を用いた方が、若干
ではありますが早くなるということが分かります。



もうお気付きの方も多いでしょうが、これに高速化ランキングの第1位の時
にお話した「画面の非表示」という構文を加えてみると、

サンプル３
―――――――――――――――――――――――――＋
    Application.ScreenUpdating = False
    Range("B1").Select
    For i = 1 To 50000
        a(i) = ActiveCell.Value
        ActiveCell.Offset(1, 0).Activate
    Next i
    Application.ScreenUpdating = True
―――――――――――――――――――――――――＋

このサンプル３では、2.6秒という結果となりました。
（当然ですが断然早くなります。）



ですが、
この様な場合、わざわざ「画面の非表示」を使わなくてももっと格段に
スピードアップさせる方法があります。


それは、セレクトせずに直接セルの値を取りに行く下記の方法です。

サンプル４
―――――――――――――――――――――――――＋
    For i = 1 To 50000
        a(i) = Range("B" & CStr(i)).Value
    Next i
―――――――――――――――――――――――――＋

このサンプル４では、1.0秒という結果が出ました。
（「画面の非表示」をせずにこの結果です。）



ちょっと予断ですが、ちなみにこの

a(i) = Range("B" & CStr(i)).Value

という書き方は、本来は

Dim objRng As Range
Set objRng = Range("B" & CStr(i))
a(i) = objRng.Value
Set objRng = Nothing

の言わば省略形なだけなので、どちらで書いても速度的にほとんど変わりま
せん。

でも、VBAでもやたらとこうしたSetを用いたコードを書きたがるプログラマ
も見うけますが、速度が同じでコードが１行ですっきりするという意味では
なるべく前者の書き方でコーディングすることをおすすめします。



話を元に戻しますが、
更に、スピードアップを図る手段として、Cellsを使う方法が有効です。

サンプル５
―――――――――――――――――――――――――＋
    For i = 1 To 50000
        a(i) = Cells(i, "B").Value
    Next i
―――――――――――――――――――――――――＋

このサンプル５の結果では、0.8秒となりました。


結論を言うと、
データ取得の際のセル指定は、Cellsプロパティを使ってダイレクトに値を
取り出す方法が最も高速だということになります。


（ちなみに今、最も高速だと言いましたがVariant型を使ってもっと高速に
データ取り込みを行なえる方法もあります。しかしVariant型を使うこと自
体推奨しませんし語り出すと深く脱線してしまいそうなので、今回Variant
型に関しては触れません。）




また（ついでに）、
データの書き込み時のセル指定の方もベンチマークを行なってみた結果では
以下（サンプル６〜９の比較）に示す様に、データを読み込む場合とほぼ同
様の結果が得られました。


サンプル６　⇒55.9秒
―――――――――――――――――――――――――＋
    For i = 1 To 50000<
        Range("A" & CStr(i)).Select
        ActiveCell.FormulaR1C1 = i
    Next i
―――――――――――――――――――――――――＋


サンプル７　⇒6.1秒
―――――――――――――――――――――――――＋
    Application.ScreenUpdating = False
    For i = 1 To 50000
        Range("A" & CStr(i)).Select
        ActiveCell.FormulaR1C1 = i
    Next i

    Application.ScreenUpdating = True
―――――――――――――――――――――――――＋


サンプル８　⇒4.5秒
―――――――――――――――――――――――――＋
    For i = 1 To 50000
        Range("A" & CStr(i)).Value = i
    Next i
―――――――――――――――――――――――――＋


サンプル９　⇒4.3秒
―――――――――――――――――――――――――＋
    For i = 1 To 50000
        Cells(i, "B").Value = i
    Next i
―――――――――――――――――――――――――＋



ちなみに、
このサンプル８や９でもプラス「画面の非表示」を用いると、
更にもう少しスピードアップを図ることができます。


サンプル１０　⇒3.6秒
―――――――――――――――――――――――――＋
    Application.ScreenUpdating = False
    For i = 1 To 50000
        Cells(i, "B").Value = i
    Next i
    Application.ScreenUpdating = True
―――――――――――――――――――――――――＋



今回はこれで終了です。