配列とセル

VBAが最もよく使われるのが、Officeの中でも最も使われていると思われるExcelでしょう。
Excelでは一般的にもセルの上に置かれた数値を計算したりグラフ化したりとなんらかの処理をするために使われることが多く、それらを定型化、自動化したいという要求が強いのも当然です。

小規模であればセルを直接操作すれば良く、実際にそういうプログラムが多いようです。
これも非常に分かりやすくて
Cells(行,列) というオブジェクトになっています。
Cells(行,列).Value であればそのセルの値、
Cells(行,列).Address であればそのセルの位置、これはExcel上で振られているA1とかそういう値を持ちます。
オブジェクトとプロパティがそのセルの様々な要素と対応していて理解しやすいのです。

小規模であれば良い、というのは大規模になると話が別になるということです。
どうもExcelはセルへの操作(参照も)が桁違いに遅いようなのです。
セルをあたかも変数の感覚で使っていると大規模になるほどにその遅さに悩むことになります。

大概はそのセルの値の操作が殆どだと思うので基本として下のようなことを押さえておきましょう。
Sub test()
Dim cel(10, 1)
Dim i
For i = 0 To 10
cel(i, 0) = i
cel(i, 1) = "○"
Next
Cells(1, 1) = "start"

Range("A2:B11").Value = cel()
End Sub
cel配列を使ってそれに値を設定してから、一気に実際のワークシート(Cells)に反映します。
最後にRangeでワークシート上の範囲を決めて、そこに配列の値を代入するという形です。
もちろん逆にワークシートのある範囲の値を配列に代入することもできます。
cel() = Range("A2:B11").Value
一度セルの値を配列にコピーして操作してから戻す、というのは何か回りくどくて遅いような感覚を持つかもしれませんが、実際にはセルの値を一つ更新するのにも一度変数に読み込んで書き戻すのですから。
なんらかのセルを一つ触るだけでも相当遅いのです。
例えば配列から100個のセルに1度でコピーするより、2回セルに書き込むほうが遅いように思います。

セルを触ることで、再計算や画面の再描画が生じてそれが遅くなる原因となることもあります。
もちろんこれらを止める命令はあるのですが、それもどうもきちんと効いているのか疑問に思うくらい遅いのです。

では実際にどの程度遅いのでしょうか。
実際にテストプログラムを書いてみました。
Sub test()
Dim cel(1 To 50000, 1 To 1)

'配列に入れてからセルにコピーする
For j = 1 To 50
For i = 1 To 50000
cel(i, 1) = Time
Next
adrs1 = Cells(1, j).Address
adrs2 = Cells(50000, j).Address
s = adrs1 & ":" & adrs2
Range(s) = cel(1, 1)
Next

'直接セルに書いていく
For j = 52 To 70
For i = 1 To 50000
Cells(i, j) = Time
Next
Next
End Sub
セルを時刻で埋めていくので実行速度が一目瞭然です。
これの実行結果を見ると、最初の、配列に入れてからの方は1行目から5万行書くのを1秒間で30回ぐらい処理できています。
後者の直接書く方が1つめのループ、つまり1行目から5万行目まで書くのに2〜3秒かかっています。
すごく大ざっぱですが、60倍程度は速度が違うことが分かりました。
具体的な時間で言えば、一分と一秒とか、一時間と一分とかいうそういう違いになります。
comments (2)

コメント

https://ivettebastianelli.wordpress.com/tag/hammertoe | 2017/07/31 04:10 PM
I think this is one of the most significant info for
me. And i am glad reading your article. But wanna
remark on few general things, The website style is ideal,
the articles is really excellent : D. Good job, cheers
How can you heal an Achilles tendonitis fast? | 2017/07/29 06:00 AM
What a data of un-ambiguity and preserveness of precious familiarity regarding unpredicted emotions.

Comment Form