【ExcelVBA】ブック内のシート全てに対して数式・計算式が使われているセルを特定する方法について

この記事では、ブック内のシート全てに対して数式・計算式が使われているセルを特定する方法についてご説明します。

【動画】ブック内のシート全てに対して数式・計算式が使われているセルを特定する実際の動き

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


ブック内のシート全てに対して数式・計算式が使われているセルを特定し、シート「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メソッドについては次の通りです。

  1. Anchor:ハイパーリンクを設定するセルの位置
  2. Address:ハイパーリンクを設定するURL
  3. SubAddress:ハイパーリンクのサブアドレス
  4. ScreenTip:マウスオーバーした時のポップアップの表示文字列
  5. TextToDisplay:セルの文字列

①Anchor

Anchorには、ハイパーリンクを設定する数式・計算式が使われているセルの位置を指定します。

②Address

Addressには、ハイパーリンクを設定するURLを指定します。

ただし今回はブック内の数式・計算式が使われているセルがあるシート名をリンク先にするので、Addressには何も指定しません。

③SubAddress

SubAddressには、リンクの飛び先のシートとセルの位置を指定します。

④ScreenTip

ScreenTipには、マウスオーバーした時のポップアップの表示文字列を指定します。

⑤TextToDisplay

TextToDisplayには、セルの文字列を指定します。

動作確認

マクロ実行前

シート「work」

マクロを実行する前の状態。マクロを実行するとシート「work」に、数式・計算式が使われているセル(位置)を書き出します。

シート「1月」

シート「1月」には「B5」「B19」「C19」「D19」「E19」「F19」の6つのセルで数式・計算式が使われています。

シート「2月」

シート「2月」には「D6」「B11」「C11」「D11」「E11」「F11」の6つのセルで数式・計算式が使われています。

シート「3月」

シート「3月」には「E8」「C12」「B14」「C14」「D14」「E14」「F14」の7つのセルで数式・計算式が使われています。

マクロ実行後

マクロを実行後、ブック内のシート全てに対して数式・計算式が使われているセル位置がシート「work」に書き出されています。

最後に

本記事では、ブック内のシート全てに対して数式・計算式が使われているセルを特定する方法についてご説明しました。

ブック内のシート全てに対して数式・計算式が使われているセルをまとめて特定したい時に参考にしてみてくださいね。

プログラミングのスキルを習得するなら

プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。

プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→ TechAcademyの「1週間 無料体験」はこちら