Excel VBA 逆引き集 | 部品ライブラリ化

Excel VBA
スポンサーリンク

ねらい：よく使う処理を「部品化」して、どのブックでも再利用できる状態へ

毎回ゼロから書くと、品質も速度も安定しません。部品ライブラリ化とは、共通処理を「小さな、よく磨かれたモジュール」にまとめ、どのブックにも持ち込んで使える形にすること。初心者でも貼ってすぐ使える「標準部品」を用意し、作り方・使い方・守るべき設計ルールを深掘りして解説します。

  • 目的: 重複コードを排除し、品質・速度・保守性を底上げ
  • 基本方針: 小さく単機能・引数と戻り値で完結・副作用最小、命名と可視性を統一
  • 重要ポイント（深掘り）：
    • インターフェースを固定する: 「引数と戻り値」が仕様。内部は自由に改善しても利用側は壊れない
    • Public最小主義: 外へ見せる関数を必要最小限にし、内部はPrivateで守る
    • ライブラリは”配布しやすく”あること: .basファイル単位、依存は明示、導入が簡単

ライブラリの構成と命名（迷わないルール）

  • ファイル単位（.bas）で分割: ModDateUtil.bas、ModRangeUtil.bas、ModCsv.bas、ModProgress.bas、ModValidator.bas、ModConfig.bas など
  • 1ファイル1責務: 文字列/日付、Range操作、CSV書き出し、進捗、検証、設定読み取り…
  • 命名規約:
    • モジュール名は先頭に「Mod」＋用途（ModCsv、ModProgress）
    • Public関数は「動詞＋目的語」（WriteCsv、ProgressStatus、ReadConfigString）
    • Option Explicitを全モジュールへ
  • 重要ポイント（深掘り）：
    • 名前で役割が伝わると、導入・検索・保守が楽になる
    • 依存の向きは「アプリ→ライブラリ」で一方通行にする。ライブラリ同士の循環依存は禁止

標準部品1：日付・文字列ユーティリティ（ModDateUtil）

' ModDateUtil.bas
Option Explicit

' 例：安全なタイムスタンプ（ファイル名向け）
Public Function StampNow(Optional ByVal withTime As Boolean = True) As String
    If withTime Then
        StampNow = Format(Now, "yyyy-mm-dd_HHNNSS")
    Else
        StampNow = Format(Date, "yyyy-mm-dd")
    End If
End Function

' 例：禁則文字を除いた安全なファイル名へ正規化
Public Function SafeFileName(ByVal raw As String) As String
    Dim name As String: name = Trim$(raw)
    Dim bad As Variant: bad = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    Dim i As Long
    For i = LBound(bad) To UBound(bad)
        name = Replace(name, CStr(bad(i)), "_")
    Next
    Do While Left$(name, 1) = ".": name = Mid$(name, 2): Loop
    Do While Right$(name, 1) = ".": name = Left$(name, Len(name) - 1): Loop
    If Len(name) = 0 Then name = "untitled"
    SafeFileName = name
End Function
VB
  • 深掘り（重要）：
    • ファイル名禁則対応は毎回必要。部品化すると凡ミスが消える
    • タイムスタンプの書式を統一すると、全出力の並びと検索が安定

標準部品2：Rangeユーティリティ（ModRangeUtil）

' ModRangeUtil.bas
Option Explicit

Public Function LastRow(ByVal ws As Worksheet, ByVal col As Variant) As Long
    LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Public Function LastCol(ByVal ws As Worksheet, ByVal headerRow As Long) As Long
    LastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
End Function

Public Function ReadCurrentRegion(ByVal topLeft As Range) As Variant
    ReadCurrentRegion = topLeft.CurrentRegion.Value
End Function

' A1形式の範囲へ2次元配列を書き込む
Public Sub WriteArray(ByVal ws As Worksheet, ByVal topLeft As String, ByVal arr As Variant)
    ws.Range(topLeft).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub
VB
  • 深掘り（重要）：
    • 最終行/最終列取得を共通化すると、バラバラの実装が消えてバグ低減
    • 配列I/Oにすると「ロジック分離」が容易になり、速度も出る

標準部品3：CSV書き出し（UTF-8）ライブラリ（ModCsv）

' ModCsv.bas
Option Explicit

' UTF-8で2次元配列を書き出し
Public Sub WriteCsv(ByVal path As String, ByVal arr As Variant)
    Dim st As Object: Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
        Dim line As String: line = ""
        For c = 1 To UBound(arr, 2)
            Dim s As String: s = CStr(arr(r, c))
            s = Replace(s, """", """""")
            line = line & IIf(c > 1, ",", "") & """" & s & """"
        Next
        st.WriteText line & vbCrLf
    Next
    st.SaveToFile path, 2
    st.Close
End Sub

' フォルダ保証
Public Sub EnsureFolder(ByVal folderPath As String)
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(folderPath) Then fso.CreateFolder folderPath
End Sub
VB
  • 深掘り（重要）：
    • 文字化け対策（UTF-8）を部品に。現場の定番課題を一掃
    • 値のエスケープ（ダブルクォート）を統一。毎回考えないで済む

標準部品4：進捗・ステータスバー（ModProgress）

' ModProgress.bas
Option Explicit

Public Sub ProgressStatus(ByVal cur As Long, ByVal total As Long, Optional ByVal label As String = "進捗")
    If total <= 0 Then Exit Sub
    ' 1%ごとに更新（総件数に応じて間引き）
    If cur Mod Application.WorksheetFunction.Max(1, total \ 100) = 0 Then
        Application.StatusBar = label & " " & Format(cur / total, "0%") & " (" & cur & "/" & total & ")"
        DoEvents
    End If
End Sub

Public Sub StatusClear()
    Application.StatusBar = False
End Sub
VB
  • 深掘り（重要）：
    • 更新頻度の自動調整がポイント。毎回更新は遅い
    • 進捗表示の仕様が共通になるとUXが「いつも同じ」で安心

標準部品5：検証・バリデータ（ModValidator）

' ModValidator.bas
Option Explicit

' 6桁社員番号（数字のみ）
Public Function IsEmpNo(ByVal s As String) As Boolean
    IsEmpNo = (Len(s) = 6) And (s Like "*[!0-9]*" = False)
End Function

' 電話番号（ハイフン可、数字抽出10-11桁）
Public Function IsPhone(ByVal s As String) As Boolean
    Dim digits As String: digits = ExtractDigits(s)
    IsPhone = (Len(digits) >= 10 And Len(digits) <= 11 And digits Like "*[!0-9]*" = False)
End Function

Public Function ExtractDigits(ByVal s As String) As String
    Dim i As Long, r As String
    For i = 1 To Len(s)
        Dim ch As String: ch = Mid$(s, i, 1)
        If ch Like "[0-9]" Then r = r & ch
    Next
    ExtractDigits = r
End Function
VB
  • 深掘り（重要）：
    • 業務定番の検証を部品に。ルール改訂も1箇所で済む
    • 失敗しがちな文字抽出・桁判定を固定化

標準部品6：Config読み取り（ModConfig）

' ModConfig.bas
Option Explicit

Private Function ConfigSheet() As Worksheet
    Set ConfigSheet = ThisWorkbook.Worksheets("Config")
End Function

Public Function ReadConfigString(ByVal key As String) As String
    Dim ws As Worksheet: Set ws = ConfigSheet()
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Dim r As Long
    For r = 2 To lastRow
        If StrComp(CStr(ws.Cells(r, "A").Value), key, vbTextCompare) = 0 Then
            ReadConfigString = Trim$(CStr(ws.Cells(r, "B").Value))
            Exit Function
        End If
    Next
    Err.Raise 900, , "Configキーが見つかりません: " & key
End Function

Public Function ReadConfigNumber(ByVal key As String) As Double
    Dim s As String: s = ReadConfigString(key)
    If Not IsNumeric(s) Then Err.Raise 901, , "数値ではありません: " & key & "=" & s
    ReadConfigNumber = CDbl(s)
End Function
VB
  • 深掘り（重要）：
    • 定数を消してConfigへ。ライブラリは「読み取りAPI」で一貫
    • 誤設定は例外で即停止。黙って進めない

実運用の組み合わせ例（貼って試せる）

' 例：InputのCurrentRegionをCSVでエクスポート（日付付きファイル名、進捗表示）
Sub ExportInputCsv()
    Dim ws As Worksheet: Set ws = Worksheets("Input")
    Dim arr As Variant: arr = ReadCurrentRegion(ws.Range("A1"))
    
    Dim folder As String: folder = ReadConfigString("OUTPUT_FOLDER")
    EnsureFolder folder
    
    Dim fname As String
    fname = SafeFileName(ReadConfigString("OUTPUT_BASE")) & "_" & StampNow(True) & ".csv"
    Dim path As String: path = folder & fname
    
    ' 進捗（行数ベースで目安）
    Dim r As Long, total As Long: total = UBound(arr, 1)
    For r = 1 To total
        ProgressStatus r, total, "CSV準備"
    Next
    StatusClear
    
    WriteCsv path, arr
    MsgBox "出力完了: " & path
End Sub
VB
  • ここが肝：
    • 「組み合わせるだけ」で現場処理が作れる。ロジックは最小
    • 変更があれば部品かConfigを変えるだけ。業務コードはほぼ不変

テストと互換性の維持（インターフェース契約）

  • 契約（仕様）の固定: Public関数の「名前・引数・戻り型」は変えない
  • 内部改善は自由: 高速化やバグ修正は内部で。利用側を壊さない
  • テストSubを各モジュールに用意: 最小の入力で期待出力を確認できるように
' ModCsvテスト例
Sub Test_WriteCsv()
    Dim a(1 To 2, 1 To 3) As Variant
    a(1, 1) = "A": a(1, 2) = "B": a(1, 3) = "C"
    a(2, 1) = "1": a(2, 2) = "2": a(2, 3) = """quoted"""
    EnsureFolder "C:\Tmp\"
    WriteCsv "C:\Tmp\test.csv", a
    MsgBox "CSVテスト完了"
End Sub
VB
  • 深掘り（重要）：
    • 「使う側」を壊さない契約がライブラリ品質の核心
    • テストは部品単体で動くように書くと、配布前チェックが楽

バージョン管理と配布（.basで持ち運ぶ）

  • 配布形態: .basファイルとしてエクスポート（VBE→ファイル→エクスポート）
  • バージョン表記: モジュール先頭コメントに Version と変更履歴を記載
  • 依存の明示: どのモジュールに依存するかをコメントで記載（例：ModCsvはADODB不要、ModRangeUtilに依存なし）
' ModCsv.bas
' Version: 1.2.0
' Changes: UTF-8固定、ダブルクォートのエスケープ修正、EnsureFolder追加
' Depends: Scripting.FileSystemObject（EnsureFolder）
Option Explicit
VB
  • 深掘り（重要）：
    • 依存が分かれば導入が一発で決まる
    • .bas単位ならGit等で差分管理も簡単

品質ルール（これだけ守れば崩れない）

  • Public最小・Private最大: 外に見せるのは入口関数だけ
  • Option Explicit必須: 全モジュールで未宣言変数を排除
  • 副作用最小: 関数は「引数→戻り値」で完結させ、画面操作はしない（必要なら別モジュールへ）
  • 例外は明確なメッセージ: Err.Raiseで止め、呼び出し側でハンドリング
  • ドキュメントコメント: 先頭に「役割・使い方・引数/戻り値・依存・バージョン」

例題で練習（貼って試せる）

  • 例1：日付・名前の部品（ModDateUtil）で安全なファイル名＋タイムスタンプを生成
  • 例2：Range部品（ModRangeUtil）で最終行/配列読み書きの動きを確認
  • 例3：CSV部品（ModCsv）でUTF-8書き出し
  • 例4：進捗部品（ModProgress）で長処理の体感を向上
  • 例5：検証部品（ModValidator）を使って入力データの品質チェック

スターター手順（最短導入）

  1. ここで紹介した6つの標準部品（.bas）を作り、共通ライブラリフォルダへ保存
  2. 新規ブックでは「必要な部品だけ」インポート（責務ごと）
  3. 業務コードは部品のPublic関数を呼ぶだけにする（ロジックを書きすぎない）
  4. 変更は「部品の内部」か「Config」で吸収。インターフェースは固定
  5. バージョンコメントを更新し、配布前にテストSubを実行

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