VBA処理速度の向上（配列？）

質問

いつもお世話になっております。
エクセルVBA勉強中の初心者です。

書籍などを参考に、下記コードを作成し、セル内データを計算しています。
セル数が少ない場合には良かったのですが、将来的には、
約30,000行×100列程度になる予定で、処理に時間がかかってしまっています。
（現状で10,000行×50列程度、データは増えていきます。）

過去ログ、HPなどを調べると、配列に入れれば、処理速度が格段に上がると
あったのですが、如何せん私には難しく、理解できませんでした・・・
配列に限らず、処理速度が上がる方法をご教授、添削頂ければ助かります。

よろしくお願いいたします。

Sub test()

Dim Gyo As Long, Retu As Long

For Gyo = 3 To Range("A65536").End(xlUp).Row
For Retu = 1 To Range("D3").End(xlToRight).Column
Cells(Gyo, Retu).Value = Cells(Gyo, Retu).Value / 1000000
Next Retu
Next Gyo

End Sub

回答No.5

taiga_no_1 · ベストアンサー

配列処理は高速化する上で、避けられないでしょう。
下記のコードを確かめてみてください。

Sub test()
　Dim Gyo As Long, Retu As Long
　Dim AllCells As Variant

　' ---変数にセル範囲を取り込む
　AllCells = Range(Cells(3, 1), _
　　Cells(Range("A65536").End(xlUp).Row, _
　　　　　Range("D3").End(xlToRight).Column))

　' ---何らかの処理
　For Gyo = 1 To UBound(AllCells, 1)
　　　For Retu = 1 To UBound(AllCells, 2)
　　　　　AllCells(Gyo, Retu) = AllCells(Gyo, Retu) / 1000000
　　　Next Retu
　Next Gyo

　' ---セル範囲に一気に書き戻す
　Range(Cells(3, 1), _
　　　Cells(Range("A65536").End(xlUp).Row, _
　　　　　　Range("D3").End(xlToRight).Column)) = AllCells
End Sub

あと下記のコードも追加するともっと速くなります。

　　Application.ScreenUpdating = False

お試しください。

お礼

taiga_no_1様

こんばんは。知りたかった配列処理のコードを記述していただき、誠にありがとうございました！
このように書くのですね。確かに処理も速くいろいろな場面で応用が出来そうです。
内容をじっくり解読し、マスターできるように頑張ります！
ありがとうございました！

回答No.4

AKARI0418

コーディング方法以外にも高速化の手段があるのでご紹介いたします。
Excelのプロパティを操作することで実現できます。
共通していえることですが、必ず解除してから終わってください、画面が真っ白のままになったりします。もし解除できずに終わってしまいましたら、あわてず解除専用マクロを組んで解除してください。

１．表示の更新を自動で行わないようにする。
Application.ScreenUpdating = false
処理
Application.ScreenUpdating = true

２．セル内の計算を自動で行わないようにする。
Application.Calculation = xlCalculationManual
処理
Application.Calculation = xlCalculationAutomatic

３．イベント発生の抑止
Application.EnableEvents = False
処理
Application.EnableEvents = true

お勧めは１と２を組み合わせて使うと速度が大幅に改善されます。
当初のマクロに組み込んで試してみてください、違いが実感できると思います。
Application.ScreenUpdating = false
Application.Calculation = xlCalculationManual
処理
Application.ScreenUpdating = true
Application.Calculation = xlCalculationAutomatic

お礼

AKARI0418様

こんばんは。ご回答ありがとうございます！
確かに処理速度が上がりました！いろいろな場面で応用できそうです。
ありがとうございました！

回答No.3

merlionXX

配列じゃないですが、きっと早いと思います。

Sub test01()
Dim Gyo As Long, Retu As Long
Gyo = Range("A65536").End(xlUp).Row
Retu = Range("D3").End(xlToRight).Column
Cells(1, 1).SpecialCells(xlLastCell).Offset(1).Value = 1000000
Cells(1, 1).SpecialCells(xlLastCell).Copy
Range(Cells(3, 1), Cells(Gyo, Retu)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
Cells(1, 1).SpecialCells(xlLastCell).Clear
Application.CutCopyMode = False
End Sub

お礼

merlionXX様

こんばんは。ご回答ありがとうございます。
先ほど、試してみました。一瞬ですね！！
全体に対して処理する方法もあるのですね。いろいろと勉強になります！
誠にありがとうございました！

回答No.2

OtenkiAme

こんにちは。

セルのデータを　1000000　で割って、元のセルに書き込むのですよね。
それなら、余白のセルに　1000000　を入力してコピーし、
計算対象の範囲に形式を選択して貼り付け（除算）を実行し、
余白のセルの　1000000　をクリアしてみたら如何ですか？

計算対象の範囲の取得方法をどうするかですが、
ワークシート上のデータの内容が判りませんので
サンプルでは、Ａ３セルのアクティブセル領域を対象に書いています。

With ActiveSheet.Range("A3").CurrentRegion
　　With .Resize(1, 1).Offset(, .Columns.Count + 2)
　　　　.Value = 1000000
　　　　.Copy
　　End With
　　.PasteSpecial Paste:=xlValues, Operation:=xlDivide, _
　　　　SkipBlanks:=False, Transpose:=False
　　.Resize(1, 1).Offset(, .Columns.Count + 2).ClearContents
　　.Range("A1").Select
End With

お礼

OtenkiAme様

こんばんは。
コードの提示、ありがとうございました！
試したところ、かなり早く処理できました。
このような方法もあるのですね。。。勉強になります！
参考にして取り入れていきたいと思います！

回答No.1

diashun

ご提示のソースは何が目的かわかりませんが、VBAの処理速度を上げるということが目標なら、配列処理で行うことが避けられません。配列処理とは（私の理解するところでは）、変数の（大量の）値（情報）を主メモリーに格納するので、その情報のIn-Outが高速にできます。ぜひ、配列の勉強に挑戦してみてください。決っして思っているほど難しくはありません。
（私の知識が間違っていればスミマセン）

お礼

diashun様

こんばんは。
早速のご回答、ありがとうございました。
『配列処理』頑張って勉強してみたいと思います！
ただ、なかなか自分が思っている処理に応用できなくて・・・
書籍、サイトを参考にしながらステップアップしたいと思います！