2021/03/09

数式ミスを参照元・参照先トレースで発見

• １．背景
• ２．システム概要
• 　２－１．「参照元トレース」の動作
• 　２－２．「トレース」結果の見方
• 　２－３．「参照先トレース」の動作
• 　２－４．その他の機能
• ３．プログラムの流れ
• ４．標準モジュール（Module1）
• ５．ダイアログ（UserForm1）
• 　５ー１．フォーム上のコントロールの配置
• 　５ー２．フォームモジュール
• 　　５ー２ー１．起動時の設定
• 　　５ー２ー２．参照元トレースの実行
• 　　５ー２ー３．参照先トレースの実行
• 　　５ー２ー４．その他のボタン（トレース解除・終了）
• ６．アドインとしてExcelにマクロを登録
• ７．最後に

１．背景

Excelは表計算ソフトですので、使い方として最も多いのは図１－１のような集計表だと思います。セルには計算式も入力できますし、文字通り表計算は得意中の得意です。

図１－１

しかし、その計算に得意なExce表で得られた数値が「なんかおかしい」「どうしても計算が合わない」なんていうことは、残念ながら良くあることです。主な原因は、計算式の間違いです。
関数「SUM」などの範囲指定を間違えたり、間違えたつもりは無いけど「表を拡張した時に、計算式が置いてけぼりになった」などは、私も良くやるミスです。

計算結果が変だったりした時のチェックの方法としては、図１－２のようにいくつかあります。
　・数値を電卓で再計算する
　・数式の入っているセルを選択し、上の数式バーで数式の内容を確認する。
　・数式セルの左上に「緑色の三角印」（エラーチェックマーク）が付いているセルを重点的に調べる
　・「緑色の三角印」のセルを選択すると「注意マーク」が出るので、それにカーソルを当てると注意の内容が表示される。（下記以外にも色々あります。詳細は下記「よりみち」を参照下さい）
　　　「このセルにある数式が、セルの周囲の数式と異なっています」の時は、隣接する数式と違っている可能性がある。
　　　「このセルにある数式は、隣接したセル以外の範囲を参照します」の時は、指定範囲が不足している可能性がある。

図１－２

その他としてExcelの標準の道具を使い、図１－３のように「数式セルを選択し、参照元トレース」をする手段もあります。「数式が示している範囲はどこか」を紫色枠と矢印で示してくれるものです。
今回のシステムは、この参照元・参照先トレースを使って「数式のミスを見つけ易くする」ことを目指しています。

図１－３

「エラーチェック」について、少し調べてみました。 Excelの「ファイル」→「オプション」→「数式」の「エラーチェックルール」には、図１－４、図１－５の１０項目（Excel2016）があります。 表中の「表示されるコメント」については網羅出来ていない可能性はありますが、カーソルを当てることで表示されるコメントを読んで考えれば「間違ている部分が（なんとなく）分かる」ように思います。 図１－４ 図１－５ ただし「エラーチェックを外す設定」も可能で、他のサイトでは「外し方」ばかりに焦点が向いている気がします。 しかし、せっかくExcelが「エラーが隠れているかも」と教えてくれているのですから、「エラーマークは出るように設定」しておき「エラーマークが出ないように努力」するのが正しい姿ではないかと思います。

２．システム概要

本システムをExcelにアドイン登録した時には図２－１のようにボタン①が出来ますので、そのボタン①をクリックすることで、ダイアログ②が表示されます。なお、一番下のサンプルファイルにはシート上に起動ボタンを設置していますので、クリックすることでダイアログ②が起動します。
ダイアログ上には、「参照元トレース③」ボタンと「参照先トレース④」ボタンがあります。トレースを消去するには「トレース解除⑤」ボタンをクリック、システムを終了するには「終了⑥」ボタンをクリックします。

図２－１

２－１．「参照元トレース」の動作

「参照元トレース③」ボタンをクリックすることで、内部的には「数式」タブ→「ワークシート分析」ブロック→「参照元トレース」を実行したことになります。なお、セル選択をどの状態にしておくかで、「参照元トレース」を表示する範囲が異なります。

まず「数式セル以外の単一セル（図２－２のように『表の外のセル』や、表の中でも『数値・文字列のセル』）」を選択している場合は、図２－２のように「アクティブシートの数式の入っているセル全て」に対して「参照元トレース」を表示します。

図２－２

一方「数式の入っているセル」を選択（複数可）している場合は、図２－３のように「選択した数式セル」に対して「参照元トレース」を実行します。
なお「複数セル」を選択し、その中に「数式セルでは無いセル」が含まれている場合には、「数式セルに対してだけトレースを表示」します。これは「数式セルでは無いセルに対してもトレース実行したが、結局表示するものが無かった」ことになるためです。

図２－３

１つの使い方として、図２－４のように「列全体を選択」した場合には、その中の数式がある部分部分に対して「トレースを表示」します。「行全体を選択」でも同様です。
大きな表の場合、合計などの計算式を入れている列・行は固定しているでしょうから、列・行全体を選択する方法は有効と思います。

図２－４

なお、複数セルを選択（列全体・行全体を含む）しても、その中に「数式セルが１つも無い」場合には、「１つもトレースが表示されない」ことになります。これは（トレースの実行はしているが、結局表示するものが無かった　ということになります。
また、表の外（UserdRange以外）で複数セルを選択した状態で「参照元トレース」を実行しようとすると、「対象では無い」旨のコメントが出て終了します。複数セルを選択する場合は、データの存在する部分で実施して下さい。

２－２．「トレース」結果の見方

次に、トレースを表示した結果を見て「どこに数式のエラーがあるのか」を考えます。図２－５は、「P列全体を選択」し「小計列の数式すべてに対してトレース」をした場合です。

図２－５

P列は「１月（D列）～１２月（O列）までの合計」の列とすると、トレースをすれば本来は「D列～O列までが青いトレース枠で囲まれる」ことになるはずです。
しかし図２－５では、①⑤⑥で「集計範囲が不足している」ことが分かりますし、④は矢印が来ていないので「数式ではない」ことも分かります。また、②③⑦で縦線が入っているのは、すぐにエラーとは断定できませんが、「集計範囲が不足」「集計範囲が分割」「集計範囲が重複」の可能性があることを示していると思います。

通常の集計列・行は「計算式が揃っている」のが普通ですから、「トレースが揃っていない部分」に注目すれば「エラーを見つけ出すことは容易」になると思います。

２－３．「参照先トレース」の動作

「参照先トレース④」については、今回システムに盛り込むべきか否かを迷いました。目的が「数式のエラーを発見」でしたので、参照先トレースとは目的が異なると思います。
しかし、使い方はほとんど一緒ですので、参照元トレースと同様の機能を盛り込んでみました。

まず、表の外（UsedRange以外）の単一セルを選択している場合は、図２－６のように「アクティブシートの全セル（UsedRange内）」に対して対して「参照先トレース」を実行します。トレースが表示されないセルは、どこからも参照されていないことを表します。

図２－６

表内のどこかのセル（複数可）を選択している場合は、図２－７のように「選択しているセル」に対して「参照先トレース」を表示します。
なお、選択しているセルが「どこからも参照されていない」場合には、トレースされないことになります。これは「トレースの実行はしているが、結局表示するものが無かった」ということです。

図２－７

また参照元トレースと同様、表の外（UserdRange以外）で複数セルを選択した状態で実行しようとすると、「対象では無い」旨のコメントが出て終了します。複数セルを選択する場合は、データの存在する部分で実施して下さい。

２－４．その他の機能

図２－１の「トレース解除⑤」ボタンをクリックすると、表示したトレースは消去されます。
また「参照元トレース」「参照先トレース」を実行する前には「一旦、前回のトレースを消去後に実行」しますので、トレースの上書きはしません。

トレース実行中は、図２－８のように「実行中のボタンの表示文字が赤色」になります。

図２－８

また大きな表のトレースには、ある程度時間がかかります（特に「参照先トレース」は、「参照元トレース」の数倍以上）。ですので、処理をスタートし、トレースが表示され始めたけど「しまった、こんなに広い範囲で実行するんじゃなかった」と後悔することがあると思います。
その時のために、実行中に「Esc」ボタンを押下すれば「実行を中止」します。
（通常はマクロ実行中にEscキーを押すとマクロ中断しますが、今回は正常終了させますので、作業が継続できます。）

３．プログラムの流れ

「参照元トレース」と「参照先トレース」は、プログラム的にはほとんど同じです。モジュール化できないかと考えたのですが、一部が異なるロジックになってしまったため断念しました。

図３－１が「参照元トレース」の主な流れです。
まず、アクティブなシート内に「数式セル」が存在しなければトレースできないので、そこで終了します。

次に「トレースを実行する範囲」を決めるため、選択しているセル範囲の数を取得し、複数であればUserdRangeと重なっている範囲を「実行範囲」、選択範囲が１セルで且つ数式セルであれば、その選択セルを「実行範囲」とします。
選択範囲が１セルで且つ数式セルでない時は、全数式セルを「実行範囲」としました。
その「実行範囲」をFor Each～Nextで回しながら、１セルずつ「参照元トレース」を実行します。

図３－１

「参照先トレース」の場合は、図３－２の様な流れになります。参照先トレースでは、たとえUsedRange外であっても「他のシートから呼ばれる場合がある」ためトレースの対象となってしまいます。
ですので、全範囲（UsedRange内）で参照先トレースを行うには、単一セルがUsedRange外にあることを条件にしました。それ以外は参照元トレースの時と一緒です。

図３－２

上記のように「参照元トレース」と「参照先トレース」とでは、単一セル選択時のロジックが異なってしまいました。参照元の方も「UsedRange外の単一セル選択 → 全数式セルが対象」という風にも出来たのですが、「参照元トレース」での操作性を重視して決めました。

例えば、非常に大きな表でトレースを実行しようとした時「UsedRangeより外のセルを選択しなければいけない」としたら大変です。なにしろ、ユーザー側にはUsedRangeの範囲が見えるわけでもなく、「範囲外と思って実行しても、そこが範囲内であればトレースが何も表示されない」ことになります。
それよりは「恐らく数式セルよりも圧倒的に多いはずの数式以外のセル」をクリックして実行することで「全体のトレースが実行」された方がストレスも少ないだろう、との気持ちです。

４．標準モジュール（Module1）

標準モジュールには、図４－１のように「システムを起動させるプロシージャ」を置いています。
内容としては、UserForm1をモードレス（ダイアログ表示中もシート作業が可）で起動しています。引数の「vbModeless」を簡単に「0（ゼロ）」としてもＯＫです。

1. '==========　⇩(1)　システムの起動プロシージャ　============
2. Public Sub RefArrow()
3. 　UserForm1.Show vbModeless
4. End Sub

図４－１

５．ダイアログ（UserForm1）

５ー１．フォーム上のコントロールの配置

フォーム上には、図５－１のように４つのボタンを置いています。
表面の文字はマクロ側から設定しますので、配置と大きさだけ考え、作りっぱなしでＯＫです。

図５－１

５ー２．フォームモジュール

５ー２ー１．起動時の設定

フォーム起動時に最初に実行されるのが図５－２のInitializeイベントです。８～１１行目では、４つのCommandButtonに表示文字を設定しています。

6. '==========　⇩(2)　データベース接続とSQL実行　=============
7. Private Sub UserForm_Initialize()
8. 　Me.CommandButton1.Caption = "参照元トレース"
9. 　Me.CommandButton2.Caption = "参照先トレース"
10. 　Me.CommandButton3.Caption = "トレース解除"
11. 　Me.CommandButton4.Caption = "終了"
12. End Sub

図５－２

５ー２ー２．参照元トレースの実行

「参照元トレース」のボタンをクリックした時に動作するプロシージャが図５－３です。

14. '==========　⇩(3)　参照元トレースの実行　=============
15. Private Sub CommandButton1_Click()
16. 　Dim SearchRange As Range　　　 '←トレース実行範囲
17. 　Dim r As Range　　　　　　　　 '←For Each内で実行するセル
19. 　On Error Resume Next
20. 　　Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
21. 　　If Not Err.Number = 0 Then
22. 　　　MsgBox "このシートに数式はありません"
23. 　　　Exit Sub
24. 　　End If
25. 　On Error GoTo 0
27. If Selection.Count = 1 Then
28. 　　If ActiveCell.HasFormula = True Then
29. 　　　Set SearchRange = ActiveCell
30. 　　Else
31. 　　　Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
32. 　　End If
33. 　Else
34. 　　Set SearchRange = Intersect(Selection, ActiveSheet.UsedRange)
35. 　　If SearchRange Is Nothing Then
36. 　　　MsgBox "対象範囲以外です"
37. 　　　Exit Sub
38. 　　End If
39. 　End If
41. 　Me.CommandButton1.ForeColor = RGB(255, 0, 0)
42. 　DoEvents
44. 　Call CommandButton3_Click
46. 　Application.EnableCancelKey = xlErrorHandler
48. 　On Error GoTo myErr
49. 　　For Each r In SearchRange
50. 　　　r.ShowPrecedents
51. 　　Next r
52. 　On Error GoTo 0
54. myErr:
55. 　If Not Err.Number = 18 And Not Err.Number = 0 Then
56. 　　MsgBox Err.Description
57. 　End If
59. 　Set SearchRange = Nothing
61. 　Application.EnableCancelKey = xlInterrupt
63. 　Me.CommandButton1.ForeColor = RGB(0, 0, 0)
65. End Sub

図５－３

１９～２５行目は、シート内の数式セルの有無を調べ、無ければトレースをせずに終了させる部分です。
２０行目ではSpecialCellsメソッドを使って特定のセル（今回は、数式の含まれているセル）を取得し、変数SearchRangeに代入します。
SpecialCellsメソッドの第一引数には、図５－４の１０種があります。今回は「数式が含まれているセル」を使用します。

定数	値	内容
xlcelltypeallformatconditions	-4172	表示形式が設定されているセル
xlcelltypeallvalidation	-4174	条件の設定が含まれているセル
xlcelltypeblanks	4	空白セル
xlcelltypecomments	-4144	コメントが含まれているセル
xlcelltypeconstants	2	定数が含まれているセル
xlcelltypeformulas	-4123	数式が含まれているセル
xlcelltypelastcell	11	使われたセル範囲内の最後のセル
xlcelltypes ameformatcondition	-4173	同じ表示形式が設定されているセル
xlcelltypes amevalidation	-4175	同じ条件の設定が含まれているセル
xlcelltypevisible	12	すべての可視セル

図５－４

またSpecialCellsメソッドには、図５－５のような第二引数もあります。第一引数として「xlcelltypeconstants」「xlcelltypeformulas」を設定した場合に、特定の種類の定数や数式を含むセルだけを取得することができます。

定数	値	内容
xlerrors	16	エラーのあるセル
xllogical	4	論理値のあるセル
xlnumbers	1	数値のあるセル
xlTextValues	2	テキストのあるセル

図５－５

今回「xlcelltypeformulas」を設定していますので、図５－５の設定が可能です。今回は「全てを対象」とすべく「16 + 4 + 1 + 2」の合計「23」を設定しています。なお、この第二引数は「省略すれば全てが対象」となりますので、省略してもＯＫです。

また、SpecialCellsメソッドの対象範囲は「Cells」と「アクティブなシート全面」としました。
なお、対象範囲として単一セル（例えば『ActiveSheet.Range("a1")』）を指定しても「シート全面」が対象となります。理由は良く分かりませんが、「単一セルを指定した時は、全面が対象となる」という今回のシステムとどこか似ている気がします。
もちろん複数セルを対象範囲として指定すれば、その指定範囲内で探したセルを戻して来ます。

１９行目で「On Error Resume Next」としているのは、２０行目のSpecialCellsで「数式セルが無かった場合」や「グラフシートの場合」にはエラーが発生するためです。ですので２１行目でエラー番号を調べ、ゼロ以外（＝エラー発生）の場合は２２行目でコメントを出し、２３行目でマクロを終了させます。

なお、この２０行目の式は３１行目にも再出しますので、３１行目に集約する手もあるかと思います。
と言うのは、２９行目や３４行目では「数式のないセルに対してもトレース実行範囲」としていますので、わざわざ仕分ける前の２０行目で「数式の無いシートを除外」しなくても、３１行目で「数式の無いシートを除外」すれば良いのでは無いか、との考え方です。
これを検証するために、場合分けをしたのが図５－６です。

図５－６

この表で分かることは、３１行目にSpecialCellsを集約してしまう（表の右側）と「数式の無いシートの場合でも、１セル・複数セルに対してはFor Each～Nextを実行してしまう」ことになります。但し、最初に数式シートを除外する方法（表の左側）もSpecialCellsを２回実行してしまう等の無駄もあるように思えます。
ユーザーが「作業しようとしているシートには数式がある」と分かって使うのであれば、３１行目へ集約するパターンの方が良いのかもしれませんが、今回は「システムの対象外である『数式のないシート』は、まず除外する」という考え方から、初期段階（１９～２５行目）で仕訳けることにしました。

２７～３９行目では、「実行範囲」である変数SearchRangeへの設定を行っています。
まず２７行目の「If Selection.Count = 1」で、選択しているセル範囲の数で仕訳けます。単一セルを選択している場合は２８～３２行目を、複数セル選択時は３４～３８行目を実行します。

単一セルの場合は２８行目の「If ActiveCell.HasFormula = True」で、選択しているセルが数式（HasFormula）か否かを調べます。
数式の場合（=True）は、２９行目で「選択しているセルを実行範囲」に設定します。
数式で無い場合（=False）は、３１行目の「Cells.SpecialCells(xlCellTypeFormulas, 23)」で「数式のあるセル全て」を実行範囲としています。
間違っても「Set SearchRange = Cells」などと「全セルを対象」としないようにして下さい。簡単にExcelがフリーズします。

複数セルを選択した場合は、３４行目の「Set SearchRange = Intersect(Selection, ActiveSheet.UsedRange)」で、選択範囲の中でUsedRangeと重なっている部分を実行範囲としています。
この式により「列全体を選択」しても実行範囲が絞り込めることになります。
なお、重なる部分が無い（UsedRangeの外側で複数範囲を選択している）場合は、３４行目で変数SearchRangeがNothingになりますので、３５行目で調べて３６行目でコメントを出し、３７行目でマクロ終了させます。

２７～３９行目のコードを図５－７のような表に整理してみました。

選択セル数	選択セル位置	実行範囲
単一セル	数式セル	選択セル
	数式セル以外	シート内の全数式セル
複数セル	UsedRange内	UsedRangeと重なっている範囲
	UsedRange以外	マクロ終了

図５－７

この「UsedRangeの外側で複数範囲を選択」の状態を「ユーザーがUsedRange全体のトレースを希望している」と考えるならば、３６～３７行目を「３１行目のコードに置き換える」ことになります。
また、「１セルの実行」「複数セルの実行」も「SpecialCellsで数式だけに絞る」という方法もあるかと思います。ただし「SpecialCellsは、対象が無い（例えば複数セル内に数式が無い）とエラーが発生」しますので、その対応が大変そうだったので今回は「１セルは、なにしろ実行」「複数セルもなにしろ実行」としました。

ここまででトレースの準備が出来ましたので、ここからトレース実行に入ります。
まず「実行に入ったことを表す」ために、「ボタンの文字色を赤色」にします。４１行目の「CommandButton1.ForeColor = RGB(255, 0, 0)」で赤色にしているのですが、しかしExcelは間髪空けずに次の処理に進んでしまうため、このままですと「赤色の表示は、トレース処理が完了したあと」になってしまいます。今回はマクロ終了後に文字色を黒色に戻しているため、ユーザーには文字色が変わった事が分からない状態になります。
そこで、文字色を変更した直後の４２行目で「DoEvents」を実行することで、制御が一旦O/S側（Windows）に渡り、表示が更新（文字色が赤に変更）されます。

４４行目は「Call CommandButton3_Click」と、CommandButton3（トレース解除ボタン）のClickイベントプロシージャ（図５－１２）を呼び出しています。その中には「ActiveSheet.ClearArrows」が書かれている為、「トレース解除」を「トレース実行の前に実施」していることになります。
今回は直接「ActiveSheet.ClearArrows」を４４行目に記しても良いですが、今後「トレース解除処理のまとまり」としてCommandButton3_Clickイベントの中にコードを追加されても良いように、イベントプロシージャそのものを呼び出しています。

４６行目の「Application.EnableCancelKey」は、Esc（又は Ctrl+Break）キーが押された時に、どのように処理されるかを設定するもので、プロパティ値は図５－８の３種あります。

定数	値	内容
xldisabled	0	Escキーを無視
xlErrorHandler	2	エラーを発生。エラーコード＝18
xlInterrupt	1(既定)	プロシージャを停止

図５－８

４６行目では「xlErrorHandler」を設定することで、Escキーが押されると「エラーコード18」が得られますので、On Error Goto ･･･とIf Err.Number=18･･･で拾い上げて処理することが可能になります。

先に４９～５１行目を説明します。
４９行目の「For Each r In SearchRange」では、２７～３９行目で決定した実行範囲SearchRangeに対し、１つ１つのセル（変数r）を取り上げて５０行目の「r.ShowPrecedents」で参照元トレースを実行します。
なお手動で「数式では無いセルを選択し、参照元トレースを実行」すると、図５－９のような表示が出ますが、マクロで実行している時には無視してくれるようで、特にエラー処理を考えなくても良いようです。

図５－９

トレース実行の部分は、４８行目の「On Error GoTo myErr」と52行目の「On Error GoTo 0」とに挟まれていますので、トレース実行中にEscキーを押された時（何かのエラーが発生した時も含む）には５４行目の行ラベル「myErr:」に飛び、その中でエラー処理が行われることになります。

５５行目では「If Not Err.Number = 18 And Not Err.Number = 0 Then」と「エラー番号が18番でも0番でも無いエラー（Esc以外の本当のエラー）」の時は５６行目の「MsgBox Err.Description」を実行し、エラー内容を表示します。つまり、実行中にEscキーを押した時は５６行目を実行せず、５９行目以降を実行していきます。
５９行目で変数SearchRangeを解放し、６１行目でEnableCancelKeyのプロパティを元に戻し、６３行目でボタンの文字色を黒色にしてからマクロを終了します。

実はこの流れは、４８～５２行目を「Escキーを押されずに」正常終了した時と同じ終わり方です。正常に実行が完了した時はエラー番号はゼロですので５５行目のIf文の「Not Err.Number = 0」により成立せず５６行目は実行されません。

通常エラー処理部は「エラーが発生した時専用のコード」になっていますが、正常に終了した時に通過してはいけない　という事はありません。
このように並べることで、変数の解放やプロパティの既定値戻しなどのコードを重複させる必要が無くなります。（コードが読み難くなってしまっては、元も子もありませんが）

なお６３行目の文字色変更（赤色→黒色）は、直後にマクロが終了するので「文字色がすぐに変更される」ため「DoEvents」を挿入する必要はありません。

また、４８～５２行目の「On Error GoTo myErr」～「On Error GoTo 0」の間で、Escキーが押されて「myErr:」に飛んでしまうと、「On Error GoTo myErr」が解除されずにマクロ終了してしまうことになります。
しかし「On Error GoTo」の内容は、プロシージャを抜ければ自動的に「On Error GoTo 0」に戻るため、エラー処理の中にあえて含める必要はありません。
なお、６１行目でEnableCancelKeyのプロパティをxlInterruptに戻していますが、試してみると「マクロ終了すれば、既定のxlInterruptに戻る」ようなので、６１行目は本当は不要かもしれませんが、あえて明記しています。

５ー２ー３．参照先トレースの実行

「参照先トレース」のボタンをクリックした時に動作するプロシージャが図５－１０です。

67. '==========　⇩(4)　参照先トレースの実行　=============
68. Private Sub CommandButton2_Click()
69. 　Dim SearchRange As Range　　　 '←トレース実行範囲
70. 　Dim r As Range　　　　　　　　 '←For Each内で実行するセル
72. 　On Error Resume Next
73. 　　Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
74. 　　If Not Err.Number = 0 Then
75. 　　　MsgBox "このシートに数式はありません"
76. 　　　Exit Sub
77. 　　End If
78. 　On Error GoTo 0
80. 　If Selection.Count = 1 Then
81. 　　If Not Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then
82. 　　　Set SearchRange = ActiveCell
83. 　　Else
84. 　　　Set SearchRange = ActiveSheet.UsedRange
85. 　　End If
86. 　Else
87. 　　Set SearchRange = Intersect(Selection, ActiveSheet.UsedRange)
88. 　　If SearchRange Is Nothing Then
89. 　　　MsgBox "対象範囲以外です"
90. 　　　Exit Sub
91. 　　End If
92. 　End If
94. 　Me.CommandButton2.ForeColor = RGB(255, 0, 0)
95. 　DoEvents
97. 　Call CommandButton3_Click
99. 　Application.EnableCancelKey = xlErrorHandler
101. 　On Error GoTo myErr
102. 　　For Each r In SearchRange
103. 　　　r.ShowDependents
104. 　　Next r
105. 　On Error GoTo 0
107. myErr:
108. 　If Not Err.Number = 18 And Not Err.Number = 0 Then
109. 　　MsgBox Err.Description
110. 　End If
112. 　Set SearchRange = Nothing
114. 　Application.EnableCancelKey = xlInterrupt
116. 　Me.CommandButton2.ForeColor = RGB(0, 0, 0)
118. End Sub

図５－１０

内容は「参照元トレース」とほぼ同じですので、簡単に説明します。
７２～７８行目では、シート上に数式セルが無い場合はマクロを中断しています。「参照先トレース」ですので、他のシートから参照されることはあると思いますが、今回は「計算式のチェック」を目的としていますので、数式の無いシートは無視しています。

８０～９２行目では、実行範囲の変数SearchRangeの設定をしています。図５－１１のように、選択セル数と選択セルの位置により、実行範囲を決めています。

選択セル数	選択セル位置	実行範囲
単一セル	UsedRange内	選択セル
	UsedRange以外	全UserdRange
複数セル	UsedRange内	UsedRangeと重なっている範囲
	UsedRange以外	マクロ終了

図５－１１

「参照元トレース」と異なるのは、以下の２点です。
　・「単一セルでUsedRange内を選択」している時には、「選択セル」に対して参照先トレースを実行。
　・「単一セルでUsedRange外を選択」している時には、「UsedRange」の全セルに対して参照先トレースを実行。

９４～９５行目でボタンの文字色を赤色に変更し、１０１～１０５行目で「参照先トレース」を実行します。
実行の途中でEscキーが押された時は１０７行目の「myErr:」行ラベルに飛び、エラー番号が18（Escキーを押した証拠）であった場合、またトレースを正常終了した時は１０９行目を飛び越えて、１１２行目以降の終了処理を行います。

なお、「参照先トレース」の実行時間は「参照元トレース」の数倍以上かかる　と前述しましたが、これはサンプルファイルの「120セルしかない表での結果」です。
この「参照先トレースでは時間がかかる」現象があったので、あとから「Escキーでの中断機能」を盛り込んだ次第ですし、また「Application.ScreenUpdating=False」で画面更新を停止すると確かに時間は短くなるのですが、「止まってしまったのでは？」と思われそうなので「画面更新停止」は止めました。

またExcelを全画面表示の状態で使用している時、トレース処理時間が長くなると「少しの間Excel画面が停止」してしまうことがありました。処理が完了するとExcelも戻ってくるのですが、ダイアログがExcelの下側に行ってしまうようで「ダイアログが消えた！」みたいな状態になるようです（全画面表示以外は大丈夫そう）。
その場合は、Excelを一度最小化にしてから再度表示するとダイアログも戻ってきます。

たぶん表が大きくなればなるほど指数的に処理時間が増えると思いますので、もし実用的で無いと思われたら「全UsedRange範囲」に対する実行は止めるなどの対応をして下さい。

５ー２ー４．その他のボタン（トレース解除・終了）

「トレース解除」ボタンをクリックした時に動作するプロシージャが図５－１２です。

120. '==========　⇩(5)　トレース解除ボタン　=============
121. Private Sub CommandButton3_Click()
122. 　ActiveSheet.ClearArrows
123. End Sub

図５－１２

トレースを「参照元」も「参照先」も含めて解除するには、１２２行目の「ClearArrows」を使用します。
また「参照元トレース」のみを解除するには「ShowPrecedents Remove:=True」、「参照先トレース」のみを解除するには「ShowDependents Remove:=True」と、各トレース実行の引数に「Remove:=True」を設定します。

「終了」ボタンをクリックした時に動作するプロシージャが図５－１３です。

125. '==========　⇩(6)　終了ボタン　=============
126. Private Sub CommandButton4_Click()
127. 　Unload Me
128. End Sub

図５－１３

１２７行目のようなUnloadの代わりに、「Hide」で隠して閉じる方法もあります。しかし、フォームは最初に起動したブックを記憶していますので、複数のブックを起動しながら作業するような場面では、思わぬトラブル（例：フォームが起動しなくなる）が発生する可能性があります。
可能な限り、フォームはUnloadで閉じた方が良いと思います。

６．アドインとしてExcelにマクロを登録

このマクロをExcelの機能の１つに登録し、Excel上部のリボンのボタンを押せばシステムを起動できるようにできます。
その方法については「年賀状リスト等の宛名検索と追記　アドイン登録」を参照下さい。

７．最後に

表内の計算式は、サンプルファイルの様な単純SUM関数だけではありません。複雑な数式（例えば「勤務時間・残業時間などを計算する簡易な勤務表」の勤務表）を持つ表も多いと思います。
そのような複雑な表、又大きな表では処理時間は結構かかりますが、トレースの状況で「数式エラーが一目で分かる」ようになります。その効果は、当初考えていた以上のものでした（スピードに対しては不満は残りますが）。
今回システムを「計算表の最終チェック工程」などに活用することで、運用開始後のトラブルを少なくすることも期待できる気がします。


数式ミスを参照元・参照先トレースで発見（it-052.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。