【ExcelVBA】ブック内のシート全てに対してエラーが発生しているセルを特定する方法について

この記事では、ブック内のシート全てに対してエラーが発生しているセルを特定する方法についてご説明します。

すぐにソースコードを見たい場合はこちらをクリック

Table of Contents

【動画】ブック内のシート全てに対してエラーが発生しているセルを特定する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

ブック内のシート全てに対してエラーが発生しているセルを特定し、シート「work」に書き出しています。

マクロ作成の流れ

STEP.1

ブック内のシート名を全て取得する

ブック内のシート名を全て取得します。

STEP.2

シート内でエラーが発生しているセル位置を取得する

シート内でエラーが発生しているセル位置を取得します。

目次へ戻る

コードの例

Excelのマクロのコード(例)

Option Explicit

Sub test()

    Dim rng         As Range        'Rangeオブジェクト格納用変数
    Dim rngWk       As Range        'Rangeオブジェクト格納用変数
    Dim ws          As Worksheet    'Worksheet用変数
    Dim cellRowCnt  As Integer
    
    'カウンタを初期化する
    cellRowCnt = 3
    
    'シートの数だけループする
    For Each ws In Worksheets
    
        If ws.Name <> "work" Then
        
            'シート「work」以外の場合
            
            'エラーが発生しているセルを取得する
            Set rng = Worksheets(ws.Name).Range("B2:F11").SpecialCells(xlCellTypeFormulas, xlErrors)
            
            For Each rngWk In rng
            
                'シート名をA列のセルに設定する
                Worksheets("work").Cells(cellRowCnt, 1).Value = ws.Name
                
                'エラーが発生しているセル位置をセルに設定する
                Worksheets("work").Cells(cellRowCnt, 2).Value = Split(rngWk.Address, "$")(1) & rngWk.Row
                                
                'セルにハイパーリンクを設定
                'ハイパーリンクをクリックするとセルに移動する
                Call Hyperlinks.Add( _
                Anchor:=Cells(cellRowCnt, 2), _
                Address:="", _
                SubAddress:=ws.Name & "!" & Split(rngWk.Address, "$")(1) & rngWk.Row, _
                ScreenTip:=ws.Name & "／" & Split(rngWk.Address, "$")(1) & rngWk.Row)
                
                cellRowCnt = cellRowCnt + 1
                        
            Next rngWk
    
        End If
    
    Next ws

End Sub

コードの解説

注目すべきコード①

最初に見て頂きたいのは14行目から16行目です。

    'シートの数だけループする
    For Each ws In Worksheets
    
        If ws.Name <> "work" Then

ブック内のシート全てに対して何かしらの処理を行うようループを処理を行います。

今回は、シート「work」はエラーが発生しているセルのシート名をセル位置を書き出すためだけに使うので、シート「work」を除いてループ処理を行います。

注目すべきコード②

次に見て頂きたいのは21行目です。

            'エラーが発生しているセルを取得する
            Set rng = Worksheets(ws.Name).Range("B2:F11").SpecialCells(xlCellTypeFormulas, xlErrors)

21行目では、エラーが発生しているセルを取得して変数「rng」に格納します。

SpecialCellsメソッドは、SpecialCellsメソッドに指定した定数に該当するセルを返します。

定数xlCellTypeFormulasは「数式が含まれているセル」を表す定数で、数式が設定されているセルを取得したい場合にSpecialCellsメソッドの第1引数にこのxlCellTypeFormulasを指定します。

定数xlErrorsは「エラー値が発生している」ことを表す定数で、数式でエラーが発生しているセルを取得したい場合にSpecialCellsメソッドの第2引数にこのxlErrorsを指定します。

また、RangeオブジェクトにB2からF11のセルの範囲を指定していますが、B2からF11の範囲内に存在するエラーが発生しているセルを取得しています。

注目すべきコード③

次に見て頂きたいのは26行目から37行目です。

                'シート名をA列のセルに設定する
                Worksheets("work").Cells(cellRowCnt, 1).Value = ws.Name
                
                'エラーが発生しているセル位置をセルに設定する
                Worksheets("work").Cells(cellRowCnt, 2).Value = Split(rngWk.Address, "$")(1) & rngWk.Row
                                
                'セルにハイパーリンクを設定
                'ハイパーリンクをクリックするとセルに移動する
                Call Hyperlinks.Add( _
                Anchor:=Cells(cellRowCnt, 2), _
                Address:="", _
                SubAddress:=ws.Name & "!" & Split(rngWk.Address, "$")(1) & rngWk.Row, _
                ScreenTip:=ws.Name & "／" & Split(rngWk.Address, "$")(1) & rngWk.Row)

26行目では、エラーが発生しているセルのシート名をシート「work」のA列に書き出しています。

29行目では、エラーが発生しているセル位置をシート「work」のB列に書き出しています。

33行目から37行目では、29行目で書き出したエラーが発生しているセル位置にハイパーリンクを設定しています。

ハイパーリンクを設定するにはHyperlinks.Addメソッドを使います。

今回使っているHyperlinks.Addメソッドについては次の通りです。