この記事では、計算式が使われているセルを特定する方法についてご説明します。
【動画】計算式が使われているセルを特定している実際の動き
本題に入る前に、まずは次の動画をご覧ください。
コマンドボタンをクリックすると、計算式が使われているセルに色を付きます。
分かりやすくセルを色付けしていますが、再度コマンドボタンをクリックすると、コマンドボタンをクリックする前の色の状態に戻しています。
マクロの仕様について
今回ご紹介するマクロを実行した後に計算式が使われたセルを特定するのに、次の仕様を決めています。
- 計算式が使われているセルに色が付く
- セルの色は元に戻せるようにする
特定したセルをどうやって分かるように見せるかは、今回のマクロではセルに色を付けることにしました。
なお、再度マクロを実行することで、色付けしたセルを元に戻せるようにしています。
セルB3とセルC6に計算式が使われています。
セルB3には黄色の色が設定されています。(C6は塗りつぶしなし)
マクロを実行すると、計算式が使われているセルB3とセルC6に赤の色が設定されました。
再度マクロを実行すると、セルB3は黄色の色が、セルC6には塗りつぶしなしに色が設定されています。
※セルの色がマクロ実行前(特定前)の状態に戻りました
注意事項
今回ご紹介するマクロは、計算式が使われているセルに色を付ける、というものです。
なので、もしマクロを実行する際は、事前に念のためファイルのバックアップ(ファイルのコピー)をしておいてください。
計算式が使われているセルを特定する方法
計算式が使われているセルを特定するマクロは、次の流れの通りにコードを書いていきます。
マクロ作成の流れ
セルに計算式が使われているか判定するには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
コードの解説
注目すべきコード①
最初に見て頂きたいのは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に赤の色が設定されました。
再度マクロを実行すると、セルB3は黄色の色が、セルC6には塗りつぶしなしに色が設定されています。
※セルの色がマクロ実行前(特定前)の状態に戻りました
最後に
本記事では、計算式が使われているセルを特定する方法についてご説明しました。
セルに計算式が使われているのか特定するにはHasFormulaプロパティを使います。
HasFormulaプロパティの値がTRUEならセルに計算式が使われている、FALSEなら使われていないことが分かります。
今回はセルの範囲を指定して、そのセル全てに対して計算式が使われているか特定できるよう、パッとみて分かるようにマクロではセルに色を付けるようにしています。
また、再度マクロを実行するとセルの色を戻すよう処理を行うようにしています。
計算式が使われているセルを特定したい場合は是非参考にしてみてくださいね。
※今回ご紹介するマクロは、計算式が使われているセルに色を付ける、というものです。
なので、もしマクロを実行する際は、事前に念のためファイルのバックアップ(ファイルのコピー)をしておいてください。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。