試し方
標準モジュールに下記のサンプルコードを貼り付けて、目的のシートをアクティブにした状態でマクロ(MarkVolatileCells)を実行すると、揮発性の数式を持つセルにメモが添付されます。
解説
引数の値をそのまま返すような単純なユーザー定義関数(UDF)を作成した場合、その引数にセル参照を指定しておくと、再計算時には参照先が揮発性の場合にのみ当該 UDF が呼び出されることになります。
サンプルソースコードの MarkVolatileCells() では、この性質を利用して、
- 作業用に、対象シート(target_sheet)を複製
- 複製した作業用のシート(temp_sheet)中で数式を持つセル(=target_sheetでも同じアドレスのセルは数式を持つ)に対し、数式を target_sheet の同じアドレスのセルへの参照を引数としたUDF(ReferCellValue)に置換
置換を行う前には自動計算状態(Application.Calculation = xlCalculationAutomatic)にして、全ての ReferCellValue が一度呼び出される(実行される)ようにしておく
- 手動計算状態に切り替え(Application.Calculation = xlCalculationManual)た後、コレクション(VolatileAddressCollection)を初期化する
- 強制的に再計算(temp_sheet.Calculate)を行うと、参照先が揮発性の場合にのみ ReferCellValue が呼び出されるため、このアドレスをコレクション(VolatileAddressCollection)に記録
- 記録されたアドレスに対応する target_sheet 中のセルにメモを添付
という処理を行っています。
注意事項
上記の手法は当方が思いつきで試してみたものであり、正しく揮発性の数式を持つセルが検出できるのかについての保証はありません。あくまで参考として考えて下さい。
また、数式が指定されているセルに対してしか使えないため、
- 条件付き書式内で揮発性数式が使われている場合
- リンクした図の場合
等のケースについては検出できません。