ユーザー定義関数の再計算

質問

ユーザー定義関数を作りました。
ところが、この関数が自動再計算をしてくれません。
どうしたら自動再計算するようになるのでしょうか？
よろしくお願いします。

ちなにこの関数は、自分のシートのB2とsheet1～sheet4のB9を比較して、正しければB9の４つ右のセルの値を合計して返すものです。
=SheetLook($B$2,"sheet1,sheet2,sheet3,sheet4",B9,4)

コードです。
Function SheetLook(参照元 As Variant, 比較対象シート As String, 比較対象セル As Range, 参照セル位置 As Integer) As Variant
　　Dim i As Integer
　　Dim rng As Range
　　Dim sss As Variant
　　Dim kei As Variant
　　Dim cnt As Integer

　　sss = Split(比較対象シート, ",")
　　kei = 0
　　cnt = 0
　　For i = 0 To UBound(sss)
　　　　Set rng = Sheets(sss(i)).Range(比較対象セル.Address)
　　　　If 参照元 = rng Then
　　　　　　kei = kei + rng.Offset(0, 参照セル位置)
　　　　　　cnt = cnt + 1
　　　　End If
　　Next
　　If cnt <> 0 Then
　　　　SheetLook = kei
　　Else
　　　　SheetLook = ""
　　End If
End Function

回答No.3

Wendy02 · ベストアンサー

こんにちは。

まず、そのユーザー定義関数は、ちょっと無理があるかもしれませんね。

どこかというと、
　　　　Set rng = Sheets(sss(i)).Range(比較対象セル.Address)

この部分です。文字で入れて、シートを認識させるのは悪くはないけれども、そのコードにはブックの概念が、ここには含まれていません。

臨時の措置としては、
　　Set rng = ThisWorkbook.Sheets(sss(i)).Range(比較対象セル.Address)
のようにしないと、ActiveWorkbook のシートを探すようになります。

本格的には、
'---------------------------
　 On Error Resume Next
　　　Dim myRng As Range
　　　Dim myBk As Workbook
　　Set myRng = Application.Caller
　　If myRng Is Nothing Then 　'ルーチンで使った場合
　　　 Set myBk = ThisWorkbook
'Set myBk = ActiveWorkbook ''いずれか
　　Else
　　　Set myBk = myRng.Parent.Parent
　　End If
'-----------------------------
　　For i = 0 To UBound(sss)
　　　　Set rng = myBk.Sheets(sss(i)).Range(比較対象セル.Address)　'**

なお、

>この関数が自動再計算をしてくれません。

Volitile を入れると、引数の参照先の変更があったときにも、再計算がされます。
Volitile を入れなければ、引数の直接の変更があったときに、再計算がされます。だから、本来は、どちらでもよいことです。

その代わりに、このような方法もあります。

=SheetLook($B$2,"sheet1,sheet2,sheet3,sheet4",B9,4)+NOW()*0

Volitile でも、上記のようにしても、必ず、再計算してしまいますから、ワークシートを変更がなくても、開いただけでCalculate の命令があったというように、Excelは解釈してしまいますから、変更があった(ThisWorkbook.Saved =False) となって、保存を要求されます。

お礼

回答ありがとうございます。
とりあえず、+NOW()*0でやってみました。
何とか解決しました。
ありがとうございました。

回答No.2

Sinogi

>私のExcel2003では、変化有りませんでした。

Application.Volatile　は有効なはずです。
オプション　計算方法のチェックはOK?

お礼

オプション　計算方法のチェックは自動でした。
いろいろやってみると、動くときと動かないときがあるようです。
そして、一度動いたシートでは動き続けました。
規則性はちょっと掴めませんでした。

回答No.1

kybo

最初の方に以下の１文を入れてみてください。

Application.Volatile

お礼

早速の回答、ありがとうございます。
私のExcel2003では、変化有りませんでした。