HOME > VBA高速化 > 【第4位】セル指定

エクセルVBA高速化ランキング【第4位】


では、さっそく今回は高速化ランキングの続き、第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
―――――――――――――――――――――――――+



今回はこれで終了です。



----------【サンプルテストの実行環境】----------

   PCスペック:Pentium4 1.8GHz
   OS    :Windows XP
   Excel   :Microsoft Excel2003 or 2002

----------------- r(-●_●-) -----------------



この記事は、「GoGo エクセルマクロをはじめよう!」を運営するWATエクセルカスタマイズセンターが実施した速度計測 ベンチマークテストによる結果を基にした高速化手法テクニックを順位付けして発表するものです。 (2007年発行の「エクセルVBA技術通信」のバックナンバー記事からの抜粋)




NEXT >>
時間の掛かる不要行の削除を高速化する簡単テクニック
【コラム】間違いだらけのマクロ高速化手順

目次へ戻る
 

 
最新号 は、下記にアドレス登録すると無料配信されます。

▼マクロ講座の登録はこちら((無料)) まぐまぐ
Go!Go! エクセルマクロをはじめよう! (マガジンID:0000135169)   
メルマガ登録
  メールアドレス:

ロジック編ランキング

  • プログラムのロジックを駆使したVBA高速化の王道テクニックを公開。 (現在準備中...)

 

読者数1万人達成しました!

エクセルで 最大級の
メールマガジン。
まぐまぐ殿堂入り

最新号は 今すぐ ↓↓↓ 登録

メールアドレス:

Powered by
まぐまぐ

完全まぐまぐ配信なので登録安心です!