Line Inputは遅くない


私のセミナーを受講された方から質問されました。VBAでCSVを読み込むときの話です。

CSVを読み込むとき、Line Inputを使っているんですけど、CSVの行数が多いと時間がかかります。ネットで調べたら、Line InputよりQueryTableを使った方が速いって書いてあったんですけど、どうなんでしょう?

こうした誤解をしている方がとても多いので、ここに書いておきます。

まず、読み込むCSVをご覧ください。

こんな感じのCSVです。これ100,000行あります。まずはLine Inputで読み込んでみましょう。ここでは、次のコードでやってみます。

Sub Macro1()
    Dim buf As String, A As Variant, i As Long, j As Long
    Open "C:\Data\Work\sample.csv" For Input As #1
        Do Until EOF(1)
            i = i + 1
            Line Input #1, buf
            A = Split(buf, ",")
            For j = 0 To UBound(A)
                Cells(i, j + 1) = A(j)
            Next j
        Loop
    Close #1
End Sub

4分20秒くらいですか。さすがに100,000行ともなれば、このくらいの時間はかかるでしょう。ちなみに検証している環境は「CPU:i7-7500, メモリ:16GB, Windows 10 Pro, Excel 2016(1901)」です。では、速いと言われているQueryTableで、やってみましょう。

Sub Macro2()
    With ActiveSheet.QueryTables.Add("TEXT;C:\Data\Work\sample.csv", Range("A1"))
        .TextFileCommaDelimiter = True
        .Refresh
    End With
End Sub

おお!速いじゃないですか。なるほど、確かにこの結果だけを見たら「そっか、Line Inputを使うと遅いのか」と誤解するかもしれませんね。

やってることが違う

速度の前に、これ、そもそもやっていることが違うと理解していますか?OpenとLine Inputは、CSVのデータを直接読み込んで、そのデータをセルに代入しています。CSVとセルはリンクしていません。だから、代入したあとでCSVを変更しても、その変更はセルに反映されません。

対してQueryTableは、データ接続です。元のソースファイル(ここではCSVファイル)への接続を作って、ソースファイルとセルをリンクさせているんです。手動でやるなら、これですね。

ブック内のデータ接続を確認してみれば一目瞭然です。

ちなみに、ソースファイルの名前を変更したり、別のフォルダに移動したりすると、当たり前ですがリンクは途切れます。その状態で更新すると、

まぁ、そうなりますわな。よろしいですか?どっちがイイとかじゃなくて、そもそもやっていることが違うんです。やっていることが違うんですから、本来なら「どっちが速いか」と比べるような話ではありません。われわれは、VBAの速度コンテストに出るんじゃないんです。業務でExcelを使ってるんです。であれば「ここはリンクしよう」とか「ここはデータを読み込もう」など、業務として必要な処理があるはずです。それに従ってマクロを作ります。単純に、マクロのことだけを考えればいいのではありません。

Line Inputは遅くない

でもまぁ、確かに両者の速度差は大きいです。でも、ちょっと待ってください。この結果をもって
Line Input → 遅い
QueryTable → 速い
と考えるのは早計です。

安易に考えれば、両者は「Line Inputを使ったマクロ」と「QueryTableを使ったマクロ」です。でも「Line Inputを使ったマクロ」は、次のことをしているんです。

多くの人は、この(1)~(3)のうち「100,000回も、1行ずつ読み込んでいるんだから、きっとこれが犯人(遅い)だろう」って思うようですね。じゃ、やってみましょう。100,000行のCSVをLine Inputで読み込むだけで、どれくらい時間がかかるのか。

Sub Macro1()
    Dim buf As String, A As Variant, i As Long, j As Long
    Open "C:\Data\Work\sample.csv" For Input As #1
        Do Until EOF(1)
''            i = i + 1
            Line Input #1, buf
''            A = Split(buf, ",")
''            For j = 0 To UBound(A)
''                Cells(i, j + 1) = A(j)
''            Next j
        Loop
    Close #1
End Sub

0.141秒です。Line Inputで100,000行読み込むのは、264.516秒のうち、0.141秒しかかかっていないんです。現在のパソコンは高性能です。CPUもメモリもバスも、めちゃくちゃ速いです。Windowsだって進化しています。Windowsのファイルシステムは、100,000行くらいのテキストファイルだったら、1行ずつ読んだってこの程度です。これで(1)は犯人ではないと分かりました。じゃ、もしかしたら(2)が遅いのでしょうか?やってみましょう。

Sub Macro1()
    Dim buf As String, A As Variant, i As Long, j As Long
    Open "C:\Data\Work\sample.csv" For Input As #1
        Do Until EOF(1)
''            i = i + 1
            Line Input #1, buf
            A = Split(buf, ",")
''            For j = 0 To UBound(A)
''                Cells(i, j + 1) = A(j)
''            Next j
        Loop
    Close #1
End Sub

ご覧のとおりです。Split関数で文字列を配列に変換するのなんて、100,000回やったって0.1秒程度しかかかりません。これで分かりましたよね。遅いのは(1)のLine Inputではなく、(3)の「セルへの代入」なんです。犯人はこいつです。「Line Inputを使うと遅い」ってのは"えん罪"です。Line Inputが泣きながら訴えてますよ「俺じゃない!遅いのは俺じゃないんだ!」って。

まぁ、考えてみれば当然です。これ、セルへの代入が500,000回行われているのですから。

ちなみに私だったら、配列の要素を1つずつセルに代入するなんてことはしません。そんなもの、やる前から遅いと分かっています。一次元配列を横方向のセル範囲に代入するんだったら、次のようにします。

Sub Macro3()
    Dim buf As String, i As Long
    Open "C:\Data\Work\sample.csv" For Input As #1
        Do Until EOF(1)
            i = i + 1
            Line Input #1, buf
            Cells(i, 1).Resize(, 5) = Split(buf, ",")
        Loop
    Close #1
End Sub

59.903秒でした。これ、当初の264.516秒から見れば、だいたい「4.5分の1」です。誤差も考慮に入れて、ここは「約5分の1」と考えるとわかりやすいです。そりゃそうです。セルへの代入が5分の1になったのですから。

ちなみに、もっと速くやるなら、データを一度二次元配列に入れて、セルへの代入は1回で済ませます。このへんの考え方は、

配列を使う

を、ご覧ください。

Sub Macro4()
    Dim buf As String, A As Variant, i As Long, j As Long
    ReDim B(99999, 4)
    Open "C:\Data\Work\sample.csv" For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
            A = Split(buf, ",")
            For j = 0 To UBound(A)
                B(i, j) = A(j)
            Next j
            i = i + 1
        Loop
    Close #1
    Range("A1").Resize(100000, 5) = B   ''1回だけ代入
End Sub

おやおや、QueryTableより速くなっちゃいましたw

マクロの速度を気にするのでしたら、「マクロが遅い」とか「マクロが速い」みたいに"マクロ全体"の実行速度を見てはいけません。そのマクロの中でどんな処理をしているのか。その処理のうち、遅いのはどこかを検証してください。そうでないと今回のように「Line Inputを使っているから遅い」とか「QueryTableを使ったから速い」のように、的外れな判断になってしまいます。