行列番号によるINDIRECT関数の撤回まとめ続き参考資料はじめに先日は安定したユーザー定義関数(UDF*1)の作り方についてお話しました。www.excel-chunchun.com前回の安定したユーザー定義関数を作る方法をまとめるとExcelは引数で指定したセルが変更された時しか再計算しない。そのためには、処理に必要なデータは全て引数に含めること。今回の議題は、その直前に書いた正しい設計で関数を作成すれば、Application.Volatileが必要になることはまずありえません。必要な時もあるかもよというお話したいと思います。揮発性関数がなぜ揮発性なのか?先ほどから登場しているApplication.Volatileですが、これはユーザー定義関数をExcelに揮発性関数として認識させるための式です。Application.Volatileを理解するためには、先に「揮発性関数」について正確に理解して頂く必要があると考えています。電源を切ると記憶内容が消える性質の記憶装置。揮発性関数は、計算結果が揮発するもの=(同じ引数を与えた場合でも)計算するたびに値が変化する可能性がある関数と考えると良いでしょう。なっていますが、以下のMicrosoft公式ドキュメントにあるように、「揮発性関数」が正しい和名です。Excelの再計算この記事は、一度よく読んでおくことをオススメします。組み込みのワークシート関数で、揮発性関数として有名なのが以下の関数です。環境変数を使っているもの(NOW、TODAY、RAND、RANDBETWEEN)計算しないと参照先のセルが不明なもの(OFFSET、INDIRECT)の2種類に分類できます。いずれのグループの関数も、揮発性関数にせざる負えない理由があって、やむを得ず揮発性関数になっています。環境変数を使う揮発性関数NOW、TODAY、RAND、RANDBETWEENは、計算の入力値に環境変数を使っています。システム時刻の取得や、乱数生成のために何らかの時刻を使用していると考えられます。これらの関数は、本来、常に最新の結果が欲しくて使う関数のはずですから、ことある毎に再計算を行うのは仕方がないと言えます。(それを知らずにTODAY()を使って、資料の日付が・・・なんて話をたまに聞きますが、たぶん、関数の解説が「今日の日付を求める」になっているのが誤用する原因なのだと思います。:を覚えて、上手く使い分けましょう)support.office.comsupport.office.com参照先のセルが不明な揮発性関数OFFSETやINDIRECTは、引数に参照先のセルがはっきり明記されていないものです。受け取った引数を関数内部で計算しないことには、Excelはどこのセルを読み取れば良いのか特定できないのです。従って「引数で指定したセルが変更された時だけ再計算する」ことができません。数式タブの「参照先のトレース」「参照元のトレース」機能が使えないことからも、Excelが追跡できていないことが確認できますね。従って、どこのセルが変更された時でも再計算を行う関数にしておく必要があるのです。support.office.comsupport.office.comVBAで揮発性関数を作成するApplication.VolatileとはApplication.Volatileはユーザー定義関数(UDF)をワークシートから使用した時に、Excelへ揮発性関数として認識させるための構文です。Functionで定義された関数全てをユーザー定義関数と呼んでいるので、呼び出し元はワークシートに限られません。普通にVBAから呼び出して使うことも出来ます。メソッドユーザー定義関数を自動再計算関数にします。自動再計算関数は、ユーザー定義のいずれかのセルで計算が行われるたびに再計算を行います。これに対して非自動再計算関数では、入力した変数の値が変わったときにだけ再計算を行います。このメソッドは、ユーザー定義のセルを計算するユーザー定義関数の内部で使わなければ効果がありません。指定すると、その関数は自動再計算関数になります。