ユーザー定義関数の再計算と更新ができない！対処方法4つを試した。

f:id:yorimichi_ticket:20180306081424j:plain

こんにちは。

エクセルでユーザー型関数（Function）を作って作業していたんですが、参照値を変更しても結果が変わらない・更新されないということが起きました。

更新されない原因と対処方法をまとめます。

ユーザー定義型関数とは

ユーザー定義型関数とはエクセル使用者が自由に作ることができる関数です。

プログラムを書いて作った関数名をシートのセルに記述すれば、作った通りの処理をしてくれます。

sumとかifって関数はよく使われる方は多いと思いますが、それらは標準ライブラリ関数と呼ばれてもともとエクセルが持っている関数です。

今回のユーザー定義関数は自分で作る関数なので、既に準備されているsumやifとは異なります。

再計算・更新がされない

今回起こっている問題は、ユーザー定義型関数が参照している値を変更しても計算結果が変わらないというものです。

テスト用に図のようなシートAとシートB、ユーザー関数を作りました。

f:id:yorimichi_ticket:20170930151806p:plain

シートAにはユーザー型関数で計算した答えをB列に格納しています。

シートBには計算に使う値をB列、C列に格納しています。

ユーザー定義型関数はシートBの2つの値を足し合わせて、シートAに格納する単純なものです。引数をシートAのA列とし、この文字列をシートBから見つけ出してその行の値を足し合わせています。

では早速、例えばシートBの"計算1"の値を1(B列)と1(C列)から5(B列)と10(C列)に変えたとします。

シートAの"計算1"は15になるはずです。

しかし、更新されず2のままでした。

ためしにシートAの"計算1"をクリックしENTERを押してみます。

すると計算結果は更新されました。

これが今回作業中に起きたことです。

更新されない原因

どうやら結果が更新されるかどうかは、ユーザー関数が動くきかっかけ（トリガー）の有無によるようでした。

関数の引数（参照している値）はシートAのA列です。

ここに変化があると関数が動き出します。

一方、関数はシートBを見ていません。

シートBに変化があってもそれを知ることができないんです。

更新されるのは関数の引数（参照している値）に変化があったのみ！

たとえ関数の内部で処理に使っていたとしても、引数に変化がなければ更新されないということが分かりました。

対処

原因が分かったところで対応策を4つ考えました。

参照先を1つずつ更新
「application.volatile」を追加
引数のセルをマクロで更新
トリガ変数を関数に追加

1つずつ紹介していきます。

1,参照先を1つずつ更新

一番簡単な方法です。

関数の引数になっているセルをとにかく「クリック→ENTER」していきます。

作業自体は簡単ですが、データが大量の場合や頻繁にデータが変わって更新させたい場合には向きません。

2.「application.volatile」を追加

作った関数のFunctionの下に「application.volatile」を追加します。

f:id:yorimichi_ticket:20170930152236p:plain

ユーザー関数を自動計算関数にするメソッドで、ワークシートのセルに変化があると自動的にマクロが動いて結果が更新されます。

ユーザー関数に関係のないセルの変化でも関数が動くので、データ数が多いと計算時間が長くなります。

こちらもデータ数が多い場合には向きません。

3,引数のセルをマクロで更新

自作のマクロです。

引数となっているセルが変化すれば関数が動き出すなら、引数を変化させるマクロを組んでしまえというやり方です。

この「変化」とは値の上書きです。

シート上にフォームボタンを追加しておけば、ワンクリックで結果を更新させることが可能です。

以下のマクロを作って、図のようにボタンを追加しました。

Sub Update()

    Dim name As String

    Dim lRow As Integer

    

    lRow = ThisWorkbook.Worksheets("A").Cells(Rows.Count, 1).End(xlUp).Row

    

    For i = 1 To lRow

        name = ThisWorkbook.Worksheets("A").Cells(i, 1).Value

        ThisWorkbook.Worksheets("A").Cells(i, 1).Value = name

    Next

    

End Sub

"計算1"という文字列を一度[name]に格納して、同一のセルに上書きしています。

これを行数分行えば全ての結果が更新されます。

f:id:yorimichi_ticket:20170930152428p:plain

4,トリガ用の引数を関数に追加

関数の動作のきっかけになるように引数を追加します。

今回は"$D$1"を引数にしました。

このセルが「クリック→ENTER」されればユーザー関数が動いて結果が更新されます。

3番目との違いはマクロで更新するか、ユーザー関数の引数で更新するかの違いです。

赤文字のtrigger As Stringという引数を追加しています。

Function CALC2(target As String, trigger As String) As Integer



    Dim lRow As Integer

    Dim Out As Integer

    Dim i As Integer

    With ThisWorkbook.Worksheets("B")

        lRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1

        For i = 1 To lRow

            If .Cells(i, 1).Value = target Then

                Out = .Cells(i, 2).Value + .Cells(i, 3).Value

                Exit For

            End If

        Next

    End With

    CALC2 = Out

End Function

これ以外にも、マクロを登録したボタンを配置しセルを更新させることも可能ですね。

あとついでですが、ダメだった例を一つ。

引数のセルに『=IF(MOD(TEXT(NOW(),"ss"),3)=0,"","")』と入れて、現在時刻（秒）が3で割り切れる時に更新、というのをやってみましたがダメでした。

NOW()が常に更新されてしまって任意のタイミングで更新させることができませんでした。