VBAはデータを扱うならば相当何でもできる万能選手ですが、一つ大きな欠点が…。
それは、速度。
何も気にせずにコードを書くと、超絶遅い処理となってしまいます。
これを回避するべく、色々と工夫するのも日々の業務効率改善に繋がります。
ちょっとした作業でも、自動処理に手間がかかると人はダレてしまい、集中力を削がれるものなので…。
自動化するならば、可能な限り最速、もしくは「状況によらず常に一定なベターな高速」である方が、業務効率改善はできるわけです。
■さて、IT業ではごく普通の自動処理の基本形となるVBAスクリプトですが
今回ご紹介するのは、テキストファイルを読み込み、EXCELシートに展開すると言う基本中の基本のコードです。
しかし…
10行、20行ならばともかく、これを1万行、10万行、100万行とこなしていくに従い、きちんと高速化を念頭にコードを書かなければ、軽く昼寝ができてしまう処理になってしまいます。
これを防ぐため、100万行の処理でも「目標3分以内にすべて反映できるコード」を目指しましょう。
■以下、ソースです。
普通にこういうソースをサラっと書けるようであれば、業務でも通用します。
気をつける点は
①テキストファイルのサイズが分からないので、メモリに無理させぬように行数×列数＝要素数を意識し、保有しているPCのメモリと比較し、PCの暴走・フリーズを防ぎつつ、出来るだけ高速化できるようなメモリサイズを確保すること。
②できるだけEXCELに転記する回数を減らす事（1回でできるだけ大量にデータを貼り付ける事）
ですね。
①②の程ほどちょうどいい所を探れば、常識的には1000行～多くとも10000行でのcommit（書き込み）が重要となるでしょう。
※このソースの試験は10万行、56列（560万要素）と言うデータ量で行いましたが、比較的低速のマシンでも30秒以内に反映できました。
下記のソースでは、10000行単位でメモリをクリアしてるので、1行あたり10000列など滅茶苦茶なデータ構成でない限りは、まず暴走する懸念はないでしょう。
まず、1行あたり10000列ならば、データ構成を疑うべきです（10000列も扱えるほど人間は賢くない、せいぜい1000列以内）
意外と、SEはVBAできちんと処理効率化出来ない人が多いので、こういうテクニックで差をつけると「何故、アイツは早く帰れるんだ？」ってことになるかもしれません。
業務SEは、散々EXCELのお世話になってる割には、意外とEXCELの使い方が雑です。
私からすれば、ここを変えるだけで、2時間早く帰れると思いますけどね笑
業務ＳＥは、自分の本来のスキル（exeファイルの開発能力）に拘り過ぎて、実は一番時間がかかっている調査・エビデンス作成部分のスキルが足りない人が圧倒的に多いんです。
そして、年収の違いの差は、実はここにありますね。
業務SEは、2時間早く仕事を終え、飲みにいきましょう。社会に貢献できますよ^^
自分自身の業務上、何が一番時間的にボトルネックとなっているのかを悟るべきです。
社会・世間はともかく、自分は「楽しましょうよ」
爆速処理して帰宅しましょう、遊びに行きましょう！
会社的に良い人材になれ、早く帰れて、自由時間もある。
もう、使うしかないわけですよ笑
もっともあんまりこういう技術が定番化すると、私が早く帰れなくなるので、最近身内に教える事もしてませんがね苦笑
早くできる事がバレると、それはそれで笑
さて、このソースの何がどういう理由で速いのでしょうか。
分かった方は「すばらしい！」ですね＾＾
--------------------------------------------
'変数宣言を必須とする
Option Explicit
Sub importTextFile()
'所謂おまじない。しかしVBA高速化の基本で「遅くなることはない」「爆速になることはある」⇒だからおまじない
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim DIR As String
DIR = "C:\Users\User\Documents\"
If "\" <> Right(DIR, 1) Then
DIR = DIR & "\"
End If
Const fName As String = "test.txt"
Const MaxLine As Long = 10000 - 1
Const otSheetNm As String = "Sheet1"
Dim buf1 As Variant
Dim buf2() As Variant
Dim maxArray1 As Long
Dim cnt As Long
Dim wRow As Long
Dim i As Long
'With句は単なる記述の簡略化ではない。参照エリアを都度調べずにすむ立派な高速化手段である（常識）
'With Thisworkbook指定は、マクロ動作中に人がwindowsをいじる場合も考慮した優秀な指定手段。
'↑この指定をすれば、アクティブウィンドウが切り替わってもVBAは、参照エリアに困らない。
'基本的に、アクティブウインドウに頼る発想は、VBA的な制約がない限りしないほうが良い
'出来ればWith Thisworkbook.Sheets(シートインデックorシート番号).Cellsまで刷るのが良い
'上記のWithまで領域指定し、Offsetで細かいセル操作をするのが最速である。
'ちなみに、withでcellsまで指定するのと、sheetsまでしか指定しないのでは、10～20%パフォーマンスが変わります。
'故に、できるだけcellsまでWith句で指定し、セル操作はcellsではなくOffsetで行うのがプロ。
'大量データを一気に扱いたい場合はCells.Resizeがコード的にも見やすく、しかも爆速
'地味ながら確実に効くWithでの参照先指定
With ThisWorkbook.Sheets(otSheetNm)
'当然ですよね？データの正確な転記は、文字列指定は基本中の基本＋EXCELの勝手な型解釈を防ぎ「
'速度上の話で言えば「EXCELによる型解釈を@(文字列)指定にする事で、すっとばせる(安定の爆速化)
.Cells.NumberFormatLocal = "@"
Open DIR & fName For Input As #1
Line Input #1, buf1
buf1 = Split(buf1, vbTab)
maxArray1 = UBound(buf1)
'ReDimは確保領域サイズに応じて遅くなる。これはC言語のreallocと同じ。
'その為、ループ処理中にReDimを行わない様にするのはVBA高速化の影の主役。
ReDim buf2(MaxLine, maxArray1) As Variant
Close #1
Open DIR & fName For Input As #1
cnt = 0
wRow = 1
Do Until EOF(1)
Line Input #1, buf1
buf1 = Split(buf1, vbTab)
For i = 0 To maxArray1
buf2(cnt, i) = buf1(i)
Next i
cnt = cnt + 1
If MaxLine + 1 = cnt Then
.Cells(wRow, 1).Resize(MaxLine + 1, maxArray1 + 1) = buf2
wRow = wRow + MaxLine + 1
cnt = 0
End If
Loop
Close #1
'最後っ屁
If 0 < cnt Then
.Cells(wRow, 1).Resize(cnt, maxArray1 + 1) = buf2
End If
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
■さて
今回はたいしたコメントも書かず、生々しいコードのみで遭えて書いてますが、どこら辺に高速化の工夫があるかご理解いただける方は、「そうだよね」と思う筈です。
また、ちょくちょくこういう情報を発信していきますね。
また、ご質問あればコメント欄までお願いします。
出来るだけ分かりやすく答えたいと思います。
では、また！