【第4位】エクセルVBA高速化ランキング
では、さっそく今回は高速化ランキングの続き、第4位の発表です。
【セル指定】の高速化
エクセルでデータの読み書きをする際には、必ずどのセルから(又はどのセ ルへ)ということを指定する必要があるわけですが、通常これを「マクロの 記録」で記録した通りに
Range("A1").Select
という基本構文がありますが、 これも、少々の工夫で劇的にスピードアップが図れる一因のひとつですので 今回は「セル指定の高速化」ということで、その辺についてご紹介していき たいと思います。
今回のベンチマークでは、単純に縦に並んだ5万件のデータを1次元配列に放 り込むという処理をサンプルにしました。
まず、通常多く用いられている「マクロの記録」で記録できる上記の構文を 用いたサンプル1の例ですが、
サンプル1 ―――――――――――――――――――――――――+ For i = 1 To 50000 Range("B" & CStr(i)).Select a(i) = ActiveCell.Value Next i ―――――――――――――――――――――――――+
このプログラムの実行速度の計測結果は、14.7秒でした。
同様に、一般的によく用いられているOffset(相対移動)を使った以下の サンプル2の場合では、
サンプル2 ―――――――――――――――――――――――――+ Range("B1").Select For i = 1 To 50000 a(i) = ActiveCell.Value ActiveCell.Offset(1, 0).Activate Next i ―――――――――――――――――――――――――+
13.6秒という結果です。
この結果では、セルのアクセスにはOffset(相対移動)を用いた方が、若干 ではありますが速くなるということが分かります。
もうお気付きの方も多いでしょうが、これに高速化ランキングの第1位の時 にお話した「画面の非表示」という構文を加えてみると、
サンプル3 ―――――――――――――――――――――――――+ 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 ―――――――――――――――――――――――――+
このサンプル3では、2.6秒という結果となりました。 (当然ですが断然速くなります。)
ですが、
この様な場合、わざわざ「画面の非表示」を使わなくてももっと格段に スピードアップさせる方法があります。
それは、セレクトせずに直接セルの値を取りに行く下記の方法です。
サンプル4 ―――――――――――――――――――――――――+ For i = 1 To 50000 a(i) = Range("B" & CStr(i)).Value Next i ―――――――――――――――――――――――――+
このサンプル4では、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を用いたコードを書きたがるプログラマ も見うけますが、速度が同じでコードが1行ですっきりするという意味では なるべく前者の書き方でコーディングすることをおすすめします。
話を元に戻しますが、
更に、スピードアップを図る手段として、Cellsを使う方法が有効です。
サンプル5 ―――――――――――――――――――――――――+ For i = 1 To 50000 a(i) = Cells(i, "B").Value Next i ―――――――――――――――――――――――――+
このサンプル5の結果では、0.8秒となりました。
結論を言うと、
データ取得の際のセル指定は、Cellsプロパティを使ってダイレクトに値を 取り出す方法が最も高速だということになります。
(ちなみに今、最も高速だと言いましたがVariant型を使ってもっと高速に データ取り込みを行なえる方法もあります。しかしVariant型を使うこと自 体推奨しませんし語り出すと深く脱線してしまいそうなので、今回Variant 型に関しては触れません。)
また(ついでに)、
データの書き込み時のセル指定の方もベンチマークを行なってみた結果では 以下(サンプル6~9の比較)に示す様に、データを読み込む場合とほぼ同 様の結果が得られました。
サンプル6 ⇒55.9秒 ―――――――――――――――――――――――――+ For i = 1 To 50000 Range("A" & CStr(i)).Select ActiveCell.FormulaR1C1 = i Next i ―――――――――――――――――――――――――+ サンプル7 ⇒6.1秒 ―――――――――――――――――――――――――+ Application.ScreenUpdating = False For i = 1 To 50000 Range("A" & CStr(i)).Select ActiveCell.FormulaR1C1 = i Next i
Application.ScreenUpdating = True ―――――――――――――――――――――――――+ サンプル8 ⇒4.5秒 ―――――――――――――――――――――――――+ For i = 1 To 50000 Range("A" & CStr(i)).Value = i Next i ―――――――――――――――――――――――――+ サンプル9 ⇒4.3秒 ―――――――――――――――――――――――――+ For i = 1 To 50000 Cells(i, "B").Value = i Next i ―――――――――――――――――――――――――+
ちなみに、
このサンプル8や9でもプラス「画面の非表示」を用いると、 更にもう少しスピードアップを図ることができます。
サンプル10 ⇒3.6秒 ―――――――――――――――――――――――――+ Application.ScreenUpdating = False For i = 1 To 50000 Cells(i, "B").Value = i Next i Application.ScreenUpdating = True ―――――――――――――――――――――――――+
今回はこれで終了です。
NEXT >>
・第5位 不要行の削除
・高速化ランキングの目次へ戻る