Excel VBA 逆引き集 | 大規模エクセルの設計

Excel VBA
スポンサーリンク

ねらい：大規模エクセルを「壊れない・速い・分かりやすい」設計にする

大規模エクセルは「機能が増えるほど遅く・壊れやすく・読みづらく」なりがちです。設計の型を先に決め、配列I/O、明確な責務分割、設定外出し、ログ・例外・テスト枠を標準装備にすると、規模が大きくなっても安定します。初心者でも貼って動くテンプレを、構成・性能・安全運用・拡張の順で解説します。

アーキテクチャ設計：責務を分け、変更に強くする

層構造（レイヤリング）

  • 入力・出力（シートI/O）層、ドメインロジック層（計算・検証）、インフラ層（ファイル、WMI/ネット、PowerShell委譲）、ユーティリティ層（配列・文字・日時・ログ）に分離します。シート操作はI/O層に限定し、ロジックは配列で完結させます。

命名とフォルダ化

  • 1ブック＝1プロダクト。モジュール名は役割で命名し、標準モジュールに機能ごとの前置詞（IO_, DOM_, INF_, UTIL_, APP_）を付けます。シート名も役割固定（Input、Model、Report、Logs、Config）。

例：基盤テンプレのモジュール一覧

ModApp       （開始・終了枠、環境復帰）
ModLog       （INFO/WARN/ERROR ログ出力）
ModConfig    （外部設定読込）
IO_Sheet     （Range⇔配列 I/O 一括）
DOM_Validate （検証・正規化）
DOM_Calc     （集計・変換ロジック）
INF_Files    （ファイル走査・ZIP連携）
UTIL_Array   （配列ツール、ソート・結合・Distinct）
UTIL_Text    （トリム・大小統一・正規表現）

性能の核：配列I/O・バッチ処理・安定ソート

Rangeを丸ごと配列へ

' IO_Sheet.bas
Option Explicit

Public Function ReadRegion(ByVal ws As Worksheet, Optional ByVal topLeft As String = "A1") As Variant
    ReadRegion = ws.Range(topLeft).CurrentRegion.Value
End Function

Public Sub WriteRegion(ByVal ws As Worksheet, ByVal a As Variant, Optional ByVal topLeft As String = "A1")
    ws.Range(topLeft).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
VB

大規模に効く安定ソート（多列キーは後ろから）

' UTIL_Array_Sort.bas（安定：マージソート）
Option Explicit

Public Sub StableSort2D(ByRef a As Variant, ByVal keyCol As Long, ByVal asc As Boolean)
    Dim n As Long: n = UBound(a, 1)
    If n <= 2 Then Exit Sub
    Dim tmp As Variant: ReDim tmp(1 To n, 1 To UBound(a, 2))
    Dim w As Long: w = 1
    Do While w < n
        Dim i As Long: i = 2
        Do While i <= n
            Dim L As Long: L = i
            Dim M As Long: M = WorksheetFunction.Min(i + w - 1, n)
            Dim R As Long: R = WorksheetFunction.Min(i + 2 * w - 1, n)
            MergeBlocks a, tmp, L, M, R, keyCol, asc
            i = i + 2 * w
        Loop
        Dim r As Long, c As Long
        For r = 2 To n: For c = 1 To UBound(a, 2): a(r, c) = tmp(r, c): Next c: Next r
        w = w * 2
    Loop
End Sub

Private Sub MergeBlocks(ByRef a As Variant, ByRef t As Variant, ByVal L As Long, ByVal M As Long, ByVal R As Long, ByVal k As Long, ByVal asc As Boolean)
    Dim i As Long: i = L, j As Long: j = M + 1, p As Long: p = L
    Do While i <= M And j <= R
        If Cmp(a(i, k), a(j, k), asc) <= 0 Then CopyRow a, i, t, p: i = i + 1 Else CopyRow a, j, t, p: j = j + 1
        p = p + 1
    Loop
    Do While i <= M: CopyRow a, i, t, p: i = i + 1: p = p + 1: Loop
    Do While j <= R: CopyRow a, j, t, p: j = j + 1: p = p + 1: Loop
End Sub

Private Sub CopyRow(ByRef src As Variant, ByVal r As Long, ByRef dst As Variant, ByVal k As Long)
    Dim c As Long: For c = 1 To UBound(src, 2): dst(k, c) = src(r, c): Next
End Sub

Private Function Cmp(ByVal x As Variant, ByVal y As Variant, ByVal asc As Boolean) As Long
    Dim sx As String: sx = LCase$(Trim$(CStr(x)))
    Dim sy As String: sy = LCase$(Trim$(CStr(y)))
    Dim r As Long: If sx < sy Then r = -1 ElseIf sx > sy Then r = 1 Else r = 0
    Cmp = IIf(asc, r, -r)
End Function
VB

重要ポイントの深掘り

  • セル往復をゼロにし、配列で処理して最後に一括書き戻すと「桁違いに速い」。
  • 安定ソートを標準にすると、多列キーや同率順位で“元順序が崩れない”。大規模運用の事故が減ります。

安全運用の基盤：開始・終了枠、ログ、例外、設定外出し

必ず復帰する共通枠

' ModApp.bas
Option Explicit
Public Sub AppEnter(Optional ByVal status As String = "")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    If Len(status) > 0 Then Application.StatusBar = status
End Sub
Public Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

ログと例外の型

' ModLog.bas
Option Explicit
Public Sub Log(ByVal level As String, ByVal msg As String)
    On Error Resume Next
    Dim dir As String: dir = ThisWorkbook.Path & "\logs": If Dir(dir, vbDirectory) = "" Then MkDir dir
    Dim f As String: f = dir & "\" & Format(Date, "yyyy-mm-dd") & ".log"
    Dim h As Integer: h = FreeFile
    Open f For Append As #h
    Print #h, Format(Now, "yyyy-mm-dd HH:NN:SS") & " [" & level & "] " & msg
    Close #h
    On Error GoTo 0
End Sub
VB

設定外出し（INI風）

' ModConfig.bas
Option Explicit
Private gCfg As Object

Public Sub LoadConfig(Optional ByVal path As String = "")
    Set gCfg = CreateObject("Scripting.Dictionary")
    If path = "" Then path = ThisWorkbook.Path & "\config.ini"
    If Dir(path, vbNormal) = "" Then Exit Sub
    Dim h As Integer: h = FreeFile, line As String
    Open path For Input As #h
    Do While Not EOF(h)
        Line Input #h, line: line = Trim$(line)
        If Len(line) > 0 And Left$(line, 1) <> "#" And InStr(line, "=") > 0 Then
            gCfg(LCase$(Split(line, "=")(0))) = Trim$(Split(line, "=")(1))
        End If
    Loop
    Close #h
End Sub

Public Function Cfg(ByVal key As String, Optional ByVal defVal As String = "") As String
    If gCfg Is Nothing Then LoadConfig
    key = LCase$(key)
    Cfg = IIf(gCfg.Exists(key), gCfg(key), defVal)
End Function
VB

重要ポイントの深掘り

  • 大規模は「失敗しても必ず環境復帰」が命。開始・終了枠は全処理の外枠に。
  • ログはレベル・時刻・内容の固定フォーマットで残すと、障害解析が簡単。
  • 設定外出しで、パス・閾値・フラグを“ノンコーディング”で切替可能になります。

データモデル設計：キー・検証・正規化・重複排除

複合キーで一意性を担保

' DOM_Key.bas
Option Explicit
Private Const SEP As String = Chr$(30)

Public Function MakeKey2(ByVal a As Variant, ByVal r As Long, ByVal c1 As Long, ByVal c2 As Long) As String
    MakeKey2 = LCase$(Trim$(CStr(a(r, c1)))) & SEP & LCase$(Trim$(CStr(a(r, c2))))
End Function
VB

データ検証（必須・型・範囲）

' DOM_Validate.bas
Option Explicit

Public Sub ValidateTable(ByRef a As Variant, ByRef errs As Collection)
    Dim r As Long
    For r = 2 To UBound(a, 1)
        If Len(Trim$(CStr(a(r, 1)))) = 0 Then errs.Add "Row " & r & ": Key empty"
        If Not IsNumeric(a(r, 3)) Then errs.Add "Row " & r & ": Amount not numeric"
        If IsNumeric(a(r, 3)) And (CDbl(a(r, 3)) < 0 Or CDbl(a(r, 3)) > 10 ^ 9) Then errs.Add "Row " & r & ": Amount out of range"
    Next
End Sub
VB

重複排除（辞書で高速）

' UTIL_Array_Distinct.bas
Option Explicit

Public Function DistinctByKey(ByVal a As Variant, ByVal keyCols() As Long) As Variant
    Dim d As Object: Set d = CreateObject("Scripting.Dictionary")
    Dim out() As Variant: ReDim out(1 To UBound(a, 1), 1 To UBound(a, 2))
    Dim r As Long, w As Long: w = 1
    For r = 1 To UBound(a, 1)
        Dim k As String: k = BuildKey(a, r, keyCols)
        If Not d.Exists(k) Then
            d(k) = True: w = w + 1
            Dim c As Long: For c = 1 To UBound(a, 2): out(w - 1, c) = a(r, c): Next
        End If
    Next
    ReDim Preserve out(1 To w - 1, 1 To UBound(a, 2))
    DistinctByKey = out
End Function

Private Function BuildKey(ByVal a As Variant, ByVal r As Long, ByVal idx() As Long) As String
    Dim i As Long, s As String
    For i = LBound(idx) To UBound(idx): s = s & LCase$(Trim$(CStr(a(r, idx(i))))) & SEP: Next
    BuildKey = s
End Function
VB

重要ポイントの深掘り

  • 一意性は設計の根幹。複合キーの区切りは“ありえない文字”（Chr(30)）で衝突回避。
  • 検証は「必須・型・範囲」を標準化。エラーは一覧化して人間が判断できる形に。
  • 重複排除は辞書で線形時間。大量データで劇的に効きます。

UIと拡張性：ボタン・メニュー・状態管理・テスト枠

シンプルな操作入口（ボタン1つ）

' ModEntry.bas
Option Explicit

Public Sub RunAll()
    On Error GoTo EH
    AppEnter "RunAll"
    LoadConfig
    Dim wsIn As Worksheet: Set wsIn = Worksheets("Input")
    Dim a As Variant: a = ReadRegion(wsIn)

    Dim errs As New Collection
    ValidateTable a, errs
    If errs.Count > 0 Then
        Log "WARN", "Validation errors: " & errs.Count
        ShowErrors errs
        GoTo EndFlow
    End If

    StableSort2D a, 1, True
    a = DistinctByKey(a, Array(1, 2)) ' A,B列で重複排除

    Dim wsRep As Worksheet: Set wsRep = Worksheets("Report")
    WriteRegion wsRep, a
    Log "INFO", "Process done"
EndFlow:
    AppLeave
    Exit Sub
EH:
    Log "ERROR", Err.Description
    AppLeave
End Sub

Private Sub ShowErrors(ByVal errs As Collection)
    Dim ws As Worksheet: Set ws = Worksheets("Errors")
    ws.Cells.Clear: ws.Range("A1").Value = "Message"
    Dim i As Long: For i = 1 To errs.Count: ws.Cells(i + 1, "A").Value = errs(i): Next
    ws.Columns.AutoFit
End Sub
VB

テスト枠（小さなデータで自動検証）

' ModTest.bas
Option Explicit

Public Sub Test_SortDistinct()
    Dim a As Variant: ReDim a(1 To 6, 1 To 2)
    a(1, 1) = "Key": a(1, 2) = "Val"
    a(2, 1) = "b": a(2, 2) = 1
    a(3, 1) = "a": a(3, 2) = 2
    a(4, 1) = "a": a(4, 2) = 3
    a(5, 1) = "c": a(5, 2) = 4
    a(6, 1) = "b": a(6, 2) = 5
    StableSort2D a, 1, True
    a = DistinctByKey(a, Array(1))
    Debug.Print "Rows=", UBound(a, 1) ' 4行（ヘッダー＋a/b/c）
End Sub
VB

重要ポイントの深掘り

  • UIは「入口を1つ」に。ボタン押下→基盤の型で処理→ログ・エラーが見える、が鉄板。
  • テスト枠は“最小データで自動検証”。拡張時の後退バグを防ぎます。

監査・配布・バージョン管理：壊れない運用を続ける

監査台帳（処理件数・エラー数・所要時間）

  • 日別に「処理件数、エラー件数、開始・終了時刻」をLogsシートへ記録。異常兆候（急増・急減）を可視化します。

配布と依存の管理

  • Late Binding（CreateObject）で参照設定をなくし、配布の失敗を防ぎます。外部ツールはパスをConfig化。
  • 新旧の互換を保つため、ブック名にバージョン（v1.2.3）を付け、変更点はログに記録。

メンテナンスのルール

  • 1機能＝1モジュール、1責務＝1関数。長い関数は分割。コメントは「何を・なぜ」を記載（「どうやって」はコードが示す）。

重要ポイントの深掘り

  • 配布事故の多くは参照設定とパス。Late Binding＋設定外出しで“現場に優しい”に振る。
  • バージョン明示と変更ログで、トラブル時の切り戻しが容易。大規模で必須の運用知。

例題の通し方：10万行の顧客明細を検証→重複排除→並べ替え→レポート

手順

  • InputにCSV取り込み（IO_Sheet.ReadRegionで配列化）。
  • DOM_Validateで必須・型・範囲チェック、Errorsへ一覧。
  • UTIL_Array.Sort（StableSort2D）で顧客名昇順、DistinctByKeyで顧客ID重複排除。
  • ReportへWriteRegionで一括書き戻し、Logsへ結果と所要時間を記録。

成果

  • 10万行規模でも「配列中心＋安定ソート＋辞書重複排除」で数秒〜十数秒。UIは固まらず、エラーは見える化され、再現性・可読性が高い。

まとめ：型を決めて“配列中心・責務分割・外部設定・安全枠”を標準装備にする

  • 大規模でも配列I/Oで速く、安定ソートで正しく、辞書で重複を瞬殺。
  • 例外でも必ず復帰、ログで追跡、設定外出しで運用が軽い。
  • レイヤリングと命名で読みやすく、テスト枠で拡張に強い。

スポンサーリンク
タイトルとURLをコピーしました