2021/03/09

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




1.背景

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

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

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

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

寄り道
「エラーチェック」について、少し調べてみました。
Excelの「ファイル」→「オプション」→「数式」の「エラーチェックルール」には、図1-4、図1-5の10項目(Excel2016)があります。
表中の「表示されるコメント」については網羅出来ていない可能性はありますが、カーソルを当てることで表示されるコメントを読んで考えれば「間違ている部分が(なんとなく)分かる」ように思います。
緑色の三角印の項目一覧(1)
図1-4
緑色の三角印の項目一覧(2)
図1-5

ただし「エラーチェックを外す設定」も可能で、他のサイトでは「外し方」ばかりに焦点が向いている気がします。
しかし、せっかくExcelが「エラーが隠れているかも」と教えてくれているのですから、「エラーマークは出るように設定」しておき「エラーマークが出ないように努力」するのが正しい姿ではないかと思います。

2.システム概要

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

2-1.「参照元トレース」の動作

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

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

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

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

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

2-2.「トレース」結果の見方

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

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

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

2-3.「参照先トレース」の動作

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

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

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

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

2-4.その他の機能

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

トレース実行中は、図2-8のように「実行中のボタンの表示文字が赤色」になります。
部分的に参照先トレースを実施
図2-8

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

3.プログラムの流れ

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

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

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

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

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

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

4.標準モジュール(Module1)

標準モジュールには、図4-1のように「システムを起動させるプロシージャ」を置いています。
内容としては、UserForm1をモードレス(ダイアログ表示中もシート作業が可)で起動しています。引数の「vbModeless」を簡単に「0(ゼロ)」としてもOKです。
  1. '========== ⇩(1) システムの起動プロシージャ ============
  2. Public Sub RefArrow()
  3.  UserForm1.Show vbModeless
  4. End Sub
図4-1

5.ダイアログ(UserForm1)

5ー1.フォーム上のコントロールの配置

フォーム上には、図5-1のように4つのボタンを置いています。
表面の文字はマクロ側から設定しますので、配置と大きさだけ考え、作りっぱなしでOKです。
UserForm1でのコントロール配置
図5-1

5ー2.フォームモジュール

5ー2ー1.起動時の設定

フォーム起動時に最初に実行されるのが図5-2のInitializeイベントです。8~11行目では、4つのCommandButtonに表示文字を設定しています。
  1. '========== ⇩(2) データベース接続とSQL実行 =============
  2. Private Sub UserForm_Initialize()
  3.  Me.CommandButton1.Caption = "参照元トレース"
  4.  Me.CommandButton2.Caption = "参照先トレース"
  5.  Me.CommandButton3.Caption = "トレース解除"
  6.  Me.CommandButton4.Caption = "終了"
  7. End Sub
図5-2

5ー2ー2.参照元トレースの実行

「参照元トレース」のボタンをクリックした時に動作するプロシージャが図5-3です。
  1. '========== ⇩(3) 参照元トレースの実行 =============
  2. Private Sub CommandButton1_Click()
  3.  Dim SearchRange As Range    '←トレース実行範囲
  4.  Dim r As Range         '←For Each内で実行するセル
  5.  On Error Resume Next
  6.   Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
  7.   If Not Err.Number = 0 Then
  8.    MsgBox "このシートに数式はありません"
  9.    Exit Sub
  10.   End If
  11.  On Error GoTo 0
  12. If Selection.Count = 1 Then
  13.   If ActiveCell.HasFormula = True Then
  14.    Set SearchRange = ActiveCell
  15.   Else
  16.    Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
  17.   End If
  18.  Else
  19.   Set SearchRange = Intersect(Selection, ActiveSheet.UsedRange)
  20.   If SearchRange Is Nothing Then
  21.    MsgBox "対象範囲以外です"
  22.    Exit Sub
  23.   End If
  24.  End If
  25.  Me.CommandButton1.ForeColor = RGB(255, 0, 0)
  26.  DoEvents
  27.  Call CommandButton3_Click
  28.  Application.EnableCancelKey = xlErrorHandler
  29.  On Error GoTo myErr
  30.   For Each r In SearchRange
  31.    r.ShowPrecedents
  32.   Next r
  33.  On Error GoTo 0
  34. myErr:
  35.  If Not Err.Number = 18 And Not Err.Number = 0 Then
  36.   MsgBox Err.Description
  37.  End If
  38.  Set SearchRange = Nothing
  39.  Application.EnableCancelKey = xlInterrupt
  40.  Me.CommandButton1.ForeColor = RGB(0, 0, 0)
  41. End Sub
図5-3

19~25行目は、シート内の数式セルの有無を調べ、無ければトレースをせずに終了させる部分です。
20行目ではSpecialCellsメソッドを使って特定のセル(今回は、数式の含まれているセル)を取得し、変数SearchRangeに代入します。
SpecialCellsメソッドの第一引数には、図5-4の10種があります。今回は「数式が含まれているセル」を使用します。
定数内容
xlcelltypeallformatconditions-4172表示形式が設定されているセル
xlcelltypeallvalidation-4174条件の設定が含まれているセル
xlcelltypeblanks4空白セル
xlcelltypecomments-4144コメントが含まれているセル
xlcelltypeconstants2定数が含まれているセル
xlcelltypeformulas-4123数式が含まれているセル
xlcelltypelastcell11使われたセル範囲内の最後のセル
xlcelltypes ameformatcondition-4173同じ表示形式が設定されているセル
xlcelltypes amevalidation-4175同じ条件の設定が含まれているセル
xlcelltypevisible12すべての可視セル
図5-4

またSpecialCellsメソッドには、図5-5のような第二引数もあります。第一引数として「xlcelltypeconstants」「xlcelltypeformulas」を設定した場合に、特定の種類の定数や数式を含むセルだけを取得することができます。
定数内容
xlerrors16エラーのあるセル
xllogical4論理値のあるセル
xlnumbers1数値のあるセル
xlTextValues2テキストのあるセル
図5-5

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

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

19行目で「On Error Resume Next」としているのは、20行目のSpecialCellsで「数式セルが無かった場合」や「グラフシートの場合」にはエラーが発生するためです。ですので21行目でエラー番号を調べ、ゼロ以外(=エラー発生)の場合は22行目でコメントを出し、23行目でマクロを終了させます。

なお、この20行目の式は31行目にも再出しますので、31行目に集約する手もあるかと思います。
と言うのは、29行目や34行目では「数式のないセルに対してもトレース実行範囲」としていますので、わざわざ仕分ける前の20行目で「数式の無いシートを除外」しなくても、31行目で「数式の無いシートを除外」すれば良いのでは無いか、との考え方です。
これを検証するために、場合分けをしたのが図5-6です。
数式セルの無いシートをどの段階で検出するか
図5-6

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

27~39行目では、「実行範囲」である変数SearchRangeへの設定を行っています。
まず27行目の「If Selection.Count = 1」で、選択しているセル範囲の数で仕訳けます。単一セルを選択している場合は28~32行目を、複数セル選択時は34~38行目を実行します。

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

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

27~39行目のコードを図5-7のような表に整理してみました。
選択セル数選択セル位置実行範囲
単一セル数式セル選択セル
数式セル以外シート内の全数式セル
複数セルUsedRange内UsedRangeと重なっている範囲
UsedRange以外マクロ終了
図5-7

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

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

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

46行目の「Application.EnableCancelKey」は、Esc(又は Ctrl+Break)キーが押された時に、どのように処理されるかを設定するもので、プロパティ値は図5-8の3種あります。
定数内容
xldisabled0Escキーを無視
xlErrorHandler2エラーを発生。エラーコード=18
xlInterrupt1(既定)プロシージャを停止
図5-8

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

先に49~51行目を説明します。
49行目の「For Each r In SearchRange」では、27~39行目で決定した実行範囲SearchRangeに対し、1つ1つのセル(変数r)を取り上げて50行目の「r.ShowPrecedents」で参照元トレースを実行します。
なお手動で「数式では無いセルを選択し、参照元トレースを実行」すると、図5-9のような表示が出ますが、マクロで実行している時には無視してくれるようで、特にエラー処理を考えなくても良いようです。
数式セルの無いセルで参照元トレースを実行した時のエラー表示
図5-9

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

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

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

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

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

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

5ー2ー3.参照先トレースの実行

「参照先トレース」のボタンをクリックした時に動作するプロシージャが図5-10です。
  1. '========== ⇩(4) 参照先トレースの実行 =============
  2. Private Sub CommandButton2_Click()
  3.  Dim SearchRange As Range    '←トレース実行範囲
  4.  Dim r As Range         '←For Each内で実行するセル
  5.  On Error Resume Next
  6.   Set SearchRange = Cells.SpecialCells(xlCellTypeFormulas, 23)
  7.   If Not Err.Number = 0 Then
  8.    MsgBox "このシートに数式はありません"
  9.    Exit Sub
  10.   End If
  11.  On Error GoTo 0
  12.  If Selection.Count = 1 Then
  13.   If Not Intersect(Selection, ActiveSheet.UsedRange) Is Nothing Then
  14.    Set SearchRange = ActiveCell
  15.   Else
  16.    Set SearchRange = ActiveSheet.UsedRange
  17.   End If
  18.  Else
  19.   Set SearchRange = Intersect(Selection, ActiveSheet.UsedRange)
  20.   If SearchRange Is Nothing Then
  21.    MsgBox "対象範囲以外です"
  22.    Exit Sub
  23.   End If
  24.  End If
  25.  Me.CommandButton2.ForeColor = RGB(255, 0, 0)
  26.  DoEvents
  27.  Call CommandButton3_Click
  28.  Application.EnableCancelKey = xlErrorHandler
  29.  On Error GoTo myErr
  30.   For Each r In SearchRange
  31.    r.ShowDependents
  32.   Next r
  33.  On Error GoTo 0
  34. myErr:
  35.  If Not Err.Number = 18 And Not Err.Number = 0 Then
  36.   MsgBox Err.Description
  37.  End If
  38.  Set SearchRange = Nothing
  39.  Application.EnableCancelKey = xlInterrupt
  40.  Me.CommandButton2.ForeColor = RGB(0, 0, 0)
  41. End Sub
図5-10

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

80~92行目では、実行範囲の変数SearchRangeの設定をしています。図5-11のように、選択セル数と選択セルの位置により、実行範囲を決めています。
選択セル数選択セル位置実行範囲
単一セルUsedRange内選択セル
UsedRange以外全UserdRange
複数セルUsedRange内UsedRangeと重なっている範囲
UsedRange以外マクロ終了
図5-11

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

94~95行目でボタンの文字色を赤色に変更し、101~105行目で「参照先トレース」を実行します。
実行の途中でEscキーが押された時は107行目の「myErr:」行ラベルに飛び、エラー番号が18(Escキーを押した証拠)であった場合、またトレースを正常終了した時は109行目を飛び越えて、112行目以降の終了処理を行います。

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

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

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

5ー2ー4.その他のボタン(トレース解除・終了)

「トレース解除」ボタンをクリックした時に動作するプロシージャが図5-12です。
  1. '========== ⇩(5) トレース解除ボタン =============
  2. Private Sub CommandButton3_Click()
  3.  ActiveSheet.ClearArrows
  4. End Sub
図5-12

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

「終了」ボタンをクリックした時に動作するプロシージャが図5-13です。
  1. '========== ⇩(6) 終了ボタン =============
  2. Private Sub CommandButton4_Click()
  3.  Unload Me
  4. End Sub
図5-13

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

6.アドインとしてExcelにマクロを登録

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

7.最後に

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


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

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