Excel VBA 逆引き集 | 一括解除

Excel VBA
スポンサーリンク

ねらい：一括解除で「一気に元に戻す」

「保護が掛かった多数シート」「大量のデータ検証・条件付き書式」「結合セル・ハイパーリンク・フィルタ」などを、手作業で解除していませんか？一括解除のテンプレを使えば、数秒で「保護・検証・書式・フィルタ・リンク」などをまとめて外せます。初心者でも安全に扱えるよう、重要ポイントを深掘りしながらコード例を示します。

  • 狙い: 解除対象を明確化し、順序と安全枠組みを整えた「壊れない一括解除」
  • 対象: シート保護／セルロック／データ検証／条件付き書式／オートフィルタ／書式・結合／ハイパーリンク／名前定義／オブジェクト（図形・コメント等）
  • 重要ポイント（深掘り）:
    • 順序が命: 保護解除→セルロック解除→装飾・検証・フィルタ解除の流れが安全。
    • 後戻り用の安全枠: 画面更新・再計算の制御と、必要に応じたバックアップ（CSVや別ブック）。

安全枠組み：最適化スイッチと基本ガード

' 安全枠（開始・終了）
Public Sub AppEnter()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Public Sub AppLeave()
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub
VB
  • 重要ポイント（深掘り）:
    • 最適化スイッチ: 解除処理は広範囲に及びがち。画面更新と再計算を止めて速度と安定性を確保。
    • Finally風復帰: 例外時も必ず元に戻す。これを枠化しておくと「戻し忘れ事故」を防げます。

シート保護の一括解除（パスワード対応）

' 全シートの保護解除（パスワードが同一の場合）
Public Sub UnprotectAllSheets(Optional ByVal pwd As String = "")
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If pwd = "" Then
            ws.Unprotect
        Else
            On Error Resume Next
            ws.Unprotect Password:=pwd
            On Error GoTo 0
        End If
    Next
    MsgBox "全シートの保護を解除しました。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • 保護を先に解除: 後続の「セル解除」「書式解除」を実行するための前提条件。
    • パスワード運用: 複数パスワードが混在する場合は、辞書化して試行順を制御（共通パスをConfig化）。

セルのロック・保護設定を一括解除（編集可能化）

' 全シートでセルのロック・非表示を解除（編集可能化）
Public Sub UnlockAllCells()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Locked = False
        ws.Cells.FormulaHidden = False
    Next
    MsgBox "全セルのロック・非表示を解除しました。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • 手順: 保護解除→セルロック解除→必要なら再保護（編集可能な範囲のみ保護）という順序が安全。
    • 式の非表示解除: トラブル時の調査で式を見えるようにするため有効。

データ検証・条件付き書式・フィルタの一括解除

' データ検証の一括削除
Public Sub ClearAllValidations()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Validation.Delete
    Next
    MsgBox "全シートのデータ検証を削除しました。"
    
Clean:
    AppLeave
End Sub

' 条件付き書式の一括削除
Public Sub ClearAllConditionalFormats()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.FormatConditions.Delete
    Next
    MsgBox "全シートの条件付き書式を削除しました。"
    
Clean:
    AppLeave
End Sub

' オートフィルタ解除（テーブル含む）
Public Sub ClearAllFilters()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ' シートのAutoFilter
        If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData: ws.AutoFilterMode = False
        
        ' テーブルのフィルタ解除
        Dim lo As ListObject
        For Each lo In ws.ListObjects
            If lo.ShowAutoFilter Then lo.AutoFilter.ShowAllData
        Next
    Next
    MsgBox "全フィルタを解除しました。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • 検証→条件付き書式の順: 値の入力制限や自動色付けを外してから、見た目調整を行うと混乱が少ない。
    • テーブルのフィルタ: ListObjectのフィルタは別管理。通常のAutoFilterと両方ケアする。

書式・結合・ハイパーリンクを一括解除

' 書式を一括クリア（値はそのまま）
Public Sub ClearAllFormats()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.ClearFormats
    Next
    MsgBox "全シートの書式をクリアしました（値は保持）。"
    
Clean:
    AppLeave
End Sub

' 結合セルを一括解除（列幅等の崩れに注意）
Public Sub UnmergeAllCells()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.UnMerge
    Next
    MsgBox "全シートの結合セルを解除しました。"
    
Clean:
    AppLeave
End Sub

' ハイパーリンクの一括削除
Public Sub RemoveAllHyperlinks()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Hyperlinks.Delete
    Next
    MsgBox "全シートのハイパーリンクを削除しました。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • ClearFormatsは「書式のみ」: 罫線・色・フォント・条件付き書式は消えるが、値/数式は残る。
    • UnMergeの副作用: レイアウトが崩れる可能性大。解除前に列幅・行高さのバックアップや、対象範囲を限定するのが安全。

名前定義・コメント・図形などの一括解除（必要時の強力掃除）

' 名前定義の一括削除（ブック・シート）
Public Sub DeleteAllNames()
    AppEnter
    On Error GoTo Clean
    
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        nm.Delete
    Next
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        For Each nm In ws.Names
            nm.Delete
        Next
    Next
    MsgBox "全ての名前定義を削除しました。"
    
Clean:
    AppLeave
End Sub

' コメント（メモ）削除・図形削除
Public Sub DeleteCommentsAndShapes()
    AppEnter
    On Error GoTo Clean
    
    Dim ws As Worksheet, shp As Shape
    For Each ws In ThisWorkbook.Worksheets
        ' コメント（旧コメント／メモ）
        On Error Resume Next
        ws.Cells.ClearComments
        On Error GoTo 0
        
        ' 図形削除（画像・アイコン・テキストボックスなど）
        For Each shp In ws.Shapes
            shp.Delete
        Next
    Next
    MsgBox "コメントと図形を削除しました。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • 名前定義の影響: 数式や参照に使われている名前を消すとエラーに。必要時のみ／対象を限定。
    • 図形削除は強力: 注意。帳票レイアウトのオブジェクトも消えるため、限定的に使う。

一括解除をまとめて実行（用途別の「プリセット」）

' 安全プリセット：編集可能化＋見た目リセット＋入力制限解除
Public Sub ResetWorkbookLight(Optional ByVal pwd As String = "")
    AppEnter
    On Error GoTo Clean
    
    UnprotectAllSheets pwd
    UnlockAllCells
    ClearAllFilters
    RemoveAllHyperlinks
    
    ' 見た目を軽めにリセット（条件付きと検証のみ）
    ClearAllConditionalFormats
    ClearAllValidations
    
    MsgBox "軽量リセットが完了しました。"
    
Clean:
    AppLeave
End Sub

' 強力プリセット：書式・結合・検証・条件付き・フィルタ・リンクまで全解除
Public Sub ResetWorkbookHard(Optional ByVal pwd As String = "")
    AppEnter
    On Error GoTo Clean
    
    UnprotectAllSheets pwd
    UnlockAllCells
    ClearAllFilters
    ClearAllValidations
    ClearAllConditionalFormats
    RemoveAllHyperlinks
    UnmergeAllCells
    ClearAllFormats
    
    MsgBox "強力リセットが完了しました（値は保持）。"
    
Clean:
    AppLeave
End Sub
VB
  • 重要ポイント（深掘り）:
    • 「軽量」と「強力」の使い分け: 強力はレイアウトや見た目を大幅に変える可能性があるため、必ず事前了解・バックアップ。
    • 値は保持: ここまでのテンプレは基本的に値を残す前提（ClearContentsは使っていない）。

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

  • 例1（保護解除＋編集可能化）:
    • やること: UnprotectAllSheets → UnlockAllCells。保護を外し、全セルを編集可能にする。
  • 例2（入力制限と自動色の解除）:
    • やること: ClearAllValidations → ClearAllConditionalFormats。入力制限と条件付きを外して中身を点検。
  • 例3（フィルタ解除）:
    • やること: ClearAllFilters。絞り込み状態の誤解を一掃。
  • 例4（見た目の全面リセット）:
    • やること: ResetWorkbookHard。レイアウトが崩れる可能性を理解しつつ、データだけ残したクリーン状態へ。

実務の落とし穴と対策（ここを外すと事故る）

  • 落とし穴1：結合解除で帳票が崩壊
    • 対策: 対象範囲を限定／列幅・行高さをバックアップ／必要なら結合維持。
  • 落とし穴2：名前定義削除で数式が壊れる
    • 対策: 依存確認の上で限定削除。全削除は最終手段。
  • 落とし穴3：保護パス不明
    • 対策: 運用でパス一覧を管理。共通パスをConfig化し、未解除シートはログに出す。
  • 落とし穴4：解除後に再計算が暴走
    • 対策: CalculationをManualで実施→最後にAutomaticへ戻す。
  • 落とし穴5：操作痕跡が残らない
    • 対策: 解除前後にログ出力（日時・対象・件数）を残して監査可能に。

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

  • 手順1: AppEnter/AppLeave を標準モジュールに貼る。
  • 手順2: 目的別テンプレ（保護解除・検証解除・条件付き解除・フィルタ解除）を貼る。
  • 手順3: ResetWorkbookLight/Hard を用意して、運用プリセットにする。
  • 手順4: 最初は「軽量」から。強力プリセットは必ずバックアップ後に。
スポンサーリンク
タイトルとURLをコピーしました