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

解決済

質問者：st-kanda
質問日時：2009/06/09 10:45
回答数：3件

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

ちなにこの関数は、自分のシートの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

通報する

この質問への回答は締め切られました。

質問の本文を隠す

回答 (3件)

ベストアンサー優先
最新から表示
回答順に表示

No.3ベストアンサー

回答者： Wendy02
回答日時：2009/06/09 13:46

こんにちは。

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

どこかというと、
　　　　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) となって、保存を要求されます。