ExcelVBAで行の繰り返し処理をしたときに使ったやつ

（何番煎じだ！ とか言わないで）

はじめに

ExcelVBAで何らかのデータ加工をする際、避けては通れないのが行の繰り返し処理。
他言語では「値が空白でない間繰り返し」などで簡単に実装できますが、ExcelVBAでは空白判定そのものが複雑怪奇で、さらにVBAの仕様も相まって、スパゲティソースになってしまうことが多々あります…

そこで

• データの終端を表す文字列を明示的に記入してもらう
  空白もデータとして取り扱いたい時にも有用

• 終端文字列の記入漏れによるフリーズを防ぐ機構の追加
  これがないと限界までループを回すことになり、プロセスが応答しなくなり、最終的に桁あふれで落ちたりするかもしれません。

という仕様で、汎用的に使えるよう実装してみることにしました。

以下のVBAは、Excel 2010で動作確認を行いました。

コードと解説

Sub ボタン1_Click()

    ' 1) 各種定義
    '    「読取現在行数」は、行のポインタとして使用している。2行目から読取開始するものとする
    EOL文字列 = "EOL"
    読取現在行数 = 2

    ' 2) EOL文字列が存在するかどうか
    '    A列のデータが埋まっている最終行番号を検索し、上方向にEOL文字列を探す
    '    Excelが認知している最大行から、上方向にデータが入っているセルを探しに行く
    '    終端文字列が存在しなければ、メッセージボックスを表示し処理を中断する
    EOL文字列が存在する = False
    使用済み行数 = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 使用済み行数 To 読取現在行数 Step -1
        If Cells(i, 1) = EOL文字列 Then
            EOL文字列が存在する = True
            Exit For
        End If
    Next i
    If EOL文字列が存在する = False Then
        ret = MsgBox("終端文字列が見つかりません。" & vbCrLf & "A列の最終行に「" & EOL文字列 & "」を記入してください。", vbCritical + vbOKOnly)
        Exit Sub
    End If

    ' 3) 処理を止める
    '    自動計算をオフ、イベント発火の阻止、画面更新を中止することにより、動作速度の向上を図る
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    ' 4) メインループ
    '    終端文字列が出てくるまで繰り返し処理を行う
    Do While Cells(読取現在行数, 1) <> EOL文字列

        ' 何らかの処理

        ' 5)「応答なし」回避
        '    たまに DoEvents を挟むことにより、「応答なし」を回避する
        If 読取現在行数 Mod 3 = 0 Then DoEvents

        読取現在行数 = 読取現在行数 + 1
    Loop

    
    ' 6) 止めた処理を元に戻す
    '    (3)で止めた項目を再度有効にする
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

既知の問題、Tips

• 終端文字列が複数存在した場合、最初に出現したところで処理終了してしまう
  最後に出現するところまでループしたい場合は、 (2) の 検索ループ内の添え字 i を終了行数として使って、メインループをForで回せばよい

• 終端文字列を使わず、最終行番号まで繰り返し処理を行いたい
  (2)の「使用済み行数」を流用し、メインループをForで回すとよい。