エクセルVBAでデータ検索・出力をできるだけ高速化

各所で行っているのに毎回忘れて調べる羽目になるのが面倒になったのでメモ代わりに。
よりよい方法があれば修正を加えていきたい(願望)。

修正履歴

2018/08/30 - 2次元配列を利用

経緯

大量のデータを取りまとめる表エクセルの修正を行おうとして調査した結果、表の中身が全てエクセル関数であった。
毎回の変更に対応するファイルとしてはあまりにも人力による修正箇所が多量であり、
それによって多発していたヒューマンエラーを減らそうとVBAの利用を検討。
エクセルでまとめるのやめたらって思うでしょうけど指定を受けているから……

結論(2018/08/30時点)

Application.ScreenUpdating ももちろん操作した上で。
Rangeを2次元配列に落としこんで検索、また出力したいデータを2次元配列で作成してから出力するほうが速い。

example.bas

    Dim dataSheet As Worksheet: Set dataSheet = Worksheets("dataSheet")    'データが存在するシート

    Dim i, j               As Long
    Dim startRow, startCol As Long
    Dim endRow, endCol     As Long
    Dim rng                As Range
    Dim arr                As Variant

    With dataSheet
        startRow = 1
        startCol = 1
        '指定した列の最終行番号を取得(Endから数えて最初にある行)
        endRow = .Cells(Rows.Count, 1).End(xlUp).Row
        '指定した行の最終列番号を取得(Endから数えて最初にある列)
        endCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        '配列化したい任意の範囲を設定
        Set rng = .Range(.Cells(startRow, startCol), .Cells(endRow, endCol))
        '配列にする
        arr = rng

        '配列は普通に使えばいい
        'ex)回してイミディエイトウィンドウに表示
        ''配列の最小インデックスから最大インデックスまでを回す
        For i = LBound(arr) To UBound(arr)
        ' ↓ でも可    ∵Rangeは必ず(1, 1)からはじまるから
        'For i = 1 To UBound(arr, 1)
            For j = LBound(arr, 2) To UBound(arr, 2)
                Debug.Print arr(i, j)
            Next j
        Next i

    End With

思考の動き

Rangeは遅い

はじめRangeを利用して作成してみた。
修正箇所はたしかに減ったものの1枚あたりの出力時間が遅い！遅すぎる！
これならまだエクセル関数を使うほうがマシじゃないか！？

というわけで考え方が悪いわけではないのでいい方法を模索する。

ScreenUpdating プロパティを操作する

そもそもまずこれをやってなかった。
入れるだけで劇的に速くなったものの、もっと速くはならないかと探求を続ける。

Worksheet を変数にセットする

毎度 Worksheet("example") を指定するのはとにかく視認性が低い。
変数にセットしたほうが速くなる……らしいがあまりわからなかった。しかし見やすいので良。

With を使用すると直接指定したよりも少し遅くなるとの話を聞いたが、視認性には変えられなかった。

Rangeを2次元配列に

多少ggればすぐわかることだろうと言われてしまえばそれまでなのだが、ワークシートに値を求めに行くのは大変に遅い。
出力するセルが少数であればともかく、何万も存在するとわかっている場合は2次元配列でデータを作成し、
最後にRangeで貼り付けるほうが圧倒的に速い。(いらんデータを省いてるから)

たぶんもっと速い方法はいくらでもあるのだろうが今回求めていた速さには到達したので一旦ここで考察終了。