【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:F19").SpecialCells(xlCellTypeFormulas)
            
            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:F19").SpecialCells(xlCellTypeFormulas)

21行目では、数式・計算式が使われているセルを取得して変数「rng」に格納します。

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

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

また、RangeオブジェクトにB2からF19のセルの範囲を指定していますが、B2からF19の範囲内に存在する数式・計算式が使われているセルを取得しています。

注目すべきコード③

次に見て頂きたいのは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メソッドについては次の通りです。