揮発性関数(Application.Volatileメソッド)

揮発性関数???

Application.Volatileメソッド

前回の

akashi-keirin.hatenablog.com

に、id:imihito さんからコメントをいただいた。

曰く、「きっと うまく いくでしょう」

`Excel.Application.Volatile`メソッドを使えば、その関数を揮発性関数にできたはずです。
(中略)
揮発性関数というのは計算結果が毎回再計算される関数のことです。
組み込み関数ではNOWとかOFFSET、RANDなどが該当します

ですと。

ほえ~。そんな方法があったのかぁ……。それにしても、「揮発性」て、ガソリンみたいな関数やのう……("Volatile"を辞書で引いたら、そのものズバリ、「揮発性の」という意味が載ってた。直訳かよwww)。

んで、やってみた。

自作ワークシート関数を揮発性関数化する

前回のリスト1のFunctionを、下記のように改変する。

リスト1 標準モジュール
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は、揮発性関数とやらに生まれ変わっとるということになる。ホンマかいな。

実験

シートモジュールに書いた前回のリスト2を全部コメントアウトしてから、

f:id:akashi_keirin:20171230213121j:plain

この状態の表のC2セルに、

f:id:akashi_keirin:20171230213129j:plain

「w」を書き込み、[Enter]をポチッ!

f:id:akashi_keirin:20171230213136j:plain

ウホッ! あっさり再計算されたwww

id:imihito さん、

あざす!!!!!!!!

気になること

わが自作ワークシート関数AssembleWordが、実にエレガントに生まれ変わったわけだが、ちょっと気になることが。

ステップ実行で[F8]を連打しているときに気づいたんだが、どうも1回の処理をするのに、AssembleWord関数が何遍も呼び出されているみたいなのだ。

んで、今一度コチラを見てみると、

自動再計算関数は、ワークシートのいずれかのセルで計算が行われるたびに再計算を行います。

だと。

要するに、1箇所値の変更があっただけで他の数式も全部再計算しているってことか。

検証

タモリ倶楽部』だったら、剣を打ち合う音と笙の音が流れるところ。

検証用に、次のコードで実行してみた。

スト2 標準モジュール
'宣言セクション'
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 = "www"    '……(3)'
  Debug.Print cnt    '……(4)'
End Sub

(1)で、Public変数cntを準備して、

(2)では、AssembleWordが呼び出されるごとにcntをインクリメント。

エントリポイントとしてassembleWordTestプロシージャを作る。このプロシージャでは、

(3)でC2セルを書き変え、

(4)でcntの値をイミディエイト・ウインドウに出力する。

f:id:akashi_keirin:20171230213145j:plain

この状態で実行すると、イミディエイトは

f:id:akashi_keirin:20171230213153j:plain

こうなった。AssembleWord関数を用いたセルが5つあるので、5回呼び出された模様。

おわりに

もともと、当該の1セルだけ(今回の場合だとD4セルだけ)を再計算してほしかったんであって、一つ値を変えるだけで同じ関数を用いたセル全てを再計算、みたいな大袈裟なことは望んでなかったんだけれど……。

やっぱり、Application.Volatileメソッドを使うべきなんだろうか……???

追記

前回の

akashi-keirin.hatenablog.com

やり方では何回再計算しているのか、同じように計測してみると、何と、cntの数値が「11」と出た。

やっぱり、Application.Volatileメソッドで揮発性関数にしてしまうのが一番効率的みたいですね。