見出し画像
Photo by narukuni

ExcelVBAが高速化できているか検証

まいたけ

前置き

心機一転、投稿していた記事を全削除しました。やましいことがあるわけじゃないですが、中途半端に運用していたので…。今年こそアウトプットを増やしていこうと思います。

さて、noteを書くのがだいぶ久しぶりです。
久しぶりのアウトプットということで、自分がそこそこ得意だと思っているExcelVBA（以下、VBAとします）を取り上げます。

テーマ

今回は高速化について検証してみました。もう少しいうと「自分のコードは教科書通りのやり方に比べて、本当に高速化できているのか」の検証です。

VBAの高速化は奥が深いと思っていて、例えば有名なのでいくと「ScreenUpdatingの停止」や「Calculationの手動設定」などが挙げられると思いますが、それだけでは「VBAを高速化した」とは言えないと考えています。

VBAを最初に覚えたのが新卒で就職した直後くらいなので、もう20年ほど前になります。ただ、高速化については恥ずかしながら5年ほど前から興味を持つようになりました。ちょうど情シスになった直後の頃ですね。扱うデータ量が増え、教科書通りの方法では通用しなくなったので本格的に調べ始めたのがきっかけでした。

処理内容と検証方法

処理内容

  • DB上に登録されているサンプルデータをSELECTし、Excelへ出力する

  • 20,000行をサンプルデータとする

  • VBA上ではデータに対して加工は施さず、取得した値を出力するだけ

検証内容

  • Timer関数で開始時間および終了時間を取得し、その差を結果とする

  • 以下の2パターンで時間差を計測する

    • パターン1：自分が高速化できていると思い込んでいる方法

    • パターン2：教科書通りの方法

検証結果

まどろっこしいのもあれなので、早速結果からいきます。
　パターン1：1.7秒
　パターン2：44.2秒
無事に高速化できていました

ではパターン1と2で何が違うのか？どこを工夫したのか？
結論：Excelシートに極力触らない

以下にコードの概略を載せます。なお雰囲気だけつかんでいただくため、細かい部分は割愛しておりますのでコピペしても動きません。

パターン1のコード

    ' 貼り付ける範囲を動的に設定
    Set targetRange = Range(Cells(出力行, 1列目)), Cells(出力行 + 出力対象行数, 列数))

    ' 貼付け用の配列を定義し、データをセット
    ReDim newList(1 To 出力対象行数, 1 To 列数)
    For i = 1 To 出力対象行数
        For j = 1 To UBound(newList, 2)
            newList(i, j) = データが入った配列(i, j)
        Next
    Next

    ' 配列の値を範囲に一気に貼り付け
    targetRange.Value = newList

パターン2のコード

    For i = 1 To 出力対象行数
        For j = 1 To UBound(データが入った配列, 2)
            Cells(出力行, 1列目 + j).Value = データが入った配列(i, j)
        Next
    Next

パターン1の場合、配列へ先に値を入れるなどしてお膳立てをしたうえで最後に一度だけExcelシートに触ります。対してパターン2はループの中で触りまくっています。これだけの工夫ですが、今回に関しては約40倍ほどの時間差が出ました。

最後に

先日業務でVBAを書く機会があり、当初は能天気に「パターン2で組めばいいかー」と思ったものの、何とか踏みとどまっていつものパターン1で組みました。パターン2の方がお膳立てが不要なので、組むのは楽なんですよね…。

手抜きせず、少しだけ時間をかけて高速化を意識したおかげで利用者にとって使いやすいVBAになったと自負しています。私のポリシーである「明日楽をするために今日苦労する」を守った結果ですね。ま、楽になるのは私ではないですが…笑

＜余談＞
パターン2は「教科書通りの方法」と表現しました。これは、パソコン教室で講師をしていた際に使われていた教科書のコードを基にしています。文字どおり「教科書通り」の方法です。