【ExcelVBA】爆速カラーリングの戦術メモ

はじめに

　エクセルVBAで予実管理表なんか作るときの大量セルのカラーリングって時間かかりますよね。それを爆速化します。

ソース

SPECIAL THANKS

コメントで提案事項をいただきました。
コメントが活発になるのはいいことですね！ありがとうございます。

提案①：ForEachじゃなくて、ネストが深くなっても二重ループしたほうがいい

    'nukie_53 さん
    Dim rcOffset(0 To 1) As Long
    rcOffset(0) = chunk.Row - 1
    rcOffset(1) = chunk.Column - 1

    Dim c As Long, r As Long
    For c = 1 To chunk.Columns.Count
        For r = 1 To chunk.Rows.Count
            '★色を塗る条件
            If InStr(1, CStr(data(r, c)), instrText) > 0 Then 'CStr() = for error value

                y = rcOffset(0) + r
                x = rcOffset(1) + c

                'セルアドレスの位置情報をR1C1参照として組み立て
                addr = "R" & y & "C" & x
                '...

            End If
        Next r
    Next c

提案②：255文字超えの判定ではJoinよりもLenで数字に取って判定したほうがいい

            'jinoji さん
            Dim addrLen, sumLen
            addrLen = Len(addr)
            'Rangeオブジェクトに指定できるのは255文字まで
            If sumLen + addrLen > 255 Then
                '蓄積した位置情報をA1形式に変換してまとめてエフェクトする
                chunk.Parent.Range(ConvertRC2A(Join(q.ret, ","))).Interior.ColorIndex = color
                '蓄積した位置情報を破棄
                q.Reset
                sumLen = 0
            End If

            'セルアドレスの位置情報を蓄積
            q.Push addr
            sumLen = sumLen + 1 + addrLen

１．色を塗るマスに値がある場合

ChangeColor
ある値を探して大量に色を塗る標準的な場合

２．色を塗るマスに値がなく、別のシートにdbで引っ張ってきた色塗りリストしかない場合

ChangeColor2
業務プログラムだと意外にこういうパターンってあるのよね～

実行前

色を塗るリスト

実行後

根元の参考ソース

　ここ のTest6ですが、速さ検証のグラフなどはここを見てください。

そこの管理人が、「可読性は悪いです。」というものをリファクタリングしました。読解に意外に時間かかったけど結構おもしろかったな。スクラップアンドビルドのゼロベース書き起こしなのでソースは僕のものだと思いましたまる

処理のポイント

　該当セルのアドレスをR1C1参照形式で書き貯め、それをまとめてApplication.ConvertFormulaメソッドでA1形式に変換してセルを塗りつぶす。

注意点

１．単一セルのケースはエラーがおきます。しかし、単一セルを指定することはまずない（直接塗りつぶしてよ！）ので、そこ対処してもいいんだけどやらない。
２．Application.ConvertFormulaメソッドに指定できるのは255文字までのようなので、255文字を超える直前に変換と色を塗る処理を行う。
３．★部分は色を塗るトリガーなので必要に応じて改造してください。