のんびりよりみちきっぷ

考えた事や実施したことをのんびりと。

ユーザー定義関数の再計算と更新。できないときの対処方法4つ!

スポンサーリンク

こんにちは。

 

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

 

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

 

ユーザー定義型関数とは

ユーザー定義型関数について少し書いておきます。

 

エクセル使用者が自由に作ることができる関数です。

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

 

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. 参照先を1つずつ更新
  2. 「application.volatile」を追加
  3. 引数のセルをマクロで更新
  4. トリガ変数を関数に追加

 

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()が常に更新されてしまって任意のタイミングで更新させることができませんでした。

 

まとめ

以上、ユーザー型関数が更新されない原因とその対処4つを紹介しました。

 

動作のきっかけ(トリガー)を与えてやることで、関数が動き出します。

それさえ与えてやればいいので、やり方は色々ありそうです。

 

参考になればと思います。