VBAにおける繰り返し処理では、まず For を使わない方法があるか少し考えてみる

VBA なるべく For を使わない【速度比較】

2022年3月11日

「For」とは Pascal 時代から半世紀以上、おなじみの予約語です。「繰り返しのときは、For」とつい思ってしまうし、いまだに私も思考停止のときは、ついそれを選択してしまいがちです。

しかし Excel は便利ソフトです。VBA は その Excel の機能を呼び出せるのです。そしてその機能を呼び出したほうが速度良い場合があります。

賢明な人は Bubble Sort , Heap Sort など 学術的アルゴリズムをご存じだと思われますが、Excel を作った人はそのようなのはとっくにオリコリ済みで、十分にすぐれたアルゴリズムが機能に盛り込まれているのです!

 

さて、それはともかく、

たとえば「通し番号」を打つという極めて単純な処理をVBAでやる場合、ざっと3通り思いつきます。

1.単純数値代入での比較

1.1.For ( 直接セル値のみ代入)  0.66

For i = 1 To 10000
Range("A" & i) = i
Next i

1.2.For ( 配列退避)  0.016

 Dim r As String
r = "A1:A10000"
 B = Range(r).Value
For i = 1 To 10000
' B(i, 1) = i
B(i, 1) = i
Next i
Range(r) = B

圧倒的な差である。この差は、データ量が大きければ大きいほど顕著となります。

ちなみに注意点であるが、このとき Set B=range とやってしまうと、変数にRangeの参照を格納するので、直接セル格納と同義的になります。.Value をつけること。

1.3.セルドラッグ (Excel機能を利用) 0.015

配列退避の場合と殆ど変わらない結果になった。1万というデータ数で変わらないということは、単純な数値を代入する程度であれば、セルドラッグも配列もほぼ同じパフォーマンスであると考えられるでしょう。

ただし、この方法のメリットは早いばかりでなく、値以外もコピーできることです (値のみ、書式のみ、書式ごといずれも可能)

Range("A1") = 1
Range("A2") = 2
Range("A1:A2").Select
Selection.AutoFill Destination:=Range("A1:A10000"), Type:=xlFillDefault

ここでは配列の方法と差がつかなかったが、少し計算が入ってくると、違いが出てくる。その一例を示します。

2.計算をともなう場合

上記の例は通し番号であまりにも単純であった。次に、A列の数値をカウントしたものをB列に代入するという処理の場合はどうでしょう

さすがにこれを For For でやる発想はないと思うが、あえてここで示してみます

2.1.二重For    1500秒

Range("A1") = 1
For i = 1 To 10000
For j = 1 To 10000
If Range("A" & i) = Range("A" & j) Then
k = k + 1
End If
Next j
Range("B" & i) = k
k = 0
Next i

まず、データ100 の時点で 0.17秒。これが 10倍に増えれば 100倍になるのは予想がつき、実際に 15秒となりました。
ということはデータ 1万なら 1500秒である。途方もないです。

ということで、おおかたは CountIF 関数を使う選択肢をとるでしょう。

2.2.VBA から呼び出すExcel関数 + 配列退避 3.7秒

 B = Range("A1:A10000").Value
For i = 1 To 10000
B(i, 1) = Application.WorksheetFunction.CountIf(Range("A:A"), B(i, 1))
Next i
Range("B1:B10000").Value = B

3秒は現実的な速度のように思える。

2.3.セルドラッグ (Excel機能を利用)0.125秒

Range("B1").Formula = "=COUNTIF(A:A,A1)"
Range("B2").Formula = "=COUNTIF(A:A,A2)"
Range("B1:B2").Select
Selection.AutoFill Destination:=Range("B1:B10000"), Type:=xlFillDefault

なんと先ほどの単純数値代入では、配列とドラッグでは差がつかなかったが、ちょっと演算が必要になると1万セルで30倍近くの処理速度の差がでました。

つまり、Excel操作の「セルドラッグ」を、そのまま VBA に書くなんて「なんかやだな」って感じがするし、 Bubble Sort や Heap Sort などを応用したくなるのが人情ってものでしょうが、

残念ながら Windows パソコンで動かすプログラムなのだから、 Widows関連製品 を作った人のライブラリ・機能を使うのがパフォーマンスがよいのです。

 

For 以外にも選択肢があり、Forのほうが遅い上記の例の場合、For を書くメリットも全くないわけではなく、「ダイアログバー」が出せるという利点があります。
ただし「進捗バー」というユーザーインタフェースのために、処理をちょっとだけ遅いような書き方をする必要でしょうか?

しかし製品を作る人の考えによっては、「ユーザを不安にさせるべからず」と、進捗バーが必ず必要である、と言われた場合は、わざわざ処理を遅くする方法を選択せざるをえないシチュエーションもあるのかもしれません。

例えば早い方法で 1分かかるとします。1分の間、ノーリアクションだとユーザーが不安になります。
少し遅い方法(FOR)だと5分かかるとします。

そういう場合は、あえて少し遅い方法(FOR)を採用し、進捗バーを出したほうが、「あ、このくらいかかるんだな。トイレにでも行っとこう」とか、他のウインドウを開いておこうとか、そういう優しさがあるかもしれません。

ただしこれも難しいもので、コードの書き方によっては実行中にExcelウインドウを操作させるとデータがバグる場合もあります。「実行中にユーザーがどのような操作をするか」ということも加味しなければなりません。

どうしても実行中はユーザに操作させたくない場合は、 Win32DLL を用いてマウスポインタを強制的に端っこに固定しておくなどのことも考えられます(が一般的ではないでしょう)。

 

other

Posted by hamahan