風柳メモ

ソフトウェア・プログラミング関連の覚書が中心。

エクセルで揮発性の数式を含むセルを抽出する試み

はじめに

エクセルのワークシートで使える関数の中には揮発性関数というものがあり、これらは再計算の度に再評価(実行)されてしまいます。

揮発性関数の中には OFFSETINDIRECT といった便利な関数も含まれているためについ使ってしまいがちですが、かといってこれらを多用していると動作が重くなる要因のひとつとなります。
場合によっては、他の関数に置き換える等の対処を考えたほうが良いかも知れません。
自動再計算を行わないという対処法もありますが、これはこれで何かと不便です。

そこで、調査用として、シート中の揮発性の数式を含む(再計算時に常に再評価されてしまう)セルを抽出する方法について考えてみました。

f:id:furyu-tei:20201105095343p:plain
サンプルマクロの実行結果例

なお、他の方々から知見を得るために、クイズ形式でツイートもしています。

もっとうまい方法をご存じの方、コメントやこのツイートへのリプライででも教えて下さいませ。

試し方

標準モジュールに下記のサンプルコードを貼り付けて、目的のシートをアクティブにした状態でマクロ(MarkVolatileCells)を実行すると、揮発性の数式を持つセルにメモが添付されます。

サンプルコード


解説

引数の値をそのまま返すような単純なユーザー定義関数(UDF)を作成した場合、その引数にセル参照を指定しておくと、再計算時には参照先が揮発性の場合にのみ当該 UDF が呼び出されることになります。

サンプルソースコードの MarkVolatileCells() では、この性質を利用して、

  1. 作業用に、対象シート(target_sheet)を複製
  2. 複製した作業用のシート(temp_sheet)中で数式を持つセル(=target_sheetでも同じアドレスのセルは数式を持つ)に対し、数式を target_sheet の同じアドレスのセルへの参照を引数としたUDF(ReferCellValue)に置換置換を行う前には自動計算状態(Application.Calculation = xlCalculationAutomatic)にして、全ての ReferCellValue が一度呼び出される(実行される)ようにしておく
  3. 手動計算状態に切り替え(Application.Calculation = xlCalculationManual)た後、コレクション(VolatileAddressCollection)を初期化する
  4. 強制的に再計算(temp_sheet.Calculate)を行うと、参照先が揮発性の場合にのみ ReferCellValue が呼び出されるため、このアドレスをコレクション(VolatileAddressCollection)に記録
  5. 記録されたアドレスに対応する target_sheet 中のセルにメモを添付

という処理を行っています。

注意事項

上記の手法は当方が思いつきで試してみたものであり、正しく揮発性の数式を持つセルが検出できるのかについての保証はありません。あくまで参考として考えて下さい。

また、数式が指定されているセルに対してしか使えないため、

  • 条件付き書式内で揮発性数式が使われている場合
  • リンクした図の場合

等のケースについては検出できません。