ExcelとVBAの「自動再計算」の事

◆ 『 Application.Volatile(true) 』

　・Volatileメソッド … ユーザー定義関数を自動再計算関数にする。
　・“(ture)”は省略可能。
　・functionステートメントの中で使う。
　　└→Functionステートメントによるユーザー定義関数は、
　　　　Volatileメソッドを入れない標準状態だと「自動で再計算を行わない」関数であるのだ。


※注意点。

　「自動では再計算を行わない」のは、“functionの中の参照値が変わった”場合。
　MicrosoftExcelの場合、“セルの内容が変わった時”に「自動で再計算を行う」かどうかは設定によります。標準は「セルの内容が変更されたら自動で再計算する設定」になっています。EXCEL2003の場合［ツール］→［オプション］→［計算方法］タブ→［計算方法］欄の『自動』か『手動』かで選択します。

◎例(a)

Function AddAdd(sn As Integer) As Long

AddAdd = Range("A1").Value + sn

End Function

　この関数で、A1に「100」が入力されていて、B2セルに「=AddAdd(5)」と入力していてB2セルに『105』と表示されている所で、A1セルを「100」から「200」に変更した場合、

　　・ユーザー定義関数の入力されているB2セルの内容は「=AddAdd(5)」から変化していない為、“セルの内容が変わった場合”に該当しない為、Excelの設定による再計算は行われません。
　　└そして、Functionステートメントの中にVolatileメソッドが無いと、“functionの中の参照値が変わった場合”にも相当しない為、ユーザー定義関数も再計算は行われません。
　　└結果、B2セルの表示は変わらず『105』と表示されたままになります。


◎例(b)

Function AddAdd(srn As Integer, sn As Range) As Long

AddAdd = srn.Value * sn

End Function

　この関数で、A1に「100」が入力されていて、B2セルに「=AddAdd(A1,5)」と入力していてB2セルに『105』と表示されている所で、A1セルを「100」から「200」に変更した場合、

　　・ユーザー定義関数の入力されているB2セルの内容は「=AddAdd(A1,5)」でA1を参照しており、そのA1が変化した為、変化しているとみなされる為、“セルの内容が変わった場合”に該当する為、Excelの設定による再計算は行われます。
　　└なので、Functionステートメントの中にVolatileメソッドが無く“functionの中の参照値が変わった場合”にも相当しなくても、Excelの設定による再計算が行われた結果、ユーザー定義関数も再計算が行われます。
　　└結果、B2セルの表示は『105』から『205』に変わります。


◆まとめ

　これらの関係を理解して、「Excelの設定」による自動再計算を使うか「Volatileメソッド」による自動再計算を行うのかを考えてプログラムを組むことがプログラム動作の効率化につながると思われます。
（運用をあやまると、本来ならどちらかの自動再計算だけの運用で出来る事に両方の自動再計算処理が使われてしまうという悪効率な事をしてしまいかねない）
　そして、Volatileメソッドによる自動再計算の方がExcelの設定による自動再計算よりもプログラムを重くしてしまう気がする。(例a)をVolatileメソッドで自動再計算する関数にした場合、A5セルなどの本来(例ａ)の関数とは関係ないセルが変更された場合も自動再計算処理が発生してしまうが、(例b)の関数だと、関係ないセルが変更された時には自動再計算処理が発生しないので、その分効率が良く→軽くなると思う。