揮発性関数？？？
Application.Volatileメソッド
前回の
に、id:imihito さんからコメントをいただいた。
曰く、「きっと うまく いくでしょう」
`Excel.Application.Volatile`メソッドを使えば、その関数を揮発性関数にできたはずです。
（中略）
揮発性関数というのは計算結果が毎回再計算される関数のことです。
組み込み関数ではNOWとかOFFSET、RANDなどが該当します
ですと。
ほえ～。そんな方法があったのかぁ……。それにしても、「揮発性」て、ガソリンみたいな関数やのう……（"Volatile"を辞書で引いたら、そのものズバリ、「揮発性の」という意味が載ってた。直訳かよｗｗｗ）。
んで、やってみた。
自作ワークシート関数を揮発性関数化する
前回のリスト１のFunctionを、下記のように改変する。
リスト１ 標準モジュール
Public Function assembleWord(ByVal Cell1 As Range, _
ByVal Cell2 As Range) As String
Application.Volatile '……(*)'
Dim str As String
str = Cell1.Value & Cell2.Value
str = str & Cell2.Offset(0, 1).Value
assembleWord = str
End Function
「改変した」っつっても、変えたのは(*)のところだけ。
コチラによると、たったこれだけで、わが自作ワークシート関数AssembleWordは、揮発性関数とやらに生まれ変わっとるということになる。ホンマかいな。
実験
シートモジュールに書いた前回のリスト２を全部コメントアウトしてから、
この状態の表のＣ２セルに、
「ｗ」を書き込み、[Enter]をポチッ！
ウホッ！ あっさり再計算されたｗｗｗ
id:imihito さん、
あざす!!!!!!!!
気になること
わが自作ワークシート関数AssembleWordが、実にエレガントに生まれ変わったわけだが、ちょっと気になることが。
ステップ実行で[F8]を連打しているときに気づいたんだが、どうも１回の処理をするのに、AssembleWord関数が何遍も呼び出されているみたいなのだ。
んで、今一度コチラを見てみると、
自動再計算関数は、ワークシートのいずれかのセルで計算が行われるたびに再計算を行います。
だと。
要するに、１箇所値の変更があっただけで他の数式も全部再計算しているってことか。
検証
『タモリ倶楽部』だったら、剣を打ち合う音と笙の音が流れるところ。
検証用に、次のコードで実行してみた。
リスト２ 標準モジュール
'宣言セクション'
Option Explicit
Public cnt As Integer '……(1)'
Public Function assembleWord(ByVal Cell1 As Range, _
ByVal Cell2 As Range) As String
Application.Volatile
cnt = cnt + 1 '……(2)'
Dim str As String
str = Cell1.Value & Cell2.Value
str = str & Cell2.Offset(0, 1).Value
assembleWord = str
End Function
Public Sub assembleWordTest()
ThisWorkbook.Worksheets(2).Range("C2").Value = "ｗｗｗ" '……(3)'
Debug.Print cnt '……(4)'
End Sub
(1)で、Public変数cntを準備して、
(2)では、AssembleWordが呼び出されるごとにcntをインクリメント。
エントリポイントとしてassembleWordTestプロシージャを作る。このプロシージャでは、
(3)でＣ２セルを書き変え、
(4)でcntの値をイミディエイト・ウインドウに出力する。
この状態で実行すると、イミディエイトは
こうなった。AssembleWord関数を用いたセルが５つあるので、５回呼び出された模様。
おわりに
もともと、当該の１セルだけ（今回の場合だとＤ４セルだけ）を再計算してほしかったんであって、一つ値を変えるだけで同じ関数を用いたセル全てを再計算、みたいな大袈裟なことは望んでなかったんだけれど……。
やっぱり、Application.Volatileメソッドを使うべきなんだろうか……？？？
追記
前回の
やり方では何回再計算しているのか、同じように計測してみると、何と、cntの数値が「11」と出た。
やっぱり、Application.Volatileメソッドで揮発性関数にしてしまうのが一番効率的みたいですね。
