えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

MENU
トップ > Excel > ワークシート用ユーザー定義関数を揮発性にする方法

ワークシート用ユーザー定義関数を揮発性にする方法

Excel Excelユーザー定義関数 VBA

今回はVBAで作るユーザー定義関数の第二弾。揮発性関数についてお話します。

はじめに

先日は安定したユーザー定義関数（UDF*1）の作り方についてお話しました。

www.excel-chunchun.com

前回の安定したユーザー定義関数を作る方法をまとめると

Excelは引数で指定したセルが変更された時しか再計算しない。

そのためには、処理に必要なデータは全て引数に含めること。

と、いうことになります。


今回の議題は、その直前に書いた

正しい設計で関数を作成すれば、Application.Volatileが必要になることはまずありえません。

に関して、何で無いと言えるの？　必要な時もあるかもよというお話したいと思います。


揮発性関数がなぜ揮発性なのか？

先ほどから登場しているApplication.Volatileですが、これはユーザー定義関数をExcelに揮発性関数として認識させるための式です。

Application.Volatileを理解するためには、先に「揮発性関数」について正確に理解して頂く必要があると考えています。


揮発性関数とは？

揮発性とは、Wikipedia - 揮発性によると、

と、いうことだそうです。


つまり揮発性関数は、計算結果が揮発するもの＝（同じ引数を与えた場合でも）計算するたびに値が変化する可能性がある関数と考えると良いでしょう。

（上記Microsoft文章では、ドキュメントの自動翻訳の影響か「自動再計算関数」になっていますが、以下のMicrosoft公式ドキュメントにあるように、「揮発性関数」が正しい和名です。）

docs.Microsoft.com - Excelの再計算

この記事は、一度よく読んでおくことをオススメします。


組み込みの揮発性関数

組み込みのワークシート関数で、揮発性関数として有名なのが以下の関数です。

  • NOW
  • TODAY
  • RAND
  • RANDBETWEEN
  • OFFSET
  • INDIRECT


これらは、

  • 環境変数を使っているもの（NOW、TODAY、RAND、RANDBETWEEN）
  • 計算しないと参照先のセルが不明なもの（OFFSET、INDIRECT）

の2種類に分類できます。


いずれのグループの関数も、揮発性関数にせざるを得ない理由があって、揮発性関数になっています。


環境変数を使う揮発性関数

NOW、TODAY、RAND、RANDBETWEENは、計算の入力値に環境変数を使っています。

システム時刻の取得や、乱数生成のために何らかの時刻を使用していると考えられます。

これらの関数は、本来、常に最新の結果が欲しくて使う関数のはずですから、ことある毎に再計算を行うのは仕方がないと言えます。

（それを知らずにTODAY()を使って、資料の日付が・・・なんて話をたまに聞きますが、たぶん、関数の解説が「今日の日付を求める」になっているのが誤用する原因なのだと思います。Ctrl + ;やCtrl + :を覚えて、上手く使い分けましょう）

support.office.com

support.office.com


参照先のセルが不明な揮発性関数

OFFSETやINDIRECTは、引数に参照先のセルがはっきり明記されていないものです。

受け取った引数を関数内部で計算しないことには、Excelはどこのセルを読み取れば良いのか特定できないのです。

従って「引数で指定したセルが変更された時だけ再計算する」ことができません。

例えば数式タブの「参照先のトレース」「参照元のトレース」機能が使えないことからも、Excelが追跡できていないことが確認できますね。

従って、どこのセルが変更された時でも再計算を行う関数にしておく必要があるのです。

support.office.com

support.office.com


VBAで揮発性関数を作成する

Application.Volatileとは

Application.Volatileはユーザー定義関数(UDF)をワークシートから使用した時に、Excelへ揮発性関数として認識させるための構文です。

Functionで定義された関数全てをユーザー定義関数と呼んでいるので、呼び出し元はワークシートに限られません。普通にVBAから呼び出して使うことも出来ます。


公式ドキュメント

docs.Microsoft.com - Volatile メソッド

ユーザー定義関数を自動再計算関数にします。 自動再計算関数は、ユーザー定義のいずれかのセルで計算が行われるたびに再計算を行います。 これに対して非自動再計算関数では、入力した変数の値が変わったときにだけ再計算を行います。 このメソッドは、ユーザー定義のセルを計算するユーザー定義関数の内部で使わなければ効果がありません。

名前 必須 / オプション データ型 説明
Volatile 省略可能 Variant True を指定すると、その関数は自動再計算関数になります。 False を指定すると、非自動再計算関数となります。 既定値は True です。


使い方

引数を付ける事もできますが、普通はApplication.Volatileとだけ書いていると思います。

文法的にはこの3パターンが有りえます。

Function Func()

    'この関数は揮発性関数である
    Application.Volatile True
    
    'この関数は揮発性関数ではない
    Application.Volatile False
    
    'この関数は揮発性関数である
    Application.Volatile
    
End Function


引数が使える事を活用するため、外部から揮発性化を制御するための引数を付けるというのは面白い試みかもしれません。

Function TODAYCustom(Optional v As Boolean)
    Application.Volatile v
    TODAYCustom = Now()
End Function

他にも、特定の条件を満たすまで（例えばエラー値の間）は揮発性にするとかも考えられます。


揮発性関数の作り方

上記のように、ユーザー定義関数を揮発性にするだけなら簡単です。

関数流入直後に

Application.Volatile

と書けば良いのですから。


問題はどんな時に揮発性関数にするべきか。ではないでしょうか？


少ないですが、私が過去に作成した揮発性関数を紹介しておきます。


背景色によるCOUNTIF関数

警告：地味に需要のある関数ですが、これは失敗談です。利用にはご注意ください。

VBA

'[範囲]のセルのうち[検索条件]のセルと色が一致した数をカウントする
Public Function CountIfColor(範囲 As Range, 検索条件 As Range)
    Application.Volatile True
    
    Dim cnt As Long
    Dim r As Range
    For Each r In 範囲
        cnt = cnt + -(r.Interior.Color = 検索条件.Interior.Color)
    Next
    
    CountIfColor = cnt
End Function

式

=CountIfColor($D$4:$AF$13,B16)
=CountIfColor($D$4:$AF$13,B17)
=CountIfColor($D$4:$AF$13,B18)

概要

一見、必要なセル範囲を引数に収めているので、問題ないように見えます。

しかし、背景色の変更は再計算が起こるキッカケにはならないので、どんなに待っても「再計算」はされません。

再計算を起こるキッカケは、値の変更であることに注意が必要です。


ただし、揮発性関数にしておけば何かある毎に再計算をしてくれるので、不完全とはいえある程度はリアルタイムに再計算させることが可能になります。

もし、この方法を使うならば「再計算」ボタン（F9キーに相当）を設置すると良いでしょう。

Sub ボタン1_Click()
    ActiveSheet.Calculate
End Sub

尚、揮発性関数にしておかないと、ActiveSheet.Calculateを実行しても再計算されない。ので、これまた注意が必要です。

Calculateメソッドは、Excelが再計算が必要だと認識しつつも、手動計算モードで計算が保留にされていたものにしか適用されないのです。


或いは何らかの別のVBAから

Range("C16:CC18").Dirty

を実行しておくという手もあります。


ただし、強制的な再計算（Ctrl + Alt + F9に相当）を実行すれば、揮発性関数にしなくてもOKです。

Public Function CountIfColor(範囲 As Range, 検索条件 As Range)
'    Application.Volatile '必要なし
    略
End Function

Sub ボタン1_Click()
    Application.CalculateFull
End Sub

こっちのほうが幾分かマシですね。


いっそのこと、選択セルを変更しただけで再計算させたほうが便利かも知れません。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.CalculateFull
End Sub


背景色によるCOUNTIF関数の撤回

さて、私も昔はここまでのようなギミックを使った経験がありますが、いま開発するとしたら別のアプローチを取ります。

  • セルには該当する作業番号を入力し、着色は条件付き書式により自動で行います。（文字色を背景色に合わせて見えなくすることもあります）
  • 集計は組み込みのワークシート関数のCOUNTIF関数を使用し、リアルタイムで計算されます。

また、これに加えて

  • Ctrl+Enterで一括入力すれば操作性は損なわれない。
  • Deleteキーで着色を消すことができる。
  • つまりコピーされて罫線等の書式が破壊される心配が少ない。（※要教育）

というメリットもあります。

教育が足りていないと、結局コピー＆ペーストが使用されて罫線とともに条件付き書式が破壊される恐れがあるので、注意が必要かもしれません。

それもあって、例え文句を言われようと、作業番号の文字はわざと見える仕様に留めています。


Excelの標準機能で出来るのであれば、このようなユーザー定義関数を作る必要ありませんでしたね。

（何故紹介した！？）


行列番号によるINDIRECT関数

標準のINDIRECT関数を使うには、列番号を文字列にしなければならないという制約があります。

それを回避するためにこのような関数を作ったことがありました。

'アクティブシートの指定した行・列のセル参照を返す
Function INDIRECTidx(rowidx, colidx) As Range
    Application.Volatile
    Set INDIRECTidx = Cells(rowidx, colidx)
End Function

警告：これは失敗談です。よい子のみんなは真似しないで下さい。

A B C D E F
1 あ い う 行 3
2 え お か 列 2
3 き く け 式 ?
4 式 ? 

=INDIRECTidx(F1,F2)

が、この関数は無価値です。


行列番号によるINDIRECT関数の撤回

なぜならば、OFFSET関数で要件を満たすことが出来るからです。

=OFFSET(A1,F1-1,F2-1)

どう見てもOFFSET関数の方が汎用性が高いですね。


それだけで済めば良いのですが、更に危険なバグを備えておりまして、

引数にシートを特定するための情報が無いため、別のシートがアクティブの時に誤った計算結果が出ます。

もし別シートにこの結果を参照している式があったら、一生正しい計算結果が表示されないという、とても面白いことが起きます。

これはシートに限らずブックについても言えることです。

従って、このような事がしたい場合は、必ず引数にセル参照を1つは設定しましょう。

セル参照さえあれば、Range.Worksheet.で明示的にシートを指定できるようになります。

訂正

Application.CallerやApplication.ThisCellにより、呼び出し元のセルを特定することが可能であることを教えて頂きました。ありがとうございます。

以下のように記載することで、別シートがアクティブな時にも対応できます。

'アクティブシートの指定した行・列のセル参照を返す
Function INDIRECTidx(rowidx, colidx) As Range
    Application.Volatile
    Set INDIRECTidx = Application.ThisCell.Worksheet.Cells(rowidx, colidx)
End Function


docs.microsoft.com - Application.Caller

これは、プロシージャを呼び出したボタンオブジェクト等を知るためにもよく使われるプロパティですね。

Caller 値
単一セルに入力されたユーザー定義関数 そのセルを示すRangeオブジェクトを指定します。
セル範囲内の配列数式の一部として入力されたユーザー定義関数 セルの範囲を指定するrangeオブジェクトを指定します。
Auto_Open、Auto_Close、Auto_Activate、または Auto_Deactivate マクロ ドキュメントの名前をテキストとして指定します。
OnDoubleClick プロパティまたは OnEntry プロパティのどちらかによって設定されたマクロ マクロを適用するグラフオブジェクトの識別子またはセル参照 (該当する場合) の名前を指定します。


docs.microsoft.com - Application.ThisCell

こちらは、呼び出し元のセルを取得できるピンポイントなプロパティのようです。

Function UseThisCell() 
 MsgBox "The cell address is: " & _ 
 Application.ThisCell.Address 
End Function


というわけで、せっかく作った関数はOFFSET関数で十分だったのでした。作る必要なかったですね？

（何故紹介した！？）


まとめ

今回は「揮発性関数」と「ユーザー定義関数の揮発性化」について説明しました。

揮発性関数はどこのセルが変更された時でも再計算を行う関数です。

ごくごく限られた場面でしか必要無いものだということがお分かり頂けたかと思います。

他にも揮発性には

という厄介な性質もあります。


今回記事を書くにあたって、なんとか揮発性のユーザー定義関数の良い使い道を紹介したかったのですが、私の経験の中で最後まで使えた物が思い当たりませんでした。

当時は使えると思った技術でも、Excelの基礎知識が増えていくにつれて「もっと良いやり方がある」と気がついて廃止されてしまったのです。

私のように揮発性関数にしたいと思って来訪された方は実装方法を変えることで標準機能だけで実現できないかを一度検討すると良いと思います。


もし、本シリーズを読んだ上でユーザー定義関数（の揮発性関数）の有効な使い道を思いついた方は、ぜひ公開してもらえると嬉しいです。


次回は「ユーザー定義関数自体が不安定なので使い方に気をつけたほうが良いよ」という話をして、このシリーズは完結にしたいと思います。

以上


続き

前編（Part1）

www.excel-chunchun.com


続編（Part3)

www.excel-chunchun.com


参考資料

docs.microsoft.com

docs.microsoft.com

www.excel-chunchun.com

何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪　ちゅんちゅん(・8・)

*1:User Defined Functionの略

プライバシーポリシー