【ExcelVBA】計算式が使われているセルを特定するには

この記事では、計算式が使われているセルを特定する方法についてご説明します。

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

Table of Contents

【動画】計算式が使われているセルを特定している実際の動き

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

コマンドボタンをクリックすると、計算式が使われているセルに色を付きます。

分かりやすくセルを色付けしていますが、再度コマンドボタンをクリックすると、コマンドボタンをクリックする前の色の状態に戻しています。

目次へ戻る

マクロの仕様について

今回ご紹介するマクロを実行した後に計算式が使われたセルを特定するのに、次の仕様を決めています。

計算式が使われているセルに色が付く
セルの色は元に戻せるようにする

特定したセルをどうやって分かるように見せるかは、今回のマクロではセルに色を付けることにしました。
なお、再度マクロを実行することで、色付けしたセルを元に戻せるようにしています。

マクロ実行前(特定前)

セルB3とセルC6に計算式が使われています。

セルB3には黄色の色が設定されています。(C6は塗りつぶしなし)

マクロ実行後(特定後)

マクロを実行すると、計算式が使われているセルB3とセルC6に赤の色が設定されました。

再度マクロ実行後(特定前)

再度マクロを実行すると、セルB3は黄色の色が、セルC6には塗りつぶしなしに色が設定されています。
※セルの色がマクロ実行前(特定前)の状態に戻りました

目次へ戻る

注意事項

今回ご紹介するマクロは、計算式が使われているセルに色を付ける、というものです。

なので、もしマクロを実行する際は、事前に念のためファイルのバックアップ(ファイルのコピー)をしておいてください。

目次へ戻る

計算式が使われているセルを特定する方法

計算式が使われているセルを特定するマクロは、次の流れの通りにコードを書いていきます。

マクロ作成の流れ

STEP.1

計算式が使われているセルを特定する対象のセルの範囲を取得する

計算式が使われているセルを特定する対象のセルの範囲を取得します。

STEP.2

STEP.1で設定した検索対象のセル一つ一つに対して計算式が使われているセルを特定する

STEP.1で設定した検索対象のセル一つ一つに対して計算式が使われているセルを特定します。
セルに計算式が使われているか判定するにはHasFormulaプロパティを使います。
HasFormulaプロパティはセルに数式が含まれている場合はTRUEが格納されます。

コードの例

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

Option Explicit

Private Sub CommandButton1_Click()
    
    Dim rng As Range, rng2 As Range     'Rangeオブジェクト格納用変数
    Dim cnt                As Integer   'カウンタ用変数
    Dim shtExistFlg        As Boolean   'シート存在確認フラグ
    Dim ws                 As Worksheet 'Worksheet用変数
    
    'データを貼り付けるシート名
    Const sheetTopNM As String = "top"
    
    'データを貼り付けるシート名
    Const sheetDataNM As String = "data"
    
    '計算式が設定されているセルに設定する色
    Const colorVal As Long = 255
        
    'セルの範囲を取得する
    Set rng = Sheets(sheetTopNM).Range("A1:D10")
    
    'カウンタを初期化する
    cnt = 0
    
    'シート存在確認フラグにfalse(存在しない)を設定する
    shtExistFlg = False

    'セルの色保持用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = sheetDataNM Then
        
            'シート「data」が存在している場合
            shtExistFlg = True
        
        End If
        
    Next ws
    
    If shtExistFlg = False Then
    
        'シート「data」が存在しない場合
        
        'セルの色を列挙するためのシートを新規作成する
        Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                       .Name = sheetDataNM                       
                       
        'シート「TOP」を選択する
        Sheets(sheetTopNM).Select
                   
    End If
    
    If CommandButton1.Caption = "計算式が入ったセルの色設定" Then
    
        For Each rng2 In rng
        
            If rng2.HasFormula Then
            
                'セルに計算式が使われている場合
                
                'セルの色が「塗りつぶしなし」かどうかの判定
                If rng2.Interior.ColorIndex = xlColorIndexNone Then
                    
                    'セルの色が「塗りつぶしなし」の場合
                    
                    '「塗りつぶしなし」であることを配列に保持しておく
                    '※存在しない適当な値(ここでは仮に9999)にしておく
                    Sheets(sheetDataNM).Range("A" & cnt + 1).Value = 9999
                   
                Else
                
                    'セルの色が「塗りつぶしなし」以外の場合
                    
                    '変更前の色を格納する
                    Sheets(sheetDataNM).Range("A" & cnt + 1).Value = rng2.Interior.Color
                    
                End If
                
                '色を設定する
                rng2.Interior.Color = colorVal
                
                cnt = cnt + 1
                
            End If
            
        Next rng2
        
        CommandButton1.Caption = "計算式が入ったセルの色設定解除"
    
    ElseIf CommandButton1.Caption = "計算式が入ったセルの色設定解除" Then
    
        For Each rng2 In rng
        
            If rng2.HasFormula Then
            
                'セルに計算式が使われている場合
            
                If Sheets(sheetDataNM).Range("A" & cnt + 1).Value = 9999 Then
                
                    'セルの色が元は「塗りつぶしなし」だった場合
            
                    'セルの色を「塗りつぶしなし」に戻す
                    rng2.Interior.ColorIndex = 0
                    
                Else
                
                    'セルの色が元は「塗りつぶしなし」ではなかった場合
                    
                    'セルの色を 元の色に戻す
                    rng2.Interior.Color = Sheets(sheetDataNM).Range("A" & cnt + 1).Value
                
                End If
                
                cnt = cnt + 1
            
            End If
        
        Next rng2
    
        '確認ダイアログを非表示にする
        Application.DisplayAlerts = False
                
        'シートを削除する
        Sheets(sheetDataNM).Delete
        
        '確認ダイアログを表示可にする
        Application.DisplayAlerts = True
        
        CommandButton1.Caption = "計算式が入ったセルの色設定"
        
    End If

End Sub

参考：シートの状態

今回使うExcelのシートは次の通りです。

・セルA1～D10：　　計算式が設定されているかを検索する対象のセル
・コマンドボタン：　マクロを実行するコマンドボタン

コードの解説

注目すべきコード①

最初に見て頂きたいのは20行目です。

    'セルの範囲を取得する
    Set rng = Sheets(sheetTopNM).Range("A1:D10")

20行目で、計算式が設定されているかを検索する対象のセルの範囲を指定します。(上記コードではセルA1からせるD10までのセルを検索対象としています)

注目すべきコード②

次に見て頂きたいのは53行目と90行目です。

    If CommandButton1.Caption = "計算式が入ったセルの色設定" Then

    ElseIf CommandButton1.Caption = "計算式が入ったセルの色設定解除" Then

この2つのif文は、マクロを実行を呼び出すコマンドボタンのCaptionの文字列を判定していますが、Captionの文字列の状態によって次の判定を行っています。

Captionの文字列が「計算式が入ったセルの色設定」の場合・・・計算式が使われているセルを特定して色を付ける処理を行う
Captionの文字列が「計算式が入ったセルの色設定解除」の場合・・・計算式が使われているセルを特定して色を元に戻す処理を行う

Captionの文字列は分かりやすいお好みのもので設定してみてくださいね。

注目すべきコード③

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

        For Each rng2 In rng
        
            If rng2.HasFormula Then
            
                'セルに計算式が使われている場合
                
                'セルの色が「塗りつぶしなし」かどうかの判定
                If rng2.Interior.ColorIndex = xlColorIndexNone Then
                    
                    'セルの色が「塗りつぶしなし」の場合
                    
                    '「塗りつぶしなし」であることを配列に保持しておく
                    '※存在しない適当な値(ここでは仮に9999)にしておく
                    Sheets(sheetDataNM).Range("A" & cnt + 1).Value = 9999
                   
                Else
                
                    'セルの色が「塗りつぶしなし」以外の場合
                    
                    '変更前の色を格納する
                    Sheets(sheetDataNM).Range("A" & cnt + 1).Value = rng2.Interior.Color
                    
                End If
                
                '色を設定する
                rng2.Interior.Color = colorVal
                
                cnt = cnt + 1
                
            End If
            
        Next rng2

55行目で、「注目すべきコード①」で指定したセルの範囲分だけループを行うようループ処理を設定し、57行目でセルに計算式が設定されているか、HasFormulaプロパティの値を判定します。

HasFormulaプロパティがTRUEなら計算式が設定されていることを表します。

今回は、計算式が設定されているセルには色付けをするという仕様に決めているので、80行目でセルに色を設定しています。(変数colorValには色の設定値が格納されています)

なお、セルに色を付けたまま終わりにはしないよう、再度マクロを実行すると色を元に戻すようにしています。

色を戻すには、セルに色付けする前のセルの色を保持しておく必要があるので、新規でシートを作成してそのシートに色付けする前のセルの色を保存しておきます。

セルの色が保存されたシートの中身

セルに色付けする前のセルの色を保持するには68行目か75行目で行っています。

なお、セルの色が「塗りつぶしなし」と「白」では同じ設定値(16777215)なので、「塗りつぶしなし」と「白」を分けるために、まずは62行目でセルの色が「塗りつぶしなし」かを判定しています。

「塗りつぶしなし」かそうでないかを判定するにはInterior.ColorIndexプロパティを使います。

Interior.ColorIndexプロパティが「-4142」(または定数xlColorIndexNone)の値と一致すれば「塗りつぶしなし」(または色なし)です。

「塗りつぶしなし」の場合は、「塗りつぶしなし」であることが分かるように存在しない適当な値(ここでは仮に9999としました)を、シートのセルに設定しておきます。(コードの68行目)

「塗りつぶしなし」ではない場合は、シートのセルに設定しておきます。(コードの75行目）

セルの色が「白」の場合もこの75行目のコードでシートのセルに設定されます。

注目すべきコード④

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

        For Each rng2 In rng
        
            If rng2.HasFormula Then
            
                'セルに計算式が使われている場合
            
                If Sheets(sheetDataNM).Range("A" & cnt + 1).Value = 9999 Then
                
                    'セルの色が元は「塗りつぶしなし」だった場合
            
                    'セルの色を「塗りつぶしなし」に戻す
                    rng2.Interior.ColorIndex = 0
                    
                Else
                
                    'セルの色が元は「塗りつぶしなし」ではなかった場合
                    
                    'セルの色を 元の色に戻す
                    rng2.Interior.Color = Sheets(sheetDataNM).Range("A" & cnt + 1).Value
                
                End If
                
                cnt = cnt + 1
            
            End If
        
        Next rng2

このコードでは、色付けされた計算式が使われているセルの色を元の色に戻す処理を行います。

「注目すべきコード①」で指定したセルの範囲分だけループを行うようループ処理を設定し、92行目でセルに計算式が設定されているか、HasFormulaプロパティの値を判定します。

HasFormulaプロパティがTRUEなら計算式が設定されていることを表します。

もしセルに計算式が使われている場合、元が「塗りつぶしなし」であるかどうかを98行目で判定し、元が「塗りつぶしなし」の色が設定されていた場合は103行目でにセルの色に「塗りつぶしなし」を設定します。

元が「塗りつぶしなし」以外の色が設定されていた場合は、110行目で元の色をセルに設定します。

注目すべきコード⑤

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

    'セルの色保持用シート有無のチェック
    For Each ws In Worksheets
    
        If ws.Name = sheetDataNM Then
        
            'シート「data」が存在している場合
            shtExistFlg = True
        
        End If
        
    Next ws
    
    If shtExistFlg = False Then
    
        'シート「data」が存在しない場合
        
        'セルの色を列挙するためのシートを新規作成する
        Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
                       .Name = sheetDataNM                       
                       
        'シート「TOP」を選択する
        Sheets(sheetTopNM).Select
                   
    End If

このコードでは、シートの作成を行います。
(計算式が使われているセルに色を付けた後に、元の色に戻す際に使うためのセルの色を保持しておくシート)

シートの作成は45行目のWorksheets.Add関数で行います。

シートを作成する前に、念のために作成するシートがマクロ実行前にすでに存在するかを31行目で判定しています。

セルの色を戻したら、124行目でシートを削除しています。

        'シートを削除する
        Sheets(sheetDataNM).Delete

目次へ戻る

動作確認

(マクロを実行する前、)マクロを実行した後、続けてマクロを実行した時のExcelの状態は次の通りです。

マクロ実行前(特定前)

セルB3とセルC6に計算式が使われています。

セルB3には黄色の色が設定されています。(C6は塗りつぶしなし)

マクロ実行後(特定後)

マクロを実行すると、計算式が使われているセルB3とセルC6に赤の色が設定されました。

再度マクロ実行後(特定前)

目次へ戻る

最後に

本記事では、計算式が使われているセルを特定する方法についてご説明しました。

セルに計算式が使われているのか特定するにはHasFormulaプロパティを使います。

HasFormulaプロパティの値がTRUEならセルに計算式が使われている、FALSEなら使われていないことが分かります。

今回はセルの範囲を指定して、そのセル全てに対して計算式が使われているか特定できるよう、パッとみて分かるようにマクロではセルに色を付けるようにしています。

また、再度マクロを実行するとセルの色を戻すよう処理を行うようにしています。

計算式が使われているセルを特定したい場合は是非参考にしてみてくださいね。

※今回ご紹介するマクロは、計算式が使われているセルに色を付ける、というものです。
なので、もしマクロを実行する際は、事前に念のためファイルのバックアップ(ファイルのコピー)をしておいてください。

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

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

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

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

目次へ戻る